Updates Active Directory Job Title / Phone / Department / Office / Address / Postcode based on a T-SQL Query and sends an E-mail report

Update-AD-Users-From-SQL.ps1

## SQL Powershell Module
Import-Module "sqlps" -DisableNameChecking

## AD Powershell Module
import-module "ActiveDirectory" -DisableNameChecking

## Uncomment for Verbose output

# $VerbosePreference = "Continue"

## Email Settings

$smtpServer = "smtp.company.co.uk"
$smtpFrom = "[email protected]"
$smtpTo = $smtpFrom
$messageSubject = "AD Import From HR Database Results"
$messagebody = @"
AD Import From HR Database Results:


"@


## Create PSDrive for the Database Server

New-PSDrive -Name CompanySQLServer -PSProvider SQLSERVER -Root SQLSERVER:\SQL\CompanySQLServer\DEFAULT\Databases

## Query to get info from SQL Server.

$query = @"
select
	Emps.Loginname as 'SamAccountName',
    	Emps.PreferredName as 'DisplayName',
	Emps.TelExt as 'OfficePhone',
	Emps.WorkMobile as 'MobilePhone',
	Pos.Description as 'Title', 
	Depts.Name as 'Department',
	Offices.Name as 'Office',
	Stuff(
			Coalesce(',' + Offices.Address1,'')
			+ Coalesce(', ' + Offices.Address2,'')
			+ Coalesce(', ' + Offices.Address3,'')
			+ Coalesce(', ' + Offices.Address4,'')
			, 1, 1, '') as 'StreetAddress',
	CASE	
			WHEN NULLIF(Offices.Address4, '') IS NOT NULL THEN Offices.Address4
			WHEN NULLIF(Offices.Address3, '') IS NOT NULL THEN Offices.Address3
			WHEN NULLIF(Offices.Address2, '') IS NOT NULL THEN Offices.Address2
			WHEN NULLIF(Offices.Address2, '') IS NOT NULL THEN Offices.Address1
	END AS 'Town',
	Offices.Postcode as 'Postalcode'
from CompanyPersonnel.dbo.Employees as Emps
		
	Join CompanyPersonnel.dbo.Positions as Pos on Pos.ID = Emps.CurrentPosition
	Join Company.dbo.Depts as Depts on Depts.ID = Emps.CurrentDept
	Join Company.dbo.Offices as Offices on Offices.ID = Emps.CurrentOffice
Where
Emps.IsCurrent = 1
"@

## Counters for Import

# Stats

$HRrecordsTotal = 0
$ADrecordsUpdated = 0

$ADOfficePhoneUpdated = 0
$ADMobilePhoneUpdated = 0
$ADOTitleUpdated = 0
$ADDepartmentpdated = 0
$ADOfficeUpdated = 0

# AD issues
$ADmissing = 0

# HR DB issues

$HRnoOfficePhone = 0
$HRnoMobilePhone = 0
$HRnoTitle = 0
$HRnoDepartment = 0
$HRnoOffice = 0

## Set Location to the PSDrive

$oldlocation = Get-Location
Set-Location CompanySQLServer:

## Run the SQL Query and store the results

$users = Invoke-Sqlcmd $query

$HRrecordsTotal = $users.count

## Update each AD user account in turn by running through the HR records

foreach ($user in $users) {
     
     # Try and find the AD User
     
     $ADUser = $(try {Get-ADUser $user.SamAccountName -Properties Office,OfficePhone,MobilePhone,Department,Title,City,PostalCode,StreetAddress} catch {$null})
     
     #Check if we found it
     
     if ($ADuser -ne $null) {
     
            $ADUpdated = $false
            
            #We did so check the HR data too before we try and use it
            if (-Not ([string]::IsNullOrEmpty($user.Title))) {
                        if (($user.Title -ne $ADUser.Title)) { #Check if we need to update 
                            # Update the Job Title attribute
                            Write-Verbose ("Updating Job Title to " + $user.Title + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")")
                            Set-ADUser $user.SamAccountName -Title $user.Title
                            $messagebody += "Updating Job Title to " + $user.Title + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")" + ". `r`n"
                            $ADOTitleUpdated++ 
                            $ADUpdated = $true
                            }
                } else {
                    #HR Database is wrong
                    $HRnoTitle++
                    $messagebody += "Missing Job Title in HR Record for " + $user.DisplayName  + ". `r`n"
                    Write-Warning ("Missing Job Title in HR Record for " + $user.DisplayName)
                }
            if (-Not ([string]::IsNullOrEmpty($user.OfficePhone))) {
                        if (($user.OfficePhone -ne $ADUser.OfficePhone)) { 
                            # Update the Office Phone attribute
                            Write-Verbose ("Updating Office Phone Number to " + $user.OfficePhone + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")")
                            Set-ADUser $user.SamAccountName -OfficePhone $user.OfficePhone
                            $messagebody += "Updating Office Phone Number to " + $user.OfficePhone + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")" + ". `r`n"
                            $ADOfficePhoneUpdated++ 
                            $ADUpdated = $true 
                            }
                } else {
                    #HR Database is wrong
                    $HRnoOfficePhone++
                    $messagebody += "Missing Office Phone in HR Record for " + $user.DisplayName  + ".`r`n"
                    Write-Warning ("Missing Office Phone in HR Record for " + $user.DisplayName)
                }                

            if (-Not ([string]::IsNullOrEmpty($user.MobilePhone))) {
                        if (($user.MobilePhone -ne $ADUser.MobilePhone)) {
                            # Update the Mobile Phone attribute
                            Write-Verbose ("Updating Mobile Phone Number to " + $user.MobilePhone + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")")
                            Set-ADUser $user.SamAccountName -MobilePhone $user.MobilePhone
                            $messagebody +=  "Updating Mobile Phone Number to " + $user.MobilePhone + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")" + ". `r`n"
                            $ADMobilePhoneUpdated++ 
                            $ADUpdated = $true
                            }
                } else {
                    #HR Database isn't really wrong here. Not everyone gets a mobile. So no warning/email. Just report if we're Verbose.
                    $HRnoMobilePhone++
                    Write-Verbose ("Missing Work Mobile Phone in HR Record for " + $user.DisplayName) 
                }                
            if (-Not ([string]::IsNullOrEmpty($user.Department))) { 
                    if (($user.Department -ne $ADUser.Department)) {
                            # Update the Department attribute
                            Write-Verbose ("Updating Department to " + $user.Department + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")")
                            Set-ADUser $user.SamAccountName -Department $user.Department
                            $messagebody += "Updating Department to " + $user.Department + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")" + ". `r`n"
                            $ADDepartmentUpdated++
                            $ADUpdated = $true
                            }
                } else {
                    #HR Database is wrong
                    $HRnoDepartment++
                    $messagebody += "Missing Department in HR Record for " + $user.DisplayName  + ". `r`n"
                    Write-Warning ("Missing Department in HR Record for " + $user.DisplayName)
                }                
            if (-Not ([string]::IsNullOrEmpty($user.Office))) { 
                    if (($user.Office -ne $ADUser.Office)) {
                        # Update the Office attribute
                        Write-Verbose ("Updating Office to " + $user.Office + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")")
                        Set-ADUser $user.SamAccountName -Office $user.Office
                        $messagebody += "Updating Office to " + $user.Office + " for " + $user.SamAccountName + " (" + $user.DisplayName + ")" + ". `r`n"
                        $ADOfficeUpdated++
                        $ADUpdated = $true
                        }
                } else {
                    #HR Database is wrong
                    $HRnoOffice++
                    $messagebody += "Missing Office in HR Record for " + $user.DisplayName + ". `r`n"
                    Write-Warning ("Missing Office in HR Record for " + $user.DisplayName)
                } 
            if ($ADUpdated) {$ADrecordsUpdated++}               
            } else {
                    #User is missing in AD but Exists in HR Database
                    $ADmissing++
                    Write-Warning ($user.SamAccountName + " (" + $user.DisplayName + ")" + " exists in HR Database but doesn't in AD!")
                    $messagebody += $user.SamAccountName + " (" + $user.DisplayName + ")" + " exists in HR Database but doesn't in AD!`r`n"
                    }
      
}
    
# Report Errors on Console

if ($HRnoTitle -gt 0) {Write-Warning ("Total Missing Job Titles in HR DB: " + $HRnoTitle)}   
if ($HRnoOfficePhone -gt 0) {Write-Warning ("Total Missing Office Phone Numbers in HR DB: " + $HRnoOfficePhone)}
if ($HRnoMobilePhone -gt 0) {Write-Verbose ("Total Missing Mobile Phone Numbers HR DB: " + $HRnoMobilePhone)}   # Not everyone is supposed to have a company mobile
if ($HRnoDepartment -gt 0)  {Write-Warning ("Total Missing Departments in HR DB: " + $HRnoDepartment)}   
if ($HRnoOffice -gt 0) {Write-Warning ("Total Missing Offices in HR DB: " + $HRnoOffice)}  
if ($ADmissing -gt 0) {Write-Warning ("Missing AD accounts: " + $ADmissing)}   

# Construct totals for Email
$messagebody +=  @"

AD Import From HR Database Summary:

Total HR Records Scanned: $HRrecordsTotal.
Total Active Directory Accounts Updated:  $ADrecordsUpdated.

Total Updated Job Titles: $ADOTitleUpdated.
Total Updated Office Phone Numbers: $ADOfficePhoneUpdated.
Total Updated Mobile Phone Numbers:  $ADMobilePhoneUpdated.
Total Updated Departments: $ADDepartmentUpdated.
Total Updated Offices: $ADOfficeUpdated.

Missing Job Titles in HR DB: $HRnoTitle.
Missing Office Phone Numbers in HR DB: $HRnoOfficePhone.
Missing Mobile Phone Numbers HR DB: $HRnoMobilePhone.
Missing Departments in HR DB: $HRnoDepartment.
Missing Offices in HR DB: $HRnoOffice.

Missing Active Directory accounts: $ADmissing.
"@ 

# Send Email
                
Send-MailMessage -From $smtpFrom -To $smtpTo -Subject $messageSubject -Body $messagebody -SmtpServer $smtpServer               

# Clean up

Set-Location $oldlocation
Remove-PSDrive CompanySQLServer