SIMS report -> New student AD accounts

Shaun_Dark_Lord · February 26, 2018

Hi All

On my quest to get Google Classroom up and running, I needed to get student UPNs out of SIMS and into AD. This morning I thought I may as well automate the creation of student user accounts, as I had most of the Powershell script figured out already.

The first step was to leverage a SIMS tool called "CommandReporter". This can be found under "C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe" on a SIMS client. As I'm planning to run this regularly, I'm running the tool on the SIMS server. The tool can run as any SIMS user and output a SIMS report to a CSV.

I created a new SIMS user with a slightly locked down version of the "Classroom Teacher" permissions. I basically disabled all of the write access this user has to SIMS. I logged into SIMS as this user, and created a report with the following fields selected;

I saved this report with a simple name that could easily be used by CommandReporter.

I then created a scheduled task to run CommandReporter as often as needed, using the following command;

"C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe" /USER:mySIMSUser /PASSWORD:password123 /SERVERNAME:SIMSSERVER\sims2014 /DATABASENAME:sims /REPORT:myReportname /OUTPUT:"C:\Folder\StudentUserinfo.csv"

Please note that the output from CommandReporter can differ significantly from running the report in SIMS. I discovered this after spending ages writing a script to reformat a CSV, only to find that my scheduled report was completely different!

So now I have a CSV file on my SIMS server. You could put it somewhere else, but I simply decided to create a hidden share of the parent folder, allowing my DC read access to this share.

On my DC, I created the following powershell script;

##Set the log file
$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfilepath = "C:\UserCreate\StudentCreator\Logs\"
$Logfile = $Logfilepath + $Logfilename + ".csv"

##Import and modify SIMS Report
$Names = import-csv "\\SIMSSERVER\SharedFolder$\StudentUserinfo.csv"
$Names | foreach-object {
   $_.LegalSurname = $_.LegalSurname.replace(" ","") #Removes spaces from surname
   $_.LegalSurname = $_.LegalSurname.replace("-","") #Removes dashes from surname
   $_.LegalForename = $_.LegalForename.replace(" ","") #Removes spaces from forename
   $_.LegalForename = $_.LegalForename.replace("-","") #Removes dashes from forename
   $_.Year = $_.Year.replace("  "," ") #Removes double spaces from Year
   }
$Names | export-csv "C:\UserCreate\StudentCreator\StudentUserinfo.csv" -notype #Writes modified local copy of csv for further processing

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot
$SearchOU = "OU=Students,OU=CCW Users,DC=ccw,DC=kent,DC=sch,DC=uk" #Used when checking if user already exists.

$Students = import-csv "C:\UserCreate\StudentCreator\StudentUserinfo.csv" #Imports modified local copy of csv
       foreach ($Student in $Students){
           $LegalSN = $Student.LegalSurname
           $LegalFN = $Student.LegalForename
           $SN = $Student.Surname
           $FN = $Student.Forename
           $DisplayName = $Student.Name
           $SIMSUPN = $Student.UPN
           $Year = $Student.Year
           $AccountExists = "No" #Sets initial value.
           $StudOU = ""
           if ($Year -eq "Year 7") {
               $StudOU = "2017"
           }
           if ($Year -eq "Year 8") {
               $StudOU = "2016"
           }
           if ($Year -eq "Year 9") {
               $StudOU = "2015"
           }
           if ($Year -eq "Year 10") {
               $StudOU = "2014"
           }
           if ($Year -eq "Year 11") {
               $StudOU = "2013"
           }
           if ($Year -eq "Year 12") {
               $StudOU = "2012"
           }
           if ($Year -eq "Year 13") {
               $StudOU = "2011"
           }

           ##Check if student already exists
           $UserList = Get-ADUser -SearchBase $SearchOU -Filter {POBox -eq $SIMSUPN}
           foreach ($User in $UserList){
               $ADUserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               #$FullName = $UserInfo.displayname
               $ADSAM = $ADUserInfo.samAccountName
               $ADUPN = $ADUserInfo.POBox
               
               if ($ADUPN -eq $SIMSUPN) {
                   #"$DisplayName's UPN already assigned to $ADSAM"
                   $AccountExists = "Yes"
               } else {
                   #"$DisplayName's UPN not assigned to any existing account."
               }
           }
           if ($AccountExists -eq "No" -And $StudOU -ne "" -And $SIMSUPN -ne "") {
               #"$DisplayName's UPN not assigned to any existing account."
               $NewSAM = $LegalFN.substring(0,2) + $LegalSN.substring(0,4) + $StudOU.substring(2,2) #Generates username from legal name and year of entry.
               "Creating account $NewSAM in $StudOU"
               $Email = $NewSAM + "@mydomain.com"
               $UPN = $NewSAM + "$dnsroot"
               $Date = Get-Date -format dd/MM/yyyy
               $Desc = "Created on " + $Date
               $HomeDirectory ='\\studentfs\Home$\$StudOU\$NewSAM' -f $NewSAM; #change it with your servername and share
               $OU="OU=$StudOU,OU=Students,OU=CCW Users,DC=ccw,DC=kent,DC=sch,DC=uk" ## Important change to the ou where you need to create users exapmle $OU = "cn=thisOu,dc=domain, dc=com"

            ##Create User##
               if (dsquery user -samid $NewSAM){
                   "User $NewSAM Already Exists"
                   ##Write to log file##
                   $Loginfo = $NewSAM + ' already exists!'
                   "$Loginfo" | Out-File $Logfile -append
               } else {
                New-ADUser -Name "$DisplayName" -SamAccountName $NewSAM -UserPrincipalName $UPN -DisplayName "$DisplayName" -GivenName $FN -Surname $SN -AccountPassword (ConvertTo-SecureString “password123” -AsPlainText -force) -Enabled $true -Path "$OU"
                Add-ADGroupMember -Identity $StudOU -Members $NewSAM
                   Add-ADGroupMember -Identity "CCW Students" -Members $NewSAM
                   Set-ADuser $NewSAM -HomeDrive "N:" -HomeDirectory "\\studentfs\Home$\$StudOU\$NewSAM" -ProfilePath "\\studentfs\Profile$\$StudOU\$NewSAM"
                   Set-ADUser -Identity $NewSAM -EmailAddress $Email
                   Set-ADUser -Identity $NewSAM -Description $Desc
                   Set-ADUser -Identity $NewSAM -POBox $SIMSUPN #Required for Google Class Assignment
                   Set-ADUser -Identity $NewSAM -ChangePasswordAtLogon $true

                   ##Create Home Directory##
                   New-Item \\studentfs\Home$\$StudOU\$NewSAM -type directory
                   $acl = Get-Acl \\studentfs\Home$\$StudOU\$NewSAM
                   $permission = "ccw\$NewSAM","Modify", "ContainerInherit, ObjectInherit", "None", "Allow"
                   $accessRule = new-object System.Security.AccessControl.FileSystemAccessRule $permission
                   $acl.SetAccessRule($accessRule)
                   $acl | Set-Acl \\studentfs\Home$\$StudOU\$NewSAM
                   "User $NewSAM had no Home folder, this as been created"

                   ##Write to log file##
                   $Loginfo = $NewSAM + ',' + $StudOU + ',' + $Email + ',' + $SIMSUPN
                   "$Loginfo" | Out-File $Logfile -append
               }
               ;
           }
       }

Some further explanation of our setup;

Each yeargroup's users are placed in a container based on the year they would have started in year 7. So this year's year 7 students are in the "2017" OU. They are also placed into a "2017" group, and their home folder and profile folders are also placed into parent "2017" folders.

