PowerShell Magic - Add Department Property to Google User Export

Okay okay, it's not magic, but this is still a great use case for some quick and dirty PowerShell scripting. Context: Google Mail is unable to export a user list that includes the 'Department' field that is being synchronized from Active Directory, but I am being asked to take a google export of several thousand users from another group, and add the Department field from AD into the export. Fortunately, PowerShell makes this kind of task a breeze! I wrote the following script to ingest the csv export into a PowerShell object, make another object that represented all of the users on the AD side, and cycle through each user to update the field. Then, after realizing how many of the users had a blank department property in AD, I had it spit out a second CSV that showed which users need to have the department field filled as well. Aaanyway, here's a script for your consumption:

 

#point this to csv to make object
$googleusers = import-csv \SOURCE\PS\googledata.csv

#point this to the desired output path
$outputpath = "\SOURCE\PS\googledata-updated.csv"
$failedupdatescsv = "\SOURCE\PS\googledata-failures.csv"

########################################################################

#add 'Department' property to objects   
$googleusers = $googleusers | Select-Object 'Email address','First name','Last name','Last login','First login','2-step verification enrollment','2-step verification enforcement','First nameLast name',Department

#pull in ad users with additional properties
$adusers = Get-ADUser -filter * -Properties mail,department

#begin alert
$users2process = $googleusers.length
"processing $users2process users"

#pull each user in AD and amend the google department value with the one from AD
$usercounter = 0
foreach ($googleuser in $googleusers){
    $useremail = $googleuser.'Email address'
    foreach ($aduser in $adusers){
        if($aduser.mail -like $useremail){
            $googleuser.Department = $aduser.Department
        }
    }
    $usercounter++
    "Users Processed: $usercounter"
}

#find failures
$failedupdates = $googleusers | ? Department -Like $null
$failedupdatesnum = $failedupdates.Length
$successfulupdates = $users2process - $failedupdates.Length

#output csv files
$googleusers | Export-Csv -Path $outputpath
$failedupdates | Export-Csv -Path $failedupdatescsv

#output results
""
"$successfulupdates users updated successfully, $failedupdatesnum users did not update successfully"
"verify failed users have the Department value in AD and try again"
""
"the fully updated csv can be found here: $outputpath"
""
"a list of failed users can be found here: $failedupdatescsv"

May you copy paste and prosper...

Tutorial Tags: