Updating Active Directory using PowerShell and SQL

There are a ton of examples out there on how to use CSV files to import user accounts into AD, but personally I prefer SQL to power the data that I’m pulling into my scripts when I can. Case in point, we just finished the migration to our new hosted mail server and in the process about a third of our users had their default email address changed (all the old users were forced to use an updated naming scheme). As you can image that caused all kinds of issues, but because I already had an inventory of Active Directory and the Exchange Global Address List in SQL, I was able to quickly compare the two, find the discrepancies and put this script together to sync everything back up.

Add-PSSnapin Quest.ActiveRoles.ADManagement
Import-Module SQLPS -DisableNameChecking

$SQLServer = "SERVER"
$SQLDatabase = "Inventory"
# This query displays a list of users whos AD mail attribute do not match their GAL address
# by looking for instances where the first and last names match, but email address does not
$SQLQuery = "SELECT ADUserAccounts.UserID, MailUserObjects.PrimarySMTPAddress
FROM ADUserAccounts INNER JOIN MailUserObjects ON (ADUserAccounts.First_Name = MailUserObjects.FirstName) 
    AND (ADUserAccounts.Last_Name = MailUserObjects.LastName) 
    AND (ADUserAccounts.Mail <> MailUserObjects.PrimarySMTPAddress)
WHERE (ADUserAccounts.First_Name <> '') AND (ADUserAccounts.Last_Name <> '')"

$list = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
ForEach ($row in $list) {
    [string]$UserID = $row.UserID
    [string]$PrimarySMTPAddress = $row.PrimarySMTPAddress
    Set-QADUser $UserID -email $PrimarySMTPAddress
}

As another example, everyone also just had their job title changed, so here is another version of this same logic, powered by a different SQL query to get the needed data and then set it in AD.

Add-PSSnapin Quest.ActiveRoles.ADManagement
Import-Module SQLPS -DisableNameChecking

$SQLServer = "SERVER"
$SQLDatabase = "Inventory"
# This query displays a list of users whos AD job title attribute do not match HR (with some filters)
$SQLQuery = "SELECT ADUserAccounts.UserID, StaffReport.[Job Title] as newTitle, ADUserAccounts.Title as oldTitle
    FROM StaffReport INNER JOIN ADUserAccounts ON StaffReport.[File Number] = ADUserAccounts.Employee_ID
    WHERE (ADUserAccounts.Title <> StaffReport.[Job Title]) AND (StaffReport.[Employee Status Type] <> 'Terminated') 
        AND (ADUserAccounts.Account_Status <> 'Disabled') AND ((ADUserAccounts.Employee_Type = 'Employee') OR 
        (ADUserAccounts.Employee_Type = 'Consultant') OR (ADUserAccounts.Employee_Type = 'Contractor'))"

$list = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
ForEach ($row in $list) {
    [string]$UserID = $row.UserID
    [string]$newTitle = $row.newTitle
    Set-QADUser $UserID -Title $newTitle
}
Advertisements
This entry was posted in Enterprise Management, Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s