We fairly recently adopted a new username format for students, using the first two letters of their forename, first four letters of their surname and two digits of their year of entry. Ben Smith in year 7 would be given the username of besmit17.

As some usernames are in a completely different format, I check to see if the SIMS UPN has already been assigned to another account (lines 54-68). For this we use the POBox AD property, which for existing students has already been populated with UPNs.

At the moment we just set all initial passwords to "password123". You could easily expand your SIMS report and set this to the student's DoB, postcode or something else.

I hope that this was helpful to someone else.

jthompson · February 26, 2018

I've been looking to start working on something just like this, but didin't really know how to do the PowerShell bit, so this is a great. We're quite often left out of the loop when students join/leave and we don't have Salamander (sorely tempted, though).

When I worked on the commandreporter part of it, I wanted to provide the SIMS report with the current date as a parameter (e.g. to just pick up students with an admission date of yesterday or later). You have to pass report parameters to commandreporter using an XML file, so I had a whole step set up where I had a python script first generate that day's parameters.xml file, before it's then used by commandreporter in the daily scheduled task. I just never moved on to using PowerShell to pick up the CSV output and actually create user accounts from it.

Shaun_Dark_Lord · March 5, 2018

Added lines 115 - 139 to check existing users against SIMS report and remove any students that are no longer here. If you would prefer not to remove the account, there are some commented-out lines 129-133 that can instead disable the account, and set the description to "Automatically disabled on $date".

Added lines 141 - 161 to check for the existence of the output log, and email it to someone.

##Set the log file
$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfilepath = "C:\UserCreate\StudentCreator\Logs\"
$Logfile = $Logfilepath + $Logfilename + ".csv"

##Import and modify SIMS Report
$Names = import-csv "\\SIMSServer\ReportShare$\StudentUserinfo.csv"
$Names | foreach-object {
   $_.LegalSurname = $_.LegalSurname.replace(" ","") #Removes spaces from surname
   $_.LegalSurname = $_.LegalSurname.replace("-","") #Removes dashes from surname
   $_.LegalForename = $_.LegalForename.replace(" ","") #Removes spaces from forename
   $_.LegalForename = $_.LegalForename.replace("-","") #Removes dashes from forename
   $_.Year = $_.Year.replace("  "," ") #Removes double spaces from Year
   }
$Names | export-csv "C:\UserCreate\StudentCreator\StudentUserinfo.csv" -notype #Writes modified local copy of csv for further processing

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot
$SearchOU = "OU=Students,OU=Users,DC=mydomain,DC=sch,DC=uk" #Used when checking if user already exists.

$Students = import-csv "C:\UserCreate\StudentCreator\StudentUserinfo.csv" #Imports modified local copy of csv
       foreach ($Student in $Students){
           $LegalSN = $Student.LegalSurname
           $LegalFN = $Student.LegalForename
           $SN = $Student.Surname
           $FN = $Student.Forename
           $DisplayName = $Student.Name
           $SIMSUPN = $Student.UPN
           $Year = $Student.Year
           $AccountExists = "No" #Sets initial value.
           $StudOU = ""
           if ($Year -eq "Year 7") {
               $StudOU = "2017"
           }
           if ($Year -eq "Year 8") {
               $StudOU = "2016"
           }
           if ($Year -eq "Year 9") {
               $StudOU = "2015"
           }
           if ($Year -eq "Year 10") {
               $StudOU = "2014"
           }
           if ($Year -eq "Year 11") {
               $StudOU = "2013"
           }
           if ($Year -eq "Year 12") {
               $StudOU = "2012"
           }
           if ($Year -eq "Year 13") {
               $StudOU = "2011"
           }

           ##Check if student already exists
           $UserList = Get-ADUser -SearchBase $SearchOU -Filter {POBox -eq $SIMSUPN}
           foreach ($User in $UserList){
               $ADUserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               #$FullName = $UserInfo.displayname
               $ADSAM = $ADUserInfo.samAccountName
               $ADUPN = $ADUserInfo.POBox
               
               if ($ADUPN -eq $SIMSUPN) {
                   #"$DisplayName's UPN already assigned to $ADSAM"
                   $AccountExists = "Yes"
               } else {
                   #"$DisplayName's UPN not assigned to any existing account."
               }
           }
           if ($AccountExists -eq "No" -And $StudOU -ne "" -And $SIMSUPN -ne "") {
               #"$DisplayName's UPN not assigned to any existing account."
               $NewSAM = $LegalFN.substring(0,2) + $LegalSN.substring(0,4) + $StudOU.substring(2,2)
               "Creating account $NewSAM in $StudOU"
               $Email = $NewSAM + "@theccw.net"
               $UPN = $NewSAM + "$dnsroot"
               $Date = Get-Date -format dd/MM/yyyy
               $Desc = "Created on " + $Date
               $HomeDirectory ='\\studentfs\Home$\$StudOU\$NewSAM' -f $NewSAM; #change it with your servername and share
               $OU="OU=$StudOU,OU=Students,OU=Users,DC=yourdomain,DC=sch,DC=uk" ## Important change to the ou where you need to create users exapmle $OU = "cn=thisOu,dc=domain, dc=com"

            ##Create User##
               if (dsquery user -samid $NewSAM){
                   "User $NewSAM Already Exists"
                   ##Write to log file##
                   $Loginfo = $NewSAM + ' already exists!'
                   "$Loginfo" | Out-File $Logfile -append
               } else {
                New-ADUser -Name "$DisplayName" -SamAccountName $NewSAM -UserPrincipalName $UPN -DisplayName "$DisplayName" -GivenName $FN -Surname $SN -AccountPassword (ConvertTo-SecureString “password” -AsPlainText -force) -Enabled $true -Path "$OU"
                Add-ADGroupMember -Identity $StudOU -Members $NewSAM
                   Add-ADGroupMember -Identity "CCW Students" -Members $NewSAM
                   Set-ADuser $NewSAM -HomeDrive "N:" -HomeDirectory "\\studentfs\Home$\$StudOU\$NewSAM" -ProfilePath "\\studentfs\Profile$\$StudOU\$NewSAM"
                   Set-ADUser -Identity $NewSAM -EmailAddress $Email
                   Set-ADUser -Identity $NewSAM -Description $Desc
                   Set-ADUser -Identity $NewSAM -POBox $SIMSUPN #Required for Google Class Assignment
                   Set-ADUser -Identity $NewSAM -ChangePasswordAtLogon $true

                   ##Create Home Directory##
                   New-Item \\student-fs\Home$\$StudOU\$NewSAM -type directory
                   #write "CCW\$NewSAM"
                   $acl = Get-Acl \\student-fs\Home$\$StudOU\$NewSAM
                   $permission = "ccw\$NewSAM","Modify", "ContainerInherit, ObjectInherit", "None", "Allow"
                   $accessRule = new-object System.Security.AccessControl.FileSystemAccessRule $permission
                   $acl.SetAccessRule($accessRule)
                   $acl | Set-Acl \\student-fs\Home$\$StudOU\$NewSAM
                   "User $NewSAM had no Home folder, this as been created"

                   ##Write to log file##
                   $Loginfo = 'Created,' + $NewSAM + ',' + $StudOU + ',' + $Email + ',' + $SIMSUPN
                   "$Loginfo" | Out-File $Logfile -append
               }
               ;
           }
       }

