# Filename: \\util201\scripts\Functions\userphotolib.ps1 # License: CC-BY-SA 4.0 # Author: bgstack15, TAFKAC # Startdate: 2019-11-07 16:11 # Title: User Photo library # Purpose: apply photo to AD, as reference for redesigning the Employee headshot AD workflow # History: # 2019-11-08.04 Complete session management and photo upload/download for AD, AzureAD, and Outlook # 2019-11-15.01 Add SharePoint connectivity # 2019-11-25.01 Add loglib dependency and modify all functions to use it. # Usage: # Reference: # https://richardjgreen.net/set-thumbnailphoto-active-directory-powershell/ # __Resize function https://seanonit.wordpress.com/2014/11/11/understanding-byte-arrays-in-powershell/ # A variant of the previous https://www.lewisroberts.com/2015/01/18/powershell-image-resize-function/ # Filename manipulation https://www.reddit.com/r/PowerShell/comments/3ro9ow/removing_the_end_of_a_filename_with_powershell/ # https://devblogs.microsoft.com/scripting/weekend-scripter-exporting-and-importing-photos-in-active-directory/ # Azure AD takes 500kb photos https://support.microsoft.com/en-us/help/3062745/user-photos-aren-t-synced-from-the-on-premises-environment-to-exchange # List files from SP https://www.sharepointdiary.com/2018/08/sharepoint-online-powershell-to-get-all-files-in-document-library.html # Download file from SP https://techtrainingnotes.blogspot.com/2014/02/download-file-from-sharepoint-using.html # move file in sharepoint, syntaxt partially helpful https://www.sharepointdiary.com/2018/08/sharepoint-online-move-all-files-from-one-folder-to-another-using-powershell.html # make "move-item" actually work with try-catch https://stackoverflow.com/questions/3097785/powershell-ioexception-try-catch-isnt-working # callbacks in regex http://www.xipher.dk/WordPress/?p=825 # -MaxCharLength https://devblogs.microsoft.com/scripting/10-tips-for-the-sql-server-powershell-scripter/ # http://code2care.org/2015/get-aduser-powershell---get-ad-user-details-using-email-address/ # Improve: # Dependencies: # \\util201\e$\modules\EncryptModule\PassEncryptModule.psm1 # \\util201\scripts\Functions\loglib.ps1 # Load libraries . \\util201\scripts\Functions\loglib.ps1 ### GLOBAL VARIABLES $global:domain = "example.com" if ([boolean]( (Get-Variable -Name global:have_connection_Outlook -ErrorAction SilentlyContinue) -eq $null)) { $global:have_connection_Outlook = $false } if ([boolean]( (Get-Variable -Name global:have_connection_AzureAD -ErrorAction SilentlyContinue) -eq $null)) { $global:have_connection_AzureAD = $false } ### LOAD DEPENDENCIES # For __Get-AllFilesFromFolder and __Get-SPODocumentLibraryFiles and Download-Library-Files-To-Local Add-Type -Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll" Add-Type -Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll" ### FUNCTIONS ## CONNECTION FUNCTIONS # Open-Connection-Outlook usage: # $null = Import-PSSession ($session = Open-Connection-Outlook) Function Open-Connection-Outlook { $credential = Get-Shared-Credential -User "ServiceAccount@${global:domain}" -PasswordCategory "O365" #$session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid" -Credential $credential -Authentication Basic -AllowRedirection $session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/?proxymethod=rps" -Credential $credential -Authentication Basic -AllowRedirection $global:have_connection_Outlook = $true return $session } # usage: Close-Connection-Outlook $session Function Close-Connection-Outlook { Param( [Parameter(Mandatory=$true)] $session ) Remove-PSSession $session $global:have_connection_Outlook = $false } # Get-Shared-Credential -User "ServiceAccount@${global:domain}" -PasswordCategory "O365" Function Get-Shared-Credential { Param ( [Parameter(Mandatory=$true)][string] $User, [Parameter(Mandatory=$true)][string] $PasswordCategory ) Import-Module \\util201\e$\modules\EncryptModule\PassEncryptModule.psm1; #$user = "ServiceAccount@example.com" #$password = GetPassword "O365" | ConvertTo-SecureString -AsPlainText -Force $password = GetPassword $PasswordCategory | ConvertTo-SecureString -AsPlainText -Force if ($pass -contains "ERROR") { Log "ERROR: Unable to decrypt password"; Log "ERROR: Exiting Script"; EXIT } $credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $user, $password Return $credential } # usage: $null = Open-Connection-AzureAD Function Open-Connection-AzureAD { $credential = Get-Shared-Credential -User "ServiceAccount@${global:domain}" -PasswordCategory "O365" $global:have_connection_AzureAD = $true return $( Connect-AzureAD -Credential $credential ) } # Close-Connection-AzureAD Function Close-Connection-AzureAD { Disconnect-AzureAD $global:have_connection_AzureAD = $false } #Function to get all files of a folder Function __Get-AllFilesFromFolder([Microsoft.SharePoint.Client.Folder]$Folder) { # ripped primarily from https://www.sharepointdiary.com/2018/08/sharepoint-online-powershell-to-get-all-files-in-document-library.html $d = New-Object System.Collections.ArrayList #Get All Files of the Folder $Ctx = $Folder.Context $Ctx.load($Folder.files) $Ctx.ExecuteQuery() #Get all files in Folder ForEach ($File in $Folder.files) { #Get the File Name or do something #Write-Host -f Green $File.ServerRelativeUrl # the redirect to null is important to suppress a numeric value being printed $null = $d.Add($File) } #Recursively Call the function to get files of all folders $Ctx.load($Folder.Folders) $Ctx.ExecuteQuery() #Exclude "Forms" system folder and iterate through each folder ForEach($SubFolder in $Folder.Folders | Where {$_.Name -ne "Forms"}) { __Get-AllFilesFromFolder -Folder $SubFolder } Return $d } #powershell list all documents in sharepoint online library Function __Get-SPODocumentLibraryFiles() { # ripped primarily from https://www.sharepointdiary.com/2018/08/sharepoint-online-powershell-to-get-all-files-in-document-library.html param ( [Parameter(Mandatory=$true)] [string] $SiteURL, [Parameter(Mandatory=$true)] [string] $LibraryName, [Parameter(Mandatory=$true)] [System.Management.Automation.PSCredential] $Credential ) Try { #Setup the context $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL) $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credential.UserName,$Credential.Password) #Get the Library and Its Root Folder $Library=$Ctx.web.Lists.GetByTitle($LibraryName) $Ctx.Load($Library) $Ctx.Load($Library.RootFolder) $Ctx.ExecuteQuery() #Call the function to get Files of the Root Folder __Get-AllFilesFromFolder -Folder $Library.RootFolder } Catch { #write-host -f Red "Error:" $_.Exception.Message Write-Error $_ } } # Download-Library-Files-To-Local -SiteURL "https://exampleinc.sharepoint.com/sites/UserPhotos" -LibraryName "Uploads" -Credential $Cred -Outdir "E:\test\output" Function Download-Library-Files-To-Local() { # Heavily modified from https://www.sharepointdiary.com/2018/08/sharepoint-online-powershell-to-get-all-files-in-document-library.html param ( [Parameter(Mandatory=$true)] [string] $SiteURL, [Parameter(Mandatory=$true)] [string] $LibraryName, [Parameter(Mandatory=$true)] $Outdir, [string] $MoveToLibraryName, [System.Management.Automation.PSCredential] $Credential ) #Setup the context $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL) $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credential.UserName,$Credential.Password) $Library=$Ctx.web.Lists.GetByTitle($LibraryName) $Ctx.Load($Library) $Ctx.Load($Library.RootFolder) $Ctx.ExecuteQuery() $fileList = __Get-AllFilesFromFolder -Folder $Library.RootFolder $fileList.count ForEach ($item in $fileList) { # Save file $fileUrl = $SiteURL.Split('/')[0] + "//" + $SiteURL.Split('/')[2] + $item.ServerRelativeUrl $fileName = $Outdir + "\" + $item.Name Log "Save" $fileUrl "to" $fileName $fileStream = [System.IO.File]::Create($fileName) $fileInfo = [Microsoft.SharePoint.Client.File]::OpenBinaryDirect($Ctx, $item.ServerRelativeUrl) $fileInfo.Stream.CopyTo($fileStream) $fileStream.Close() # If the file was successfully copied to local, then move it in SharePoint to "Processed" directory if ( -Not ($MoveToLibraryName -eq $null) ) { $TargetFileUrl = $fileURL.Replace($LibraryName,$MoveToLibraryName) Log $fileName "move to" $TargetFileUrl $item.MoveTo($TargetFileUrl, [Microsoft.Sharepoint.Client.MoveOperations]::Overwrite) $Ctx.ExecuteQuery() } } } ## PHOTO RESIZE FUNCTIONS # Get-Photo-Below-Size -Filename $Filename -MaxSize $MaxSize Function Get-Photo-Below-Size { # Resize-Photo takes a filename and returns the filename of an image that is smaller than MaxSize bytes # If the file is already below requested size param( [Parameter(ValueFromPipeline=$true)][string] $Filename, [Long]$MaxSize ) $photo = [byte[]](Get-Content $Filename -Encoding byte) $ResizeVal=100 $newFilename="" while ($photo.length -gt $MaxSize) { If ($ResizeVal -lt 100) { Remove-Item -Path ${newFilename} } If ($ResizeVal -gt 31) { $ResizeVal -= 10 } Else { $ResizeVal -= 3 } If ($ResizeVal -lt 5) { throw("This photo is just way too big.") } $newFilename = ${Filename} -Replace "\..{1,5}$","_${ResizeVal}.$( $Filename.Split(".")[-1] )" #Write-Host "Shrinking to $newFilename" -Debug Write-Progress -Activity "Shrinking to $newFilename" __Resize-Photo -imageSource $Filename -imageTarget $newFilename -quality $ResizeVal $photo = [byte[]](Get-Content $newFileName -Encoding byte) } if ([string]::IsNullOrEmpty($newFilename)) { $newFilename=$Filename #Write-Host "Already small $newFilename" -Debug } # after leaving the while loop, the current value of $newFilename should be the properly-sized file Remove-Variable photo return $newFilename } # __Resize-Photo -imageSource $imageSource -imageTarget $imageTarget -quality $quality Function __Resize-Photo { # Modified slightly from source: https://benoitpatra.com/2014/09/14/resize-image-and-preserve-ratio-with-powershell/ Param ( [Parameter(Mandatory=$True)][ValidateNotNull()][string] $imageSource, [Parameter(Mandatory=$True)][ValidateNotNull()][string] $imageTarget, [Parameter(Mandatory=$true)][ValidateNotNull()][int] $quality ) if (!(Test-Path $imageSource)){throw( "Cannot find the source image")} #if(!([System.IO.Path]::IsPathRooted($imageSource))){throw("please enter a full path for your source path")} #if(!([System.IO.Path]::IsPathRooted($imageTarget))){throw("please enter a full path for your target path";)} if ($quality -lt 0 -or $quality -gt 100){throw( "quality must be between 0 and 100.")} [void][System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") $bmp = [System.Drawing.Image]::FromFile($imageSource) # resize mechanism unique to bgstack15 $canvasWidth = $bmp.Width * ( $quality / 100 ) $canvasHeight = $bmp.Height * ( $quality / 100 ) #Encoder parameter for image quality $myEncoder = [System.Drawing.Imaging.Encoder]::Quality $encoderParams = New-Object System.Drawing.Imaging.EncoderParameters(1) $encoderParams.Param[0] = New-Object System.Drawing.Imaging.EncoderParameter($myEncoder, $quality) # get codec $myImageCodecInfo = [System.Drawing.Imaging.ImageCodecInfo]::GetImageEncoders()|where {$_.MimeType -eq 'image/jpeg'} #compute the final ratio to use $ratioX = $canvasWidth / $bmp.Width; $ratioY = $canvasHeight / $bmp.Height; $ratio = $ratioY if($ratioX -le $ratioY){ $ratio = $ratioX } #create resized bitmap $newWidth = [int] ($bmp.Width*$ratio) $newHeight = [int] ($bmp.Height*$ratio) $bmpResized = New-Object System.Drawing.Bitmap($newWidth, $newHeight) $graph = [System.Drawing.Graphics]::FromImage($bmpResized) $graph.Clear([System.Drawing.Color]::White) $graph.DrawImage($bmp,0,0 , $newWidth, $newHeight) #save to file $bmpResized.Save($imageTarget,$myImageCodecInfo, $($encoderParams)) #Return $bmpResized $graph.Dispose() $bmpResized.Dispose() $bmp.Dispose() Remove-Variable graph Remove-Variable bmpResized Remove-Variable bmp } ## MAIN VERB FUNCTIONS # Set-Photo-AD -User $User -Filename $Filename Function Set-Photo-AD { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) $Result = -1 $newfile = Get-Photo-Below-Size -Filename $filename -MaxSize 100000 $photo = [byte[]](Get-Content $newfile -Encoding byte) Try { Get-ADUser $User | Set-ADUser -Replace @{thumbnailPhoto=$photo} If ($newfile -ne $Filename) { Remove-Item -Path $newfile } Log "Set AD photo for ${User} to ${newfile}" $Result = 0 } Catch { Write-Error $_ } Remove-Variable newfile return $Result } # Get-Photo-AD -User $user -Filename $filename Function Get-Photo-AD { Param ( [Parameter(Mandatory=$True)] $user, [Parameter(Mandatory=$True)] $filename ) # save photo down from AD to inspect it # https://devblogs.microsoft.com/scripting/weekend-scripter-exporting-and-importing-photos-in-active-directory/ $user = get-aduser $user -properties thumbnailphoto [System.Io.File]::WriteAllBytes($filename,$user.thumbnailphoto) } # Set-Photo-AzureAD -User $User -Filename $Filename Function Set-Photo-AzureAD { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) $Result=-1 if ($global:have_connection_AzureAD -eq $false) { Write-Error -Message "Need connection to AzureAD to upload $User photo $Filename..." } else { $newfile = Get-Photo-Below-Size -Filename $filename -MaxSize 100000 $photo = [byte[]](Get-Content $newfile -Encoding byte) Try { $UserAzure = Get-AzureADUser -ObjectId "${User}@${global:domain}" Set-AzureADUserThumbnailPhoto -ObjectId $UserAzure.ObjectId -FilePath $newfile If ($newfile -ne $Filename) { Remove-Item-Later -Path $newfile } Log "Set AzureAD photo for ${User} to ${newfile}" $Result = 0 } Catch { if ($_.Exception.Message.Contains("currently undergoing migration")) { Write-Error "AzureAD is undergoing migration, skipping photo for $User for now." } else { Write-Error $_ } } Remove-Variable newfile } return $Result } # Get-Photo-AzureAD -User $User -Filename $Filename Function Get-Photo-AzureAD { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) if ($global:have_connection_AzureAD -eq $false) { Write-Error -Message "Need connection to AzureAD to upload $User photo $Filename..." } else { $User = Get-AzureADUser -ObjectId "${User}@${global:domain}" Get-AzureADUserThumbnailPhoto -ObjectId $User.ObjectId -FileName $Filename } } # Set-Photo-Outlook -User $User -Filename $Filename Function Set-Photo-Outlook { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) $Result = -1 if ($global:have_connection_Outlook -eq $false) { Write-Error -Message "Need connection to Exchange to upload $User photo $Filename..." } else { $newfile = Get-Photo-Below-Size -Filename $filename -MaxSize 500000 $photo = [byte[]](Get-Content $newfile -Encoding byte) Try { Set-UserPhoto "${User}@${global:domain}" -PictureData $photo -Confirm:$false If ($newfile -ne $Filename) { Remove-Item -Path $newfile } Log "Set Outlook photo for ${User} to ${newfile}" $Result = 0 } Catch { if ($_.Exception.FullyQualifiedErrorId.Contains("Microsoft.Exchange.Configuration.CmdletProxyException")) { Write-Error "Outlook connecton malfunctioned on Microsoft side, skipping photo for $User for now." } else { Write-Error $_ } } Remove-Variable newfile } return $Result } # Get-Photo-Outlook -User $User -Filename $Filename Function Get-Photo-Outlook { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) if ($global:have_connection_Outlook -eq $false) { Write-Error -Message "Need connection to Exchange to upload $User photo $Filename..." } else { [System.Io.File]::WriteAllBytes($Filename,(Get-UserPhoto "${User}@${global:domain}").PictureData) } } # Set-Photo-SQLServer -User $User -Filename $Filename Function Set-Photo-SQLServer { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) $Result = -1 $newfile = Get-Photo-Below-Size -Filename $filename -MaxSize 1000000 # 1 MB $photo = [byte[]](Get-Content $newfile -Encoding byte) $photobase64 = [convert]::ToBase64String($photo) Import-Module SqlServer -Verbose:$false # Connection info $params = @{'server'='SERVERNAME\DBNAME';'Database'='dbname'} $SQLCred = Get-Shared-Credential -User "dbServiceAccount" -PasswordCategory "dbServiceAccount" Try { $upn = [string]( Get-ADUser $User -Properties UserPrincipalName ).UserPrincipalName $SqlCmd = @" UPDATE [Stage].[Associate_AD_Photos] SET Encoded_AD_Photo = N'$photobase64' WHERE userPrincipalName = '$upn'; "@ Invoke-Sqlcmd @params -Query $SqlCmd -Credential $SQLCred If ($newfile -ne $Filename) { Remove-Item -Path $newfile } Log "Set SQLServer photo for ${User} to ${newfile}" $Result = 0 } Catch { Write-Error $_ } Remove-Variable newfile return $Result } # Get-Photo-SQLServer -User $User -Filename $Filename Function Get-Photo-SQLServer { Param ( [Parameter(Mandatory=$True)] $User, [Parameter(Mandatory=$True)] $Filename ) Import-Module SqlServer -Verbose:$false # Connection info $params = @{'server'='SERVERNAME\DBNAME';'Database'='dbname'} $SQLCred = Get-Shared-Credential -User "dbServiceAccount" -PasswordCategory "dbServiceAccount" Try { $upn = [string]( Get-ADUser $User -Properties UserPrincipalName ).UserPrincipalName $SqlCmd = @" SELECT UserPrincipalName as upn, Encoded_AD_photo as photo FROM stage.associate_ad_photos WHERE UserPrincipalName = '$upn'; "@ $Result = Invoke-Sqlcmd @params -Query $SqlCmd -Credential $SQLCred -MaxCharLength 200000 $photo = [byte[]][System.Convert]::FromBase64String($Result.photo) [System.Io.File]::WriteAllBytes($filename,$photo) } Catch { Write-Error $_ } } # Set-Photo-All -User $User -Filename $Filename Function Set-Photo-All { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) $Result = 0 $Result -= Set-Photo-AD -User $User -Filename $Filename # AzureAD synchronizes from on-prem AD approximately every 15 minutes. Confirmed 2019-11-22 11:27. #$Result -= Set-Photo-AzureAD -User $User -Filename $Filename $Result -= Set-Photo-Outlook -User $User -Filename $Filename $Result -= Set-Photo-SQLServer -User $User -Filename $Filename return $Result } Function Get-Photo-All { Param( [Parameter(Mandatory=$true)] $User, [Parameter(Mandatory=$true)] $Filename ) $FilenameAD = ${Filename} -Replace "\..{1,5}$","_AD.$( $Filename.Split(".")[-1] )" $FilenameAzureAD = ${Filename} -Replace "\..{1,5}$","_AzureAD.$( $Filename.Split(".")[-1] )" $FilenameOutlook = ${Filename} -Replace "\..{1,5}$","_Outlook.$( $Filename.Split(".")[-1] )" $FilenameSQLServer = ${Filename} -Replace "\..{1,5}$","_SQLServer.$( $Filename.Split(".")[-1] )" Log "FileNameAD = $FileNameAD" Get-Photo-AD -User $User -Filename $FilenameAD Log "FileNameAzureAD = $FileNameAzureAD" Get-Photo-AzureAD -User $User -Filename $FilenameAzureAD Log "FileNameOutlook = $FileNameOutlook" Get-Photo-Outlook -User $User -Filename $FilenameOutlook Log "FileNameSQLServer = $FileNameSQLServer" Get-Photo-Outlook -User $User -Filename $FilenameSQLServer } Function Remove-Item-Later { # I think this one causes tons of memory performance problems if called hundreds of times. # Needed only for AzureAD connection which is not even used for main processes that use this lib. Param( [Parameter(Mandatory=$true)] $Path ) $scriptblock = {param($Path) Start-Sleep -Seconds 3 ; Remove-Item -Confirm:$false -Path $Path ; } $null = Start-Job -ScriptBlock $scriptblock -Arg $Path } # Process-User-Photo -File "E:\test\john.doe@example.com.jpg" Function Process-User-Photo { Param( [Parameter(Mandatory=$true)] [System.Io.FileInfo] $File ) # Cheater method to drop off the .jpg ending $email = ($file.Name -Split("\.[^.]{1,6}$"))[0] # Derive user samaccountname from email address Try { $samaccountname = (Get-ADUser -Filter {Emailaddress -eq $email}).SamAccountName } Catch { $samaccountname = $null } if ($samaccountname -eq $null) { Move-Item -Path $file.Fullname -Destination "$ErrorOutdir\" Write-Warning -Message "Moved unidentifiable $file..." } else { Log "Set-Photo-All -User $samaccountname -Filename $file.Fullname" $Result = Set-Photo-All -User $samaccountname -Filename $file.Fullname if ($Result -eq 0) { Log "Success: ${file}" Move-With-Rename -Path $file.Fullname -Destination "$MoveToOutdir\" } } } # useful for renaming the file with a datestamp before moving it, if the destination file already exists # Move-With-Rename -Path $Path -Destination $Destination Function Move-With-Rename { Param( [Parameter(Mandatory=$true)] $Path, [Parameter(Mandatory=$true)] $Destination ) Try { Move-Item -Path $Path -Destination $Destination -ErrorAction Stop } Catch { if ($_.Exception.Message.Contains("Cannot create a file when that file already exists")) { # try the rename $timestamp = Get-Date -Format yyyy-MM-ddTHHmmss $replace = ".$timestamp" + '$1' $newPath = $Path -Replace("(\..{1,6})$",$replace) Move-Item -Path $Path -Destination $newPath Move-Item -Path $newPath -Destination $Destination } else { Write-Error $_ } } } ## COMMENTS # OPEN SESSIONS #$null = Open-Connection-AzureAD #$null = Import-PSSession ($session = Open-Connection-Outlook) -AllowClobber # MAIN #Set-Photo-All -User "bgstack15" -Filename "E:\test\Notre Dame.jpg" # CLOSE SESSIONS #Close-Connection-AzureAD #Close-Connection-Outlook $session