Welcome back to another edition of PowerShell Scripts I Hope You Never Need. I’m your host, a frustrated IT Admin. When our high school went to print student IDs this year, we found out that the photo company provided no exact link between the student and the photo. Instead, of using a Student ID, username, or even an email to name the photo – we got garbage.

They provided us a text file (with various formats throughout) that contained names and grade level. The pictures were given the default name that each camera generated, and this name was in their list as well.

To fix this, I started by opening Excel, importing the text file and creating a few headers. After formatting, aligning the data, and removing duplicates (yes – there were a ton of duplicate entries), I could start working with this mess in PowerShell. I planned to import the CSV and use the Get-ADUser cmdlet to compare each attribute. I found out, rather quickly, that the grade level value was inconsistent. It appears that one camera operator entered the student’s future grade level while another used their current grade level (or we had a lot of students held back).

This left me with only the firstname and lastname attributes to compare against. To prevent a lot of duplicate matches, I limited the searchbase for the Get-ADUser cmdlet to a single student OU. I also included the optional property Title because that is where we store the student’s current grade.

$PictureList = import-csv "C:\Pictures\PictureList.csv"
$SearchBase = "OU=SomeOU,OU=AnotherOU,DC=DOMAIN,DC=COM"
$Students = Get-ADUser -Filter * -SearchBase $SearchBase -Properties Title

With our two comparisons ready, we can start trying to match data. I created a folder named C:\Pictures and saved the cleaned PictureList.csv file there. I then put all of the student pictures in C:\Pictures\Combined (remember that these files are named by the camera). Finally, I created an UnMatched and a Matched folder under C:\Pictures. This structure allows us to plan for the potential outcome of each matching attempt. The possible results are:

• The full name from the picture data has a single match to an AD user at that school.
• The full name from the picture data does not have any matches at that school (i.e., the student has transferred).
• The full name from the picture data matches multiple AD users at that school. This event is handled manually with a pause statement with the grade value as a way of deciding.

foreach ($Record in $PictureList) {
    write-host $Record

    $LastNameMatch = $Students | where Surname -eq $Record.Lastname
    $FirstNameMatch = $LastNameMatch | where GivenName -eq $Record.FirstName

    if ($FirstNameMatch -eq $Null) {
        write-host "Student Not Found: " $Record -ForegroundColor Yellow
        $PicturePath = "C:\Pictures\Combined\" + $Record.PictureName
        $UnMatchedPictureName = $Record.FirstName + " " + $Record.LastName + "_" + $Record.PictureName
        Move-Item -Path $PicturePath -Destination "C:\Pictures\UnMatched\$UnMatchedPictureName" -Force
    }

    #Pauses on Duplicate Students
    if (($FirstNameMatch).count -gt 1) {
        Write-Host "Duplicate Name: $Record"
        pause
    }

    else {
        $NewPictureName = $FirstNameMatch.SamAccountName + ".jpg"
        $PicturePath = "C:\Pictures\Combined\" + $Record.PictureName
        $MatchedProperties = @{"FirstName" = $FirstNameMatch.GivenName; "LastName" = $FirstNameMatch.Surname; "Grade" = $FirstNameMatch.Title; "IDNumber" = $FirstNameMatch.SamAccountName; "OldPictureName" = $Record.PictureName; "NewPictureName" = $NewPictureName}
        $StudentExport += New-Object PSCustomObject -Property $MatchedProperties
        $MatchedPictureName = $NewPictureName
        Move-Item -Path $PicturePath -Destination "C:\Pictures\Matched\$MatchedPictureName" -Force
        sleep -Seconds 1
    }  
}

After running the whole script, our Combined folder (the one that started with all of the pictures) should only contain pictures that matched multiple AD users (our duplicate scenario). The UnMatched folder should contain students with no matching AD account due to the student leaving the school system. Our Matched folder should contain the pictures of any student at that school. The picture file is also renamed to their Student username to make uploads easier. Finally, all of that matched data is saved to a PowerShell Object ($StudentExport). If you want, you can run $StudentExport | Export-CSV to generate a nice and clean CSV.

Below is the full script:

$PictureList = import-csv "C:\Pictures\PictureList.csv"
$SearchBase = "OU=SomeOU,OU=AnotherOU,DC=DOMAIN,DC=COM"
$Students = Get-ADUser -Filter * -SearchBase $SearchBase -Properties Title

Clear-Variable StudentExport
$StudentExport = @()
foreach ($Record in $PictureList) {
    write-host $Record

    $LastNameMatch = $Students | where Surname -eq $Record.Lastname
    $FirstNameMatch = $LastNameMatch | where GivenName -eq $Record.FirstName

    if ($FirstNameMatch -eq $Null) {
        write-host "Student Not Found: " $Record -ForegroundColor Yellow
        $PicturePath = "C:\Pictures\Combined\" + $Record.PictureName
        $UnMatchedPictureName = $Record.FirstName + " " + $Record.LastName + "_" + $Record.PictureName
        Move-Item -Path $PicturePath -Destination "C:\Pictures\UnMatched\$UnMatchedPictureName" -Force
    }

    #Pauses on Duplicate Students
    if (($FirstNameMatch).count -gt 1) {
        Write-Host "Duplicate Name: $Record"
        pause
    }

    else {
        $NewPictureName = $FirstNameMatch.SamAccountName + ".jpg"
        $PicturePath = "C:\Pictures\Combined\" + $Record.PictureName
        $MatchedProperties = @{"FirstName" = $FirstNameMatch.GivenName; "LastName" = $FirstNameMatch.Surname; "Grade" = $FirstNameMatch.Title; "IDNumber" = $FirstNameMatch.SamAccountName; "OldPictureName" = $Record.PictureName; "NewPictureName" = $NewPictureName}
        $StudentExport += New-Object PSCustomObject -Property $MatchedProperties
        $MatchedPictureName = $NewPictureName
        Move-Item -Path $PicturePath -Destination "C:\Pictures\Matched\$MatchedPictureName" -Force
        sleep -Seconds 1
    }  
}

Bonus (unrelated) note: As most of you know, I work in a K-12 school system in south Georgia. We are hiring a school technician and the position is open until 08/22/18. In the unlikely chance that you are interested, just send me an email or leave a comment. I will get you the job information and application.