##Check for removed users
$UserList = Get-ADUser -SearchBase $SearchOU -Filter * | Where-Object {$_.POBox -ne ""}
foreach ($User in $UserList){
   $UserInfo = Get-ADUser -Identity $User -Properties *
   $SAM = $UserInfo.samAccountName
   $ADUPN = $UserInfo.POBox
   $Email = $UserInfo.EmailAddress
   $CanonicalName = $UserInfo.CanonicalName
   $StudOU = $CanonicalName.substring(35,4) #Grabs the OU from the CanonicalName property - Yours will likely have a different starting position and/or length.
   $SIMSUserList = Import-CSV -Path "C:\UserCreate\StudentCreator\StudentUserinfo.csv"
   If ($SIMSUserList.UPN -match $ADUPN) {
       #"User $SAM in $StudOU found in SIMS Report"
   } else {
       #"User $SAM in $StudOU not found in SIMS Report"
       #$Date = Get-Date -format dd/MM/yyyy
       #$Desc = "Automatically disabled on " + $Date
       #Disable-ADAccount -Identity $SAM # Disable User account
       #Set-ADUser -Identity $SAM -Description $Desc # Set description to date account was disabled
       #Set-ADUser -Identity $SAM -EmailAddress $null # Remove Email address  so no longer synced with
       Remove-ADUser -Identity $SAM -Confirm:$false # Remove Account
       ##Write to log file##
       $Loginfo = 'Deleted,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
       "$Loginfo" | Out-File $Logfile -append
   }
}

##Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = "smtp.mydomain.com"
       'To' = "Me "
       'From' = "Someone "
       'Subject' = "Results from Student Creation Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = "smtp.mydomain.com"
       'To' = "Me "
       'From' = "Someone "
       'Subject' = "Results from Student Creation Script"
       'Body' = "No changes today."
   }
   Send-MailMessage @emailOptions
}

This can be combined with the following script to automatically archive home folders to zip files and remove any zip files older than the specified threshold;

##Set the log file
$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfilepath = "C:\UserCreate\CleanUpHome\Logs\"
$Logfile = $Logfilepath + "Stud" + $Logfilename + ".csv"

$PathArray = "\\StudentFS\home$\2011\","\\StudentFS\home$\2012\","\\StudentFS\home$\2013\","\\StudentFS\home$\2014\","\\StudentFS\home$\2015\","\\StudentFS\home$\2016\","\\Student-FS\home$\2017\"
$leaversRoot = "\\StudentFS\home$\Old\"

foreach ($homeDriveRoot in $PathArray){

   # Get the list of folders in the home drive share
   $folders = Get-ChildItem $homeDriveRoot | Select -ExpandProperty Name
   
   # Get the list of active users from AD
   $activeUsers =  Get-ADUser -Filter {Enabled -eq $true} | Select -ExpandProperty SamAccountName
   
   # Compare the list of users to the list of folders
   $differences = Compare-Object -ReferenceObject $activeUsers -DifferenceObject $folders | ? {$_.SideIndicator -eq "=>"} | Select -ExpandProperty InputObject
   
   # For each folder that shouldn't exist, move it
   #$differences | ForEach-Object {Move-Item -Path "$homeDriveRoot$_" -Destination "$leaversRoot$_" -Force}
   $differences | ForEach-Object {
           $archive = $leaversRoot + $_ + ".zip"
           "Compressing $homeDriveRoot$_ to $archive"
           & "C:\Program Files\7-Zip\7z.exe" -mx=9 a "$archive" "$homeDriveRoot$_" -y -sdel
           ##Write to log file##
           $Loginfo = 'Created ' + $archive + ' on ' + $Logfilename
           "$Loginfo" | Out-File $Logfile -append
           #Remove-Item -Path $homeDriveRoot$_ -Force -Recurse
           }
}


### Delete archives older than 180 days ###

$limit = (Get-Date).AddDays(-180)
$path = "\\StudentFS\home$\Old"

# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.LastwriteTime -lt $limit } | Remove-Item -Force

### /Delete archives older than 180 days ###

Shaun_Dark_Lord · March 5, 2018

Pretty close to a final version. Have moved paths and preferences to top of the script so that it's a bit easier to run at a different school. Have also made the log and local data cache paths relative to the script.

You can also decide whether to delete or disable old student accounts with a Yes/No variable.

##Set paths and preferences
$LogfilePath = ".\Logs" ##Path where log files are created.
$DatafilePath = ".\Data" ##Path where data file is created.
$SIMSStudentReport = "\\SIMSServer\ReportShare$\StudentUserinfo.csv" ##Report containing list of current students
$ShortAD = "mydomain" ##Short AD name
$SearchOU = "OU=Students,OU=Users,DC=mydomain,DC=com" #OU Containing existing Students
$HomePath = "\\studentfs\Home$" ##Root home path containing yeargroup folders.
$HomeDrive = "H:" ##Drive letter to map to HomePath
$ProfilePath = "\\studentfs\Profile$" ##Root profile path containing yeargroup folders.
$StudMail = "@mygoogledomain.com" ##Mail domain for student accounts
$StudentGroup = "Students" ##Group to add all new students to.
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.mydomain.com" ##SMTP server for emailing log files.
$SMTPTo = "me " ##Recipient address for log files.
$SMTPFrom = "someone " ##Sender address for log files.
$DeleteUsers = "Yes" ##If set to "Yes", old user accounts will be deleted.  If set to something else, old user accounts will be disabled.
##/Set paths and preferences

##Set the log file
if (Test-Path -Path $LogfilePath) {
   #"Log folder exists"
} else {
   New-Item $LogfilePath -type directory #Creates log folder.
}
$LogfileName = Get-Date -UFormat "%Y%m%d"
$Logfile = $LogfilePath + '\' + $LogfileName + ".csv"

##Import and modify SIMS Report
if (Test-Path -Path $DatafilePath) {
   #"Data folder exists"
} else {
   New-Item $DatafilePath -type directory #Creates log folder.
}
$Names = import-csv $SIMSStudentReport
$Names | foreach-object {
   $_.LegalSurname = $_.LegalSurname.replace(" ","") #Removes spaces from surname
   $_.LegalSurname = $_.LegalSurname.replace("-","") #Removes dashes from surname
   $_.LegalForename = $_.LegalForename.replace(" ","") #Removes spaces from forename
   $_.LegalForename = $_.LegalForename.replace("-","") #Removes dashes from forename
   $_.Year = $_.Year.replace("  "," ") #Removes double spaces from Year
   }
$Names | export-csv "$DatafilePath\StudentUserinfo.csv" -notype #Writes modified local copy of csv for further processing

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

