I have been searching and searching through articles and blogs and discussion forums for a way to accomplish my latest endeavor, a way to extract information from our student information system and populate the matching Active Directory accounts.
The issue I have run into from all of the aforementioned electronic stops is the fact that our forest has 18 domains. Finding an easy way to update the accounts in one CSV file to an account which can be in any domain at any time has been quite a challenge. However, I finally figured out a way that worked for me and thought I would share as I have not been able to find anything in the past four months that did the job in one swoop.
First off I setup my Windows 8 workstation by enabling the PowerShell 3.0 ISE 64bit console and the windows SQL Server 2008 R2 client tools. The workstation is also located on a subnet which can access not only the SQL server of the SIS database but also one domain controller for every domain in our forest. I was also provided with an account to the database which has the permission to read the tables for the data I need to extract and an account which has the ability to update all student Active Directory accounts.
I know this may sound IT101 but for those just stepping into these shoes, every little bit of knowledge which may be obvious to those of us seniors in the game is not to those just learning to walk. And the one thing I have learned in the 13 years in this business is to never assume others know anything.
Once the workstation that will be running the script is all set and tested, open the 64bit ISE console.
First we must be able to make a connection to the SQL server and Active Directory.
import-module ActiveDirectory Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100
Next we enter the t-sql used to query the SIS database and then export it to a CSV file (note: going into the sql code required for this is beyond this post)
$SQLquery =@”
SQL code here “@
$result = invoke-sqlcmd -query $SQLquery -serverinstance “servername” -database dbname
$result | export-csv “C:\PSScripts\Update_Students.csv” -notypeinformation
Next we will import the CSV file and set the script to create a log file to review errors after the script completes.
$SyIDOutputFile = “C:\PSScripts\UpdateReport.log” $upn = import-csv “C:\PSScripts\Update_Students.csv”
Now that we’ve completed the setup, let’s get down to business. We will setup the loop to handle each account in the CSV file and do some work. Instead of throwing all at you at once, I’m going to continue to break it down so you will know what each line is actually doing. This should make it easier for you to take this script and make it your own.
We create the ‘foreach’ loop then cycle through each line in the CSV file pulling the information we will need to use and setting it to a variable.
foreach ($u in $upn) { $emailAddress = $u.email $stuNum = $u.StuNumber $empNumber = $u.StudentID
Now let’s query the Global Catalog for each student in the CSV file and pull the distinguished name attribute and assign to a variable.
$ADUser = get-adobject -Filter {mail -like $emailAddress} -server servername:3268 -Properties distinguishedName | select-object -property distinguishedName
$ADUser | ForEach-Object -process { $dn = $ADUser.distinguishedName
Now we will need a Switch statement for each domain. We will use a wildcard since the distinguished name for each user will contain the domain name but they may be in different organization units. I have only included two entries below but you would need a statement for each domain in your forest. You can also include other variables with each statement that pertains to that domain such as a specific display name, department, etc. You are populating LDAP attributes and no longer just the ones available to the Global Catalog so the list is long of what you can include. For simplicity we are just including the server which is needed to update that student account.
switch -Wildcard ($dn)
{
“*,DC=X1,DC=X,DC=X” {$server = “child1.X1.X.X”}
“*,DC=X2,DC=X,DC=X” {$server = “child2.X2.X.X”}
} #end switch
Now let’s take all of the information we have gathered and either update the account or write to the log file that it cannot be done listing the distinguished name reported and the server that was tried. This should help troubleshoot those that could not be updated.
Try{
set-aduser -Identity $stuNum -employeeNumber $empNumber -server $server
Write-host “$stuNum $emailAddress will be set to $empNumber on $server”
} #end Try
Catch
{
write-warning “$stuNum does not exist on $server to be updated as $dn”
Out-File -FilePath $SyIDOutputFile -InputObject (“$stuNum does not exist on $server to be updated as $dn”) -Encoding UTF8 -append
}#end Catch
}#end foreach-object
}# end foreach
Now let’s send the log file to our account managers to review any errors that occurred during the script
write-host “End of Active Directory Updates”
#Send Log File via email
$SmtpServer = “enter ip address of smtp relay server”
$Smtp = New-Object Net.Mail.SmtpClient($SmtpServer)
$SmtpFile = New-Object Net.Mail.Attachment($SyIDOutputFile)
$SmtpMsg = New-Object Net.Mail.MailMessage
$SmtpMsg.From = “enter address to send email from”
$SmtpMsg.To.Add(“enter email address to send log to”)
$SmtpMsg.Subject = “Student Update Log”
$SmtpMsg.Body = “Attached is the latest student update log.”
$SmtpMsg.Attachments.Add($SmtpFile)
$Smtp.Timeout = “30000”
$Smtp.Send($SmtpMsg)
Lastly, let’s clean things up
#Clean up
$SmtpFile.Dispose()
remove-item “C:\PSScripts\Update_Students.csv”
remove-item “C:\PSScripts\UpdateReport.log”
And that’s it. Now I do like to give credit where it is deserved. The email portion which sends the log file I found in the Technet blogs from Roman Zarka pertaining to Office 365. But it works regardless of what the script does and I have used it many times over.
I hope this will help the many I read entries from looking for the same solution.
reignstayr 