$Students = import-csv "$DatafilePath\StudentUserinfo.csv" #Imports modified local copy of csv
       foreach ($Student in $Students){
           $LegalSN = $Student.LegalSurname
           $LegalFN = $Student.LegalForename
           $SN = $Student.Surname
           $FN = $Student.Forename
           $DisplayName = $Student.Name
           $SIMSUPN = $Student.UPN
           $Year = $Student.Year
           $AccountExists = "No" #Sets initial value.
           $StudOU = ""
           if ($Year -eq "Year 7") {
               $StudOU = "2017"
           }
           if ($Year -eq "Year 8") {
               $StudOU = "2016"
           }
           if ($Year -eq "Year 9") {
               $StudOU = "2015"
           }
           if ($Year -eq "Year 10") {
               $StudOU = "2014"
           }
           if ($Year -eq "Year 11") {
               $StudOU = "2013"
           }
           if ($Year -eq "Year 12") {
               $StudOU = "2012"
           }
           if ($Year -eq "Year 13") {
               $StudOU = "2011"
           }

           ##Check if student already exists
           $UserList = Get-ADUser -SearchBase $SearchOU -Filter {$UPNVariable -eq $SIMSUPN}
           foreach ($User in $UserList){
               $ADUserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               #$FullName = $UserInfo.displayname
               $ADSAM = $ADUserInfo.samAccountName
               $ADUPN = $ADUserInfo.$UPNVariable
               
               if ($ADUPN -eq $SIMSUPN) {
                   #"$DisplayName's UPN already assigned to $ADSAM"
                   $AccountExists = "Yes"
               } else {
                   #"$DisplayName's UPN not assigned to any existing account."
               }
           }
           if ($AccountExists -eq "No" -And $StudOU -ne "" -And $SIMSUPN -ne "") {
               #"$DisplayName's UPN not assigned to any existing account."
               $NewSAM = $LegalFN.substring(0,2) + $LegalSN.substring(0,4) + $StudOU.substring(2,2)
               "Creating account $NewSAM in $StudOU"
               $Email = $NewSAM + $StudMail
               $UPN = $NewSAM + "$dnsroot"
               $Date = Get-Date -format dd/MM/yyyy
               $Desc = "Created on " + $Date
               $OU="OU=$StudOU,$SearchOU" ## Important change to the ou where you need to create users exapmle $OU = "cn=thisOu,dc=domain, dc=com"

            ##Create User##
               if (dsquery user -samid $NewSAM){
                   "User $NewSAM Already Exists"
                   ##Write to log file##
                   $Loginfo = 'Error - Already Exists,' + $NewSAM + ',,'
                   "$Loginfo" | Out-File $Logfile -append
               } else {
                New-ADUser -Name "$DisplayName" -SamAccountName $NewSAM -UserPrincipalName $UPN -DisplayName "$DisplayName" -GivenName $FN -Surname $SN -AccountPassword (ConvertTo-SecureString “password” -AsPlainText -force) -Enabled $true -Path "$OU"
                Add-ADGroupMember -Identity $StudOU -Members $NewSAM
                   Add-ADGroupMember -Identity $StudentGroup -Members $NewSAM
                   Set-ADuser $NewSAM -HomeDrive $HomeDrive -HomeDirectory "$HomePath\$StudOU\$NewSAM" -ProfilePath "$ProfilePath\$StudOU\$NewSAM"
                   Set-ADUser -Identity $NewSAM -EmailAddress $Email
                   Set-ADUser -Identity $NewSAM -Description $Desc
                   Get-ADUser $NewSAM -Properties $UPNVariable
                   $Command = "Set-ADUser $NewSAM -$UPNVariable $SIMSUPN"
                   Invoke-Expression $Command
                   Set-ADUser -Identity $NewSAM -ChangePasswordAtLogon $true

                   ##Create Home Directory##
                   New-Item $HomePath\$StudOU\$NewSAM -type directory
                   Copy-Item -Path C:\CCWUserCreate\AdobeAppdataFix\Adobe -Destination $HomePath\$StudOU\$NewSAM\AppData\Roaming -Force -Recurse
                   $acl = Get-Acl $HomePath\$StudOU\$NewSAM
                   $permission = "$ShortAD\$NewSAM","Modify", "ContainerInherit, ObjectInherit", "None", "Allow"
                   $accessRule = new-object System.Security.AccessControl.FileSystemAccessRule $permission
                   $acl.SetAccessRule($accessRule)
                   $acl | Set-Acl $HomePath\$StudOU\$NewSAM
                   "User $NewSAM had no Home folder, this as been created"

                   ##Write to log file##
                   $Loginfo = 'Created,' + $NewSAM + ',' + $StudOU + ',' + $Email + ',' + $SIMSUPN
                   "$Loginfo" | Out-File $Logfile -append
               }
               ;
           }
       }

##Check for removed users
$UserList = Get-ADUser -SearchBase $SearchOU -Filter {Enabled -eq $true} | Where-Object {$_.$UPNVariable -ne ""}
foreach ($User in $UserList){
   $UserInfo = Get-ADUser -Identity $User -Properties *
   $SAM = $UserInfo.samAccountName
   $ADUPN = $UserInfo.$UPNVariable
   $Email = $UserInfo.EmailAddress
   $CanonicalName = $UserInfo.CanonicalName
   $StudOU = $CanonicalName.substring(35,4)
   $SIMSUserList = Import-CSV -Path "$DatafilePath\StudentUserinfo.csv"
   If ($SIMSUserList.UPN -match $ADUPN) {
       #"User $SAM in $StudOU found in SIMS Report"
   } else {
       if ($DeleteUsers -eq "Yes") {
           #"User $SAM in $StudOU not found in SIMS Report and will be deleted"
           Remove-ADUser -Identity $SAM -Confirm:$false # Remove Account
           ##Write to log file##
           $Loginfo = 'Deleted,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       } else {
           #"User $SAM in $StudOU not found in SIMS Report and will be disabled"
           $Date = Get-Date -format dd/MM/yyyy
           $Desc = "Automatically disabled on " + $Date
           Disable-ADAccount -Identity $SAM # Disable User account
           Set-ADUser -Identity $SAM -Description $Desc # Set description to date account was disabled
           Set-ADUser -Identity $SAM -EmailAddress $null # Remove Email address  so no longer synced with
           ##Write to log file##
           $Loginfo = 'Disabled,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       }
   }
}

##Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "No changes today."
   }
   Send-MailMessage @emailOptions
}

I think this will be the version I'll be running for now. Please feel free to suggest edits or additional features that could be useful.

Again - I hope this turns out to be of use to someone else. If not, then I'm happy that it's starting to look a bit tidier.

derf · March 6, 2018

I've developed a whole automated system in our school to do this and much more (automated staff accounts, staff and parent mailing groups, timetables, visits to school doctors, photos from sims to AD etc.). All linked in to an intranet web server. Not quite ready to share yet. Works with the command reporter to suck the data from SIMS then PHP to process everything as I have a deep dislike for powershell

Shaun_Dark_Lord · March 8, 2018

Minor edit - Was concerned what would happen if the SIMS report was incomplete, so added a check for the existence of the report and the minimum expected file size. I might expand this later to include a maximum number of accounts that can be deleted in one go........

##Set paths and preferences
$LogfilePath = ".\Logs" ##Path where log files are created.
$DatafilePath = ".\Data" ##Path where data file is created.
$SIMSStudentReport = "\\SIMSServer\ReportShare$\StudentUserinfo.csv" ##Report containing list of current students
$ReportMinSize = "50KB" ##Minimum expected size for successfully exported SIMS report
$ShortAD = "MyDomain" ##Short AD name
$SearchOU = "OU=Students,OU=Users,DC=MyDomain,DC=sch,DC=uk" #OU Containing existing Students
$HomePath = "\\studentfs\Home$" ##Root home path containing yeargroup folders.
$HomeDrive = "H:" ##Drive letter to map to HomePath
$ProfilePath = "\\studentfs\Profile$" ##Root profile path containing yeargroup folders.
$StudMail = "@Studentmaildomain.com" ##Mail domain for student accounts
$StudentGroup = "Students" ##Group to add all new students to.
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.mydomain.com" ##SMTP server for emailing log files.
$SMTPTo = "Alerts " ##Recipient address for log files.
$SMTPFrom = "Someone " ##Sender address for log files.
$DeleteUsers = "Yes" ##If set to "Yes", old user accounts will be deleted.  If set to something else, old user accounts will be disabled.
##/Set paths and preferences

## Check that source data exists - Running without source data would be bad!
if ((Test-Path -Path $SIMSStudentReport) -And (Get-Item $SIMSStudentReport).Length -gt $ReportMinSize) {

##Set the log file
if (Test-Path -Path $LogfilePath) {
   #"Log folder exists"
} else {
   New-Item $LogfilePath -type directory #Creates log folder.
}
$LogfileName = Get-Date -UFormat "%Y%m%d"
$Logfile = $LogfilePath + '\' + $LogfileName + ".csv"

##Import and modify SIMS Report
if (Test-Path -Path $DatafilePath) {
   #"Data folder exists"
} else {
   New-Item $DatafilePath -type directory #Creates log folder.
}
$Names = import-csv $SIMSStudentReport
$Names | foreach-object {
   $_.LegalSurname = $_.LegalSurname.replace(" ","") #Removes spaces from surname
   $_.LegalSurname = $_.LegalSurname.replace("-","") #Removes dashes from surname
   $_.LegalForename = $_.LegalForename.replace(" ","") #Removes spaces from forename
   $_.LegalForename = $_.LegalForename.replace("-","") #Removes dashes from forename
   $_.Year = $_.Year.replace("  "," ") #Removes double spaces from Year
   }
$Names | export-csv "$DatafilePath\StudentUserinfo.csv" -notype #Writes modified local copy of csv for further processing

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

$Students = import-csv "$DatafilePath\StudentUserinfo.csv" #Imports modified local copy of csv
       foreach ($Student in $Students){
           $LegalSN = $Student.LegalSurname
           $LegalFN = $Student.LegalForename
           $SN = $Student.Surname
           $FN = $Student.Forename
           $DisplayName = $Student.Name
           $SIMSUPN = $Student.UPN
           $Year = $Student.Year
           $AccountExists = "No" #Sets initial value.
           $StudOU = ""
           if ($Year -eq "Year 7") {
               $StudOU = "2017"
           }
           if ($Year -eq "Year 8") {
               $StudOU = "2016"
           }
           if ($Year -eq "Year 9") {
               $StudOU = "2015"
           }
           if ($Year -eq "Year 10") {
               $StudOU = "2014"
           }
           if ($Year -eq "Year 11") {
               $StudOU = "2013"
           }
           if ($Year -eq "Year 12") {
               $StudOU = "2012"
           }
           if ($Year -eq "Year 13") {
               $StudOU = "2011"
           }

           ##Check if student already exists
           $UserList = Get-ADUser -SearchBase $SearchOU -Filter {$UPNVariable -eq $SIMSUPN}
           foreach ($User in $UserList){
               $ADUserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               #$FullName = $UserInfo.displayname
               $ADSAM = $ADUserInfo.samAccountName
               $ADUPN = $ADUserInfo.$UPNVariable
               
               if ($ADUPN -eq $SIMSUPN) {
                   #"$DisplayName's UPN already assigned to $ADSAM"
                   $AccountExists = "Yes"
               } else {
                   #"$DisplayName's UPN not assigned to any existing account."
               }
           }
           if ($AccountExists -eq "No" -And $StudOU -ne "" -And $SIMSUPN -ne "") {
               #"$DisplayName's UPN not assigned to any existing account."
               $NewSAM = $LegalFN.substring(0,2) + $LegalSN.substring(0,4) + $StudOU.substring(2,2)
               "Creating account $NewSAM in $StudOU"
               $Email = $NewSAM + $StudMail
               $UPN = $NewSAM + "$dnsroot"
               $Date = Get-Date -format dd/MM/yyyy
               $Desc = "Created on " + $Date
               $OU="OU=$StudOU,$SearchOU" ## Important change to the ou where you need to create users exapmle $OU = "cn=thisOu,dc=domain, dc=com"

            ##Create User##
               if (dsquery user -samid $NewSAM){
                   "User $NewSAM Already Exists"
                   ##Write to log file##
                   $Loginfo = 'Error - Already Exists,' + $NewSAM + ',,'
                   "$Loginfo" | Out-File $Logfile -append
               } else {
                New-ADUser -Name "$DisplayName" -SamAccountName $NewSAM -UserPrincipalName $UPN -DisplayName "$DisplayName" -GivenName $FN -Surname $SN -AccountPassword (ConvertTo-SecureString “password” -AsPlainText -force) -Enabled $true -Path "$OU"
                Add-ADGroupMember -Identity $StudOU -Members $NewSAM
                   Add-ADGroupMember -Identity $StudentGroup -Members $NewSAM
                   Set-ADuser $NewSAM -HomeDrive $HomeDrive -HomeDirectory "$HomePath\$StudOU\$NewSAM" -ProfilePath "$ProfilePath\$StudOU\$NewSAM"
                   Set-ADUser -Identity $NewSAM -EmailAddress $Email
                   Set-ADUser -Identity $NewSAM -replace @{gmail=$Email} ##Sets our custom gmail attribute for use with Google Password Sync.
                   Set-ADUser -Identity $NewSAM -Description $Desc
                   Get-ADUser $NewSAM -Properties $UPNVariable
                   $Command = "Set-ADUser $NewSAM -$UPNVariable $SIMSUPN"
                   Invoke-Expression $Command
                   Set-ADUser -Identity $NewSAM -ChangePasswordAtLogon $true

                   ##Create Home Directory##
                   New-Item $HomePath\$StudOU\$NewSAM -type directory
                   $acl = Get-Acl $HomePath\$StudOU\$NewSAM
                   $permission = "$ShortAD\$NewSAM","Modify", "ContainerInherit, ObjectInherit", "None", "Allow"
                   $accessRule = new-object System.Security.AccessControl.FileSystemAccessRule $permission
                   $acl.SetAccessRule($accessRule)
                   $acl | Set-Acl $HomePath\$StudOU\$NewSAM
                   "User $NewSAM had no Home folder, this as been created"

                   ##Write to log file##
                   $Loginfo = 'Created,' + $NewSAM + ',' + $StudOU + ',' + $Email + ',' + $SIMSUPN
                   "$Loginfo" | Out-File $Logfile -append
               }
               ;
           }
       }

##Check for removed users
$UserList = Get-ADUser -SearchBase $SearchOU -Filter {Enabled -eq $true} | Where-Object {$_.$UPNVariable -ne ""}
foreach ($User in $UserList){
   $UserInfo = Get-ADUser -Identity $User -Properties *
   $SAM = $UserInfo.samAccountName
   $ADUPN = $UserInfo.$UPNVariable
   $Email = $UserInfo.EmailAddress
   $CanonicalName = $UserInfo.CanonicalName
   $StudOU = $CanonicalName.substring(35,4)
   $SIMSUserList = Import-CSV -Path "$DatafilePath\StudentUserinfo.csv"
   If ($SIMSUserList.UPN -match $ADUPN) {
       #"User $SAM in $StudOU found in SIMS Report"
   } else {
       if ($DeleteUsers -eq "Yes") {
           #"User $SAM in $StudOU not found in SIMS Report and will be deleted"
           Remove-ADUser -Identity $SAM -Confirm:$false # Remove Account
           ##Write to log file##
           $Loginfo = 'Deleted,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       } else {
           #"User $SAM in $StudOU not found in SIMS Report and will be disabled"
           $Date = Get-Date -format dd/MM/yyyy
           $Desc = "Automatically disabled on " + $Date
           Disable-ADAccount -Identity $SAM # Disable User account
           Set-ADUser -Identity $SAM -Description $Desc # Set description to date account was disabled
           Set-ADUser -Identity $SAM -EmailAddress $null # Remove Email address  so no longer synced with
           ##Write to log file##
           $Loginfo = 'Disabled,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       }
   }
}

##Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "No changes today."
   }
   Send-MailMessage @emailOptions
}

} else {
   "$SIMSStudentReport does not exist or is smaller than $ReportMinSize"
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "$SIMSStudentReport does not exist or is smaller than $ReportMinSize"
   }
   Send-MailMessage @emailOptions
}

AliG · September 5, 2018

Hi,

Any updated versions?

How would I change the username format? I need YearOfEntry+First3LettersOfSurname+First3LettersOfFirstName e.g. Year7 Fred Bloggs would be 18BloFre

How can I run in test mode so I dont screw things up?

Thanks,

Shaun_Dark_Lord · September 6, 2018

Hi,

Any updated versions?

How would I change the username format? I need YearOfEntry+First3LettersOfSurname+First3LettersOfFirstName e.g. Year7 Fred Bloggs would be 18BloFre

How can I run in test mode so I dont screw things up?

Thanks,

Latest version;

##Set paths and preferences
$LogfilePath = ".\Logs" ##Path where log files are created.
$DatafilePath = ".\Data" ##Path where data file is created.
$SIMSStudentReport = "\\SIMSServer\ReportShare$\StudentUserinfo.csv" ##Report containing list of current students
$ReportMinSize = "50KB" ##Minimum expected size for successfully exported SIMS report
$ShortAD = "CCW" ##Short AD name
$SearchOU = "OU=Students,OU=Users,DC=myschool,DC=sch,DC=uk" #OU Containing existing Students
$HomePath = "\\student-fs\Home$" ##Root home path containing yeargroup folders.
$HomeDrive = "N:" ##Drive letter to map to HomePath
$ProfilePath = "\\student-fs\Profile$" ##Root profile path containing yeargroup folders.
$StudMail = "@myschool.net" ##Mail domain for student accounts
$StudentGroup = "Students" ##Group to add all new students to.
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.myschool.net" ##SMTP server for emailing log files.
$SMTPTo = "Alerts " ##Recipient address for log files.
$SMTPFrom = "Support " ##Sender address for log files.
$DeleteUsers = "Yes" ##If set to "Yes", old user accounts will be deleted.  If set to something else, old user accounts will be disabled.
##/Set paths and preferences

## Check that source data exists - Running without source data would be bad!
if ((Test-Path -Path $SIMSStudentReport) -And (Get-Item $SIMSStudentReport).Length -gt $ReportMinSize) {

##Set the log file
if (Test-Path -Path $LogfilePath) {
   #"Log folder exists"
} else {
   New-Item $LogfilePath -type directory #Creates log folder.
}
$LogfileName = Get-Date -UFormat "%Y%m%d"
$Logfile = $LogfilePath + '\' + $LogfileName + ".csv"

##Import and modify SIMS Report
if (Test-Path -Path $DatafilePath) {
   #"Data folder exists"
} else {
   New-Item $DatafilePath -type directory #Creates log folder.
}
$Names = import-csv $SIMSStudentReport
$Names | foreach-object {
   $_.LegalSurname = $_.LegalSurname.replace(" ","") #Removes spaces from surname
   $_.LegalSurname = $_.LegalSurname.replace("-","") #Removes dashes from surname
   $_.LegalForename = $_.LegalForename.replace(" ","") #Removes spaces from forename
   $_.LegalForename = $_.LegalForename.replace("-","") #Removes dashes from forename
   $_.Year = $_.Year.replace("  "," ") #Removes double spaces from Year
   }
$Names | export-csv "$DatafilePath\StudentUserinfo.csv" -notype #Writes modified local copy of csv for further processing

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

$Students = import-csv "$DatafilePath\StudentUserinfo.csv" #Imports modified local copy of csv
       foreach ($Student in $Students){
           $LegalSN = $Student.LegalSurname
           $LegalFN = $Student.LegalForename
           $SN = $Student.Surname
           $FN = $Student.Forename
           $DisplayName = $Student.Name
           $SIMSUPN = $Student.UPN
           $Year = $Student.Year
           $AccountExists = "No" #Sets initial value.
           $StudOU = ""
           if ($Year -eq "Year 7") {
               $StudOU = "2018"
           }
           if ($Year -eq "Year 8") {
               $StudOU = "2017"
           }
           if ($Year -eq "Year 9") {
               $StudOU = "2016"
           }
           if ($Year -eq "Year 10") {
               $StudOU = "2015"
           }
           if ($Year -eq "Year 11") {
               $StudOU = "2014"
           }
           if ($Year -eq "Year 12") {
               $StudOU = "2013"
           }
           if ($Year -eq "Year 13") {
               $StudOU = "2012"
           }
           ##Check for missing Year
           if ($Year -eq "") {
                   $Loginfo = 'Error - Year Missing for ' + $DisplayName
                   "$Loginfo" | Out-File $Logfile -append
           } else {

           ##Check for missing UPN
           if (!$SIMSUPN) {
                   $Loginfo = 'Error - UPN Missing for ' + $DisplayName + ' in ' + $Year
                   "$Loginfo" | Out-File $Logfile -append
           } else {
           ##Check if student already exists
           $UserList = Get-ADUser -SearchBase $SearchOU -Filter {$UPNVariable -eq $SIMSUPN}
           foreach ($User in $UserList){
               $ADUserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               #$FullName = $UserInfo.displayname
               $ADSAM = $ADUserInfo.samAccountName
               $ADUPN = $ADUserInfo.$UPNVariable
               
               if ($ADUPN -eq $SIMSUPN) {
                   #"$DisplayName's UPN already assigned to $ADSAM"
                   $AccountExists = "Yes"
               } else {
                   #"$DisplayName's UPN not assigned to any existing account."
               }
           }
           if ($AccountExists -eq "No" -And $StudOU -ne "" -And $SIMSUPN -ne "") {
               #"$DisplayName's UPN not assigned to any existing account."
               $NewSAM = $LegalFN.substring(0,2) + $LegalSN.substring(0, [Math]::Min(($LegalSN.Length), 4)) + $StudOU.substring(2,2)
               "Creating account $NewSAM in $StudOU"
               $Email = $NewSAM + $StudMail
               $UPN = $NewSAM + "$dnsroot"
               $Date = Get-Date -format dd/MM/yyyy
               $Desc = "Created on " + $Date
               $OU="OU=$StudOU,$SearchOU" ## Important change to the ou where you need to create users exapmle $OU = "cn=thisOu,dc=domain, dc=com"

            ##Create User##
               if (dsquery user -samid $NewSAM){
                   "User $NewSAM Already Exists"
                   ##Write to log file##
                   $Loginfo = 'Error - Already Exists,' + $NewSAM + ',,'
                   "$Loginfo" | Out-File $Logfile -append
               } else {
                New-ADUser -Name "$DisplayName" -SamAccountName $NewSAM -UserPrincipalName $UPN -DisplayName "$DisplayName" -GivenName $FN -Surname $SN -AccountPassword (ConvertTo-SecureString “Passw0rd666” -AsPlainText -force) -Enabled $true -Path "$OU"
                Add-ADGroupMember -Identity $StudOU -Members $NewSAM
                   Add-ADGroupMember -Identity $StudentGroup -Members $NewSAM
                   Set-ADuser $NewSAM -HomeDrive $HomeDrive -HomeDirectory "$HomePath\$StudOU\$NewSAM" -ProfilePath "$ProfilePath\$StudOU\$NewSAM"
                   Set-ADUser -Identity $NewSAM -EmailAddress $Email
                   Set-ADUser -Identity $NewSAM -replace @{gmail=$Email} ##Sets our custom gmail attribute for use with Google Password Sync.
                   Set-ADUser -Identity $NewSAM -Description $Desc
                   Get-ADUser $NewSAM -Properties $UPNVariable
                   $Command = "Set-ADUser $NewSAM -$UPNVariable $SIMSUPN"
                   Invoke-Expression $Command
                   Set-ADUser -Identity $NewSAM -ChangePasswordAtLogon $true

                   ##Create Home Directory##
                   New-Item $HomePath\$StudOU\$NewSAM -type directory
                   $acl = Get-Acl $HomePath\$StudOU\$NewSAM
                   $permission = "$ShortAD\$NewSAM","Modify", "ContainerInherit, ObjectInherit", "None", "Allow"
                   $accessRule = new-object System.Security.AccessControl.FileSystemAccessRule $permission
                   $acl.SetAccessRule($accessRule)
                   $acl | Set-Acl $HomePath\$StudOU\$NewSAM
                   "User $NewSAM had no Home folder, this as been created"

                   ##Write to log file##
                   $Loginfo = 'Created,' + $NewSAM + ',' + $StudOU + ',' + $Email + ',' + $SIMSUPN
                   "$Loginfo" | Out-File $Logfile -append
               }
               ;
           }
           }
           }
       }

##Check for removed users
$UserList = Get-ADUser -SearchBase $SearchOU -Filter {Enabled -eq $true} | Where-Object {$_.$UPNVariable -ne ""}
foreach ($User in $UserList){
   $UserInfo = Get-ADUser -Identity $User -Properties *
   $SAM = $UserInfo.samAccountName
   $ADUPN = $UserInfo.$UPNVariable
   $Email = $UserInfo.EmailAddress
   $CanonicalName = $UserInfo.CanonicalName
   $StudOU = $CanonicalName.substring(35,4)
   $SIMSUserList = Import-CSV -Path "$DatafilePath\StudentUserinfo.csv"
   If ($SIMSUserList.UPN -match $ADUPN) {
       #"User $SAM in $StudOU found in SIMS Report"
   } else {
       if ($DeleteUsers -eq "Yes") {
           #"User $SAM in $StudOU not found in SIMS Report and will be deleted"
           Remove-ADUser -Identity $SAM -Confirm:$false # Remove Account
           ##Write to log file##
           $Loginfo = 'Deleted,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       } else {
           #"User $SAM in $StudOU not found in SIMS Report and will be disabled"
           $Date = Get-Date -format dd/MM/yyyy
           $Desc = "Automatically disabled on " + $Date
           Disable-ADAccount -Identity $SAM # Disable User account
           Set-ADUser -Identity $SAM -Description $Desc # Set description to date account was disabled
           Set-ADUser -Identity $SAM -EmailAddress $null # Remove Email address  so no longer synced with Google
           Set-ADUser -Identity $SAM -replace @{gmail=$null} # Remove gmail attribute so no longer synced with Google
           ##Write to log file##
           $Loginfo = 'Disabled,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       }
   }
}

##Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage  @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "No changes today."
   }
   Send-MailMessage  @emailOptions
}

} else {
   "$SIMSStudentReport does not exist or is smaller than $ReportMinSize"
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "$SIMSStudentReport does not exist or is smaller than $ReportMinSize"
   }
   Send-MailMessage  @emailOptions
}

For your desired username format, change line 111 to this;

$NewSAM = $StudOU.substring(2,2) + $LegalSN.substring(0, [Math]::Min(($LegalSN.Length), 3)) + $LegalFN.substring(0, [Math]::Min(($LegalSN.Length), 3))

Shaun_Dark_Lord · September 6, 2018

Test Mode - I've just added a "##" to the start of lines that make changes;

##Set paths and preferences
$LogfilePath = ".\Logs" ##Path where log files are created.
$DatafilePath = ".\Data" ##Path where data file is created.
$SIMSStudentReport = "\\SIMSServer\ReportShare$\StudentUserinfo.csv" ##Report containing list of current students
$ReportMinSize = "50KB" ##Minimum expected size for successfully exported SIMS report
$ShortAD = "CCW" ##Short AD name
$SearchOU = "OU=Students,OU=Users,DC=myschool,DC=sch,DC=uk" #OU Containing existing Students
$HomePath = "\\student-fs\Home$" ##Root home path containing yeargroup folders.
$HomeDrive = "N:" ##Drive letter to map to HomePath
$ProfilePath = "\\student-fs\Profile$" ##Root profile path containing yeargroup folders.
$StudMail = "@myschool.net" ##Mail domain for student accounts
$StudentGroup = "Students" ##Group to add all new students to.
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.myschool.net" ##SMTP server for emailing log files.
$SMTPTo = "Alerts " ##Recipient address for log files.
$SMTPFrom = "Support " ##Sender address for log files.
$DeleteUsers = "Yes" ##If set to "Yes", old user accounts will be deleted.  If set to something else, old user accounts will be disabled.
##/Set paths and preferences

## Check that source data exists - Running without source data would be bad!
if ((Test-Path -Path $SIMSStudentReport) -And (Get-Item $SIMSStudentReport).Length -gt $ReportMinSize) {

##Set the log file
if (Test-Path -Path $LogfilePath) {
   #"Log folder exists"
} else {
   New-Item $LogfilePath -type directory #Creates log folder.
}
$LogfileName = Get-Date -UFormat "%Y%m%d"
$Logfile = $LogfilePath + '\' + $LogfileName + ".csv"

##Import and modify SIMS Report
if (Test-Path -Path $DatafilePath) {
   #"Data folder exists"
} else {
   New-Item $DatafilePath -type directory #Creates log folder.
}
$Names = import-csv $SIMSStudentReport
$Names | foreach-object {
   $_.LegalSurname = $_.LegalSurname.replace(" ","") #Removes spaces from surname
   $_.LegalSurname = $_.LegalSurname.replace("-","") #Removes dashes from surname
   $_.LegalForename = $_.LegalForename.replace(" ","") #Removes spaces from forename
   $_.LegalForename = $_.LegalForename.replace("-","") #Removes dashes from forename
   $_.Year = $_.Year.replace("  "," ") #Removes double spaces from Year
   }
$Names | export-csv "$DatafilePath\StudentUserinfo.csv" -notype #Writes modified local copy of csv for further processing

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

$Students = import-csv "$DatafilePath\StudentUserinfo.csv" #Imports modified local copy of csv
       foreach ($Student in $Students){
           $LegalSN = $Student.LegalSurname
           $LegalFN = $Student.LegalForename
           $SN = $Student.Surname
           $FN = $Student.Forename
           $DisplayName = $Student.Name
           $SIMSUPN = $Student.UPN
           $Year = $Student.Year
           $AccountExists = "No" #Sets initial value.
           $StudOU = ""
           if ($Year -eq "Year 7") {
               $StudOU = "2018"
           }
           if ($Year -eq "Year 8") {
               $StudOU = "2017"
           }
           if ($Year -eq "Year 9") {
               $StudOU = "2016"
           }
           if ($Year -eq "Year 10") {
               $StudOU = "2015"
           }
           if ($Year -eq "Year 11") {
               $StudOU = "2014"
           }
           if ($Year -eq "Year 12") {
               $StudOU = "2013"
           }
           if ($Year -eq "Year 13") {
               $StudOU = "2012"
           }
           ##Check for missing Year
           if ($Year -eq "") {
                   $Loginfo = 'Error - Year Missing for ' + $DisplayName
                   "$Loginfo" | Out-File $Logfile -append
           } else {

           ##Check for missing UPN
           if (!$SIMSUPN) {
                   $Loginfo = 'Error - UPN Missing for ' + $DisplayName + ' in ' + $Year
                   "$Loginfo" | Out-File $Logfile -append
           } else {
           ##Check if student already exists
           $UserList = Get-ADUser -SearchBase $SearchOU -Filter {$UPNVariable -eq $SIMSUPN}
           foreach ($User in $UserList){
               $ADUserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               #$FullName = $UserInfo.displayname
               $ADSAM = $ADUserInfo.samAccountName
               $ADUPN = $ADUserInfo.$UPNVariable
               
               if ($ADUPN -eq $SIMSUPN) {
                   #"$DisplayName's UPN already assigned to $ADSAM"
                   $AccountExists = "Yes"
               } else {
                   #"$DisplayName's UPN not assigned to any existing account."
               }
           }
           if ($AccountExists -eq "No" -And $StudOU -ne "" -And $SIMSUPN -ne "") {
               #"$DisplayName's UPN not assigned to any existing account."
               $NewSAM = $StudOU.substring(2,2) + $LegalSN.substring(0, [Math]::Min(($LegalSN.Length), 3)) + $LegalFN.substring(0, [Math]::Min(($LegalSN.Length), 3))
               "Creating account $NewSAM in $StudOU"
               $Email = $NewSAM + $StudMail
               $UPN = $NewSAM + "$dnsroot"
               $Date = Get-Date -format dd/MM/yyyy
               $Desc = "Created on " + $Date
               $OU="OU=$StudOU,$SearchOU" ## Important change to the ou where you need to create users exapmle $OU = "cn=thisOu,dc=domain, dc=com"

            ##Create User##
               if (dsquery user -samid $NewSAM){
                   "User $NewSAM Already Exists"
                   ##Write to log file##
                   $Loginfo = 'Error - Already Exists,' + $NewSAM + ',,'
                   "$Loginfo" | Out-File $Logfile -append
               } else {
##	                New-ADUser -Name "$DisplayName" -SamAccountName $NewSAM -UserPrincipalName $UPN -DisplayName "$DisplayName" -GivenName $FN -Surname $SN -AccountPassword (ConvertTo-SecureString “Passw0rd666” -AsPlainText -force) -Enabled $true -Path "$OU"
##	                Add-ADGroupMember -Identity $StudOU -Members $NewSAM
##                    Add-ADGroupMember -Identity $StudentGroup -Members $NewSAM
##                    Set-ADuser $NewSAM -HomeDrive $HomeDrive -HomeDirectory "$HomePath\$StudOU\$NewSAM" -ProfilePath "$ProfilePath\$StudOU\$NewSAM"
##                    Set-ADUser -Identity $NewSAM -EmailAddress $Email
##                    Set-ADUser -Identity $NewSAM -replace @{gmail=$Email} ##Sets our custom gmail attribute for use with Google Password Sync.
##                    Set-ADUser -Identity $NewSAM -Description $Desc
##                    Get-ADUser $NewSAM -Properties $UPNVariable
##                    $Command = "Set-ADUser $NewSAM -$UPNVariable $SIMSUPN"
##                    Invoke-Expression $Command
##                    Set-ADUser -Identity $NewSAM -ChangePasswordAtLogon $true

                   ##Create Home Directory##
##                    New-Item $HomePath\$StudOU\$NewSAM -type directory
##                    $acl = Get-Acl $HomePath\$StudOU\$NewSAM
##                    $permission = "$ShortAD\$NewSAM","Modify", "ContainerInherit, ObjectInherit", "None", "Allow"
##                    $accessRule = new-object System.Security.AccessControl.FileSystemAccessRule $permission
##                    $acl.SetAccessRule($accessRule)
##                    $acl | Set-Acl $HomePath\$StudOU\$NewSAM
                   "User $NewSAM had no Home folder, this as been created"

                   ##Write to log file##
                   $Loginfo = 'Created,' + $NewSAM + ',' + $StudOU + ',' + $Email + ',' + $SIMSUPN
                   "$Loginfo" | Out-File $Logfile -append
               }
               ;
           }
           }
           }
       }

##Check for removed users
$UserList = Get-ADUser -SearchBase $SearchOU -Filter {Enabled -eq $true} | Where-Object {$_.$UPNVariable -ne ""}
foreach ($User in $UserList){
   $UserInfo = Get-ADUser -Identity $User -Properties *
   $SAM = $UserInfo.samAccountName
   $ADUPN = $UserInfo.$UPNVariable
   $Email = $UserInfo.EmailAddress
   $CanonicalName = $UserInfo.CanonicalName
   $StudOU = $CanonicalName.substring(35,4)
   $SIMSUserList = Import-CSV -Path "$DatafilePath\StudentUserinfo.csv"
   If ($SIMSUserList.UPN -match $ADUPN) {
       #"User $SAM in $StudOU found in SIMS Report"
   } else {
       if ($DeleteUsers -eq "Yes") {
           #"User $SAM in $StudOU not found in SIMS Report and will be deleted"
##            Remove-ADUser -Identity $SAM -Confirm:$false # Remove Account
           ##Write to log file##
           $Loginfo = 'Deleted,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       } else {
           #"User $SAM in $StudOU not found in SIMS Report and will be disabled"
           $Date = Get-Date -format dd/MM/yyyy
           $Desc = "Automatically disabled on " + $Date
##            Disable-ADAccount -Identity $SAM # Disable User account
##            Set-ADUser -Identity $SAM -Description $Desc # Set description to date account was disabled
##            Set-ADUser -Identity $SAM -EmailAddress $null # Remove Email address  so no longer synced with Google
##            Set-ADUser -Identity $SAM -replace @{gmail=$null} # Remove gmail attribute so no longer synced with Google
           ##Write to log file##
           $Loginfo = 'Disabled,' + $SAM + ',' + $StudOU + ',' + $Email + ',' + $ADUPN
           "$Loginfo" | Out-File $Logfile -append
       }
   }
}

##Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage  @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "No changes today."
   }
   Send-MailMessage  @emailOptions
}

} else {
   "$SIMSStudentReport does not exist or is smaller than $ReportMinSize"
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Student Creation Script"
       'Body' = "$SIMSStudentReport does not exist or is smaller than $ReportMinSize"
   }
   Send-MailMessage  @emailOptions
}

Edited September 6, 2018 by Shaun_Dark_Lord
error on code line 111

AliG · September 6, 2018

If I use $DeleteUsers = "No" will that be in effect safe mode? As in I wont screw up my existing accounts if I get any code wrong.

AliG · September 6, 2018

Sorry just seen your test mode comment

Shaun_Dark_Lord · September 6, 2018

If I use $DeleteUsers = "No" will that be in effect safe mode? As in I wont screw up my existing accounts if I get any code wrong.

Please don't take this the wrong way, but before you run any script against your AD, YOU MUST KNOW EXACTLY WHAT IT DOES!

I'm not responsible for your School's IT - you are. That means that you can't trust me or my code just because I say it works.

It's entirely your choice. I put these scripts up here because I spent a lot of time on them, am happy with how they're working in my School, and wanted to save others some time. I'd strongly advise against running any script you find on the internet without understanding what it does and how it does it.

AliG · September 6, 2018

I take your point so I'm going to run it up on a test network first with our DC restored to it.

It does need tweaking for our exact setup.

Thanks

SIMS report -> New student AD accounts

Recommended Posts

Shaun_Dark_Lord

jthompson

Shaun_Dark_Lord

Shaun_Dark_Lord

derf

Shaun_Dark_Lord

AliG

Shaun_Dark_Lord

Shaun_Dark_Lord

AliG

AliG

Shaun_Dark_Lord

AliG

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Sage 200 budget holders - test something for me?

Bromcom Support

My Fluke LinkRunner has a fault...

Home

Forums

What's New