Shaun_Dark_Lord

Write-Host "Creating the classes!" -ForegroundColor Green $ClassList = Import-Csv -Path $FinalSIMSTeachList $FilteredCL = ($ClassList | Where {$_.Work_Email -ne "" }) $SIMSClassCount = @($FilteredCL).count # Count Number of SIMS Classes. Write-Host "$SIMSClassCount classes found in SIMS." -ForegroundColor Green foreach ($Item in $FilteredCL){ $Class = $Item.Class $ClassAlias = $SchoolPrefix + $AcaYear + "_" + $Class $TeachEmail = $Item.Work_Email $CourseCSV = $Classfilepath + $ClassAlias + '.csv' $ClassPrefix = $SchoolPrefix + $AcaYear + "_*" $ExistingCourseList = Import-Csv -Path $GAMCurrent | Where {$_.Aliases -like $ClassPrefix} $StartCourses = $ExistingCourseList.count # Count Number of Classes before we do anything. if (Test-Path -Path $CourseCSV) { #Write-Host "Student list exists for $ClassAlias" -ForegroundColor Blue $FilteredExistingCourseList = ($ExistingCourseList | Where {$_.Aliases -eq $ClassAlias }) if ($FilteredExistingCourseList) { #$CurrentOwner = $_.ownerEmail foreach ($Item in $FilteredExistingCourseList){ $CurrentOwner = $Item.ownerEmail #$CourseName = $Item.Aliases } if (($CurrentOwner -eq $TeachEmail)) { #Write-Host "Owner for class $ClassAlias already set" -ForegroundColor Blue } else { #Write-Host "Google Classroom $ClassAlias already exists - changing owner to $TeachEmail and reset course to provisioned." -ForegroundColor Green $Loginfo = 'Google Classroom ' + $ClassAlias + ' already exists - changing owner to ' + $TeachEmail + ' and reset course to provisioned.' "$Loginfo" | Out-File $Logfile -append $ClassModified = $ClassModified + 1 & $GAMPath update course $ClassAlias status PROVISIONED 2> $null & $GAMPath course $ClassAlias add teacher $TeachEmail 2> $null & $GAMPath update course $ClassAlias owner $TeachEmail 2> $null } } else { #Write-Host "Creating Google Classroom $ClassAlias with teacher $TeachEmail" -ForegroundColor Green ##Write to log file## $Loginfo = 'Created Google Classroom ' + $ClassAlias + ' with ' + $TeachEmail "$Loginfo" | Out-File $Logfile -append $ClassCreated = $ClassCreated + 1 & $GAMPath create course alias $ClassAlias name $Class teacher $TeachEmail 2> $null & $GAMPath course $ClassAlias add teacher $TeachEmail 2> $null } ##Sync class students with Class CSV #Write-Host "Syncing Google Classroom $ClassAlias students with file $CourseCSV" -ForegroundColor Green $RawCourseCSV = $Classfilepath + "Raw_" + $ClassAlias + ".csv" Get-Content $CourseCSV -Raw | Set-Content $RawCourseCSV #Reencode course CSV to RAw so that it can be read by GAM. $CSVFile = $RawCourseCSV + ":Email" #Set the source CSV file and data field for the sync. & $GAMPath course "$ClassAlias" sync students csvfile $CSVFile 2> $null } else { Write-Host "Class $ClassAlias does not contain any students." -ForegroundColor Red $FilteredExistingCourseList = ($ExistingCourseList | Where {$_.Aliases -eq $ClassAlias }) if ($FilteredExistingCourseList) { & $GAMPath update course $ClassAlias status ARCHIVED 2> $null $Loginfo = 'Class ' + $ClassAlias + ' - No students found. Class Archived.' "$Loginfo" | Out-File $Logfile -append } else { $Loginfo = 'Error creating class ' + $ClassAlias + ' - No students found.' "$Loginfo" | Out-File $Logfile -append } $InvalidClass = $InvalidClass + 1 } } $ScriptChanges = [Math]::Abs($ClassCreated - $ClassRemoved) #Redundant, as we no longer remove empty classes mid-year. Left in case I ever figure out how to do this efficiently. Write-Host "Grabbing Updated list of Google Classrooms....." -ForegroundColor Green $GAMFinal = $GAMData + 'GAMFinal.csv' & $GAMPath print courses state provisioned state active state declined aliases fields id fields name fields courseState owneremail > $GAMFinal 2> $null $FinalList = Import-Csv -Path $GAMFinal | Where {$_.Aliases -like $ClassPrefix} $EndCourses = $FinalList.count # Count Number of Classes after changes have been made. $GAMChanges = [Math]::Abs($EndCourses - $StartCourses) $Errors = [Math]::Abs($ScriptChanges - $GAMChanges) ##Determine End Time $TotalTime = $("{0:hh\:mm\:ss}" -f (New-TimeSpan -Start $StartTime -End $(Get-Date))) #Write-Host $("{0:hh\:mm\:ss}" -f (New-TimeSpan -Start $StartTime -End $(Get-Date))) -ForegroundColor Green Write-Host "Script completed in $TotalTime" -ForegroundColor Green Write-Host "Expected changes: $ScriptChanges" -ForegroundColor Green ##Script completes successfully - Email log file## if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged if ($ScriptChanges -eq $GAMChanges) { $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "Script completed in $TotalTime Classes Created: $ClassCreated Classes Modified: $ClassModified Classes Removed: $ClassRemoved Invalid Classes: $InvalidClass" 'Attachments' = $Logfile } Send-MailMessage @emailOptions Write-Host "Actual changes: $GAMChanges" -ForegroundColor Green } else { $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "Script completed with errors in $TotalTime Classes Created: $ClassCreated Classes Modified: $ClassModified Classes Removed: $ClassRemoved Invalid Classes: $InvalidClass Errors: $Errors" 'Attachments' = $Logfile } Send-MailMessage @emailOptions Write-Host "Actual changes: $GAMChanges" -ForegroundColor Red } } else { $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "No changes today. Script completed in $TotalTime minutes." } Send-MailMessage @emailOptions } ##If SIMS data does not exist; } else { Write-Host "$SIMSStudList does not exist or is smaller than $ReportMinSize" -ForegroundColor Red $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "$SIMSStudList does not exist or is smaller than $ReportMinSize" } Send-MailMessage @emailOptions } ##If GAM data does not exist; } else { Write-Host "$GAMCurrent does not exist or is smaller than $GAMDataSize" -ForegroundColor Red $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "$GAMCurrent does not exist or is smaller than $GAMDataSize" } Send-MailMessage @emailOptions }

The Powershell Script; ##Set preferences $StudMailSource = "AD" ##Valid options are "AD" or "SIMS". If AD, then $MailVariable and $AdNoVariable are mandatory. If SIMS, then $SIMSClassCSVMail is mandatory. #AD Settings (Used if you're using AD to store/verify your Staff/Student addresses) $MailVariable = "gmail" ##AD option used to store email address. $AdNoVariable = "AdNo" ##AD option used to store SIMS Admission Number. $StudOU = "OU=Students,OU=Users,DC=myschool,DC=kent,DC=sch,DC=uk" #OU Containing existing Students $GroupCreate = "Yes" ##Valid options are "Yes" or "No". If Yes, an AD group will be created in $ClassGroupOU for each class. $ClassGroupOU = "OU=Classes,OU=Groups,DC=myschool,DC=kent,DC=sch,DC=uk" #SIMS Settings $SIMSStudList = "\\simsserver\GoogleClasses$\SIMSStudList.csv" ##Report containing list of current Classes and a corresponding Student identifier (Either AdNo or Email address) $SIMSTeachList = "\\simsserver\GoogleClasses$\SIMSTeachList.csv" ##Report containing a list of current Classes and their teacher's email address (Work Email) $ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report ($SIMSStudList) $SIMSClassCSVMail = "Primary Email" ##SIMS report field containing Student Email addresses #File Paths and Misc Settings $LogfilePath = ".\Logs\" ##Path where log files are created. $Classfilepath = ".\ClassData\" ##Path where data file is created. $GAMPath = "C:\GAM\GAMADV\gam.exe" ##Path where GAMADV is installed. $GAMData = ".\GAMData\" ##Path where GAM data is recorded. $GAMDataSize = "10KB" ##Minimum expected size for successfully exported GAM Data #School and Email Settings $MailDomain = "@mydomain.com" ##Mail domain used for group creation. $SchoolPrefix = "TWS" ##Prefix to add to Class Alias to prevent MAT conflicts. $SMTPServer = "aspmx.l.google.com" ##SMTP server for emailing log files. $SMTPTo = "Alerts " ##Recipient address for log files. $SMTPFrom = "DC001 " ##Sender address for log files. ##/Set preferences if (($StudMailSource -eq "AD")) { Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot } ##Determine Start Time $StartTime = Get-Date ##Setup Counters $ClassCreated = 0 $ClassModified = 0 $ClassRemoved = 0 $InvalidClass = 0 ##Determine Current Academic Year to add to Class Alias prefix $year=(Get-Date).Year $month=(Get-Date).Month if ($month -In 1..8) {$AcaYear = ($year -1)} else {$AcaYear = $year} #"We are in Academic Year $AcaYear" ##Check paths if (Test-Path -Path $LogfilePath) { #"Log folder exists" } else { New-Item $LogfilePath -type directory #Creates log folder. } if (Test-Path -Path $Classfilepath) { #"Class Data folder exists" } else { New-Item $Classfilepath -type directory #Creates log folder. } if (Test-Path -Path $GAMData) { #"GAM Data folder exists" } else { New-Item $GAMData -type directory #Creates log folder. } ##Set Logfile Name $Logfilename = Get-Date -UFormat "%Y%m%d" $Logfile = $Logfilepath + $Logfilename + ".txt" ##Clear old GAM data Write-Host "Clearing old GAM data files...." -ForegroundColor Green Get-ChildItem -Path $GAMData -Recurse -Force | Remove-Item -Force ##Export Current Google Classes Write-Host "Grabbing list of existing Google Classrooms....." -ForegroundColor Green $GAMCurrent = $GAMData + 'GAMCurrent.csv' & $GAMPath print courses state provisioned state active state declined alias fields id fields name fields courseState owneremail > $GAMCurrent 2> $null ## Check that GAM data exists if ((Test-Path -Path $GAMCurrent) -And (Get-Item $GAMCurrent).Length -gt $GAMDataSize) { ##End of Year if ($month -eq 8) { "Doing the End of Year Process!" $ClassPrefix = $SchoolPrefix + $AcaYear + "_*" $ClassList = Import-Csv -Path $GAMCurrent | Where {$_.Aliases -like $ClassPrefix} $ClassRemoved = @($ClassList).count Write-Host "$ClassCount classes found in $AcaYear to be deleted." -ForegroundColor Green foreach ($Class in $ClassList){ $Alias = $Class.Aliases $Loginfo = 'Removed class ' + $Alias "$Loginfo" | Out-File $Logfile -append & $GAMPath update course $Alias status ARCHIVED 2> $null if(($GroupCreate -eq "Yes")) { $GroupCN = "CN=" + "$Alias" + "," + $ClassGroupOU Write-Host "Removing Old Group $Alias" -ForegroundColor DarkGreen Remove-ADObject -Identity $GroupCN -Confirm:$False } } $TotalTime = $("{0:hh\:mm\:ss}" -f (New-TimeSpan -Start $StartTime -End $(Get-Date))) ##Script completes successfully - 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 Google Classes Script End of Year Process" 'Body' = "Script completed in $TotalTime Classes Removed: $ClassRemoved" 'Attachments' = $Logfile } Send-MailMessage @emailOptions } else { Write-Host "End of Year process made no changes." -ForegroundColor Green } break #Terminate the script } else { "Running normally, as it's not August." } ## Check that source data exists - Running without source data would be bad! if ((Test-Path -Path $SIMSStudList) -And (Get-Item $SIMSStudList).Length -gt $ReportMinSize) { ##Clear old class data Write-Host "Clearing old SIMS data files...." -ForegroundColor Green Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force ##Reformat class/teacher/email list $FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv" Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList ##Reformat class/student/AdNo list Write-Host "Importing SIMS data from $SIMSStudList" -ForegroundColor Blue $NewSIMSStudList = $Classfilepath + "StudClasses.csv" ##This gives me new lines after each $ Write-Host "Modifying SIMS data." -ForegroundColor Blue Get-Content $SIMSStudList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSStudList if(($StudMailSource -eq "AD")) { Write-Host "AD set as authoritative source for student email addresses." -ForegroundColor Green $Loginfo = 'AD set as authoritative source for student email addresses.' "$Loginfo" | Out-File $Logfile -append Write-Host "Grabbing students from $StudOU" -ForegroundColor Green $ADStudList = Get-ADUser -SearchBase $StudOU -Filter * -Properties * | where {($_.$MailVariable -ne $null) -and ($_.$AdNoVariable -ne $null)} foreach ($User in $ADStudList){ $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties $FullName = $UserInfo.displayname $SAM = $UserInfo.samAccountName $AdNo = $UserInfo.$AdNoVariable $StudEmail = $UserInfo.$MailVariable #"Looking for $AdNo" $ClassList = Import-Csv -Path $NewSIMSStudList $FilteredCL = ($ClassList | Where {$_.Adno -eq $UserInfo.$AdNoVariable }) foreach ($Item in $FilteredCL){ $Class = $Item.Class $ClassAlias = $SchoolPrefix + $AcaYear + "_" + $Class if (!$Class) {"Group name blank"} else { $Classfile = $Classfilepath + $ClassAlias + ".csv" if((Test-Path -Path $Classfile )){ #Check to see if file has already been created #"Class file already exists" } else { $Classhead = 'SAM,Email' "$Classhead" | Out-File $Classfile -append #Create Blank class file } $StudData = $SAM + ',' + $StudEmail "$StudData" | Out-File $Classfile -append #Add student's email address to class file. if(($GroupCreate -eq "Yes")) { if (dsquery group -name $ClassAlias) { #Write-Host "Group $Class already exists" -ForegroundColor Blue } else { ##Write to log file## $Loginfo = 'Created group ' + $ClassAlias "$Loginfo" | Out-File $Logfile -append $GroupEmail = $ClassAlias + $MailDomain Write-Host "Creating Group $ClassAlias with address $GroupEmail in path $ClassGroupOU" -ForegroundColor Green New-ADGroup -Name $ClassAlias -GroupCategory Distribution -GroupScope Global -DisplayName $ClassAlias -OtherAttributes @{'mail'=$GroupEmail} -Path $ClassGroupOU } ##Check if user is already group member $Members = Get-ADGroupMember -Identity $ClassAlias -Recursive | Select -ExpandProperty samAccountName If ($Members -contains $SAM) { #"User $SAM already member of $Class" } else { Write-Host "Adding $SAM to group $ClassAlias" -ForegroundColor Green $Loginfo = 'Added student ' + $SAM + " to " + $ClassAlias "$Loginfo" | Out-File $Logfile -append Add-ADGroupMember -Identity $ClassAlias -Members $SAM } } } } } ##Cleanup Groups if(($GroupCreate -eq "Yes")) { Write-Host "Removing old students from groups." -ForegroundColor Green $GroupList = Get-ADGroup -filter * -searchbase $ClassGroupOU foreach ($Group in $GroupList){ $GN = $Group.Name #$DN = $EmptyGroup.DistinguishedName $Classfile = $Classfilepath + $GN + ".csv" if((Test-Path -Path $Classfile)){ #"Group file for $GN exists" $GroupMembers = Get-ADGroupMember $GN foreach ($GroupMember in $GroupMembers) { $UserInfo = Get-ADUser -Identity $GroupMember -Properties * $SAM = $UserInfo.samAccountName #$StudEmail = $UserInfo.EmailAddress $ClassList = Import-CSV -Path $Classfile if ($ClassList.SAM -match $SAM ) { #"User $SAM found in class list for $GN" } else { Write-Host "User $SAM no longer member of $GN" -ForegroundColor DarkGreen ##Write to log file## ##Remove User from Class Group Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false } } } else { Write-Host "Group file for $GN does not exist" -ForegroundColor Red } } Write-Host "Looking for empty class groups to remove." -ForegroundColor Green $EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $ClassGroupOU | where {!$_.members} | where {!$_.membersof} foreach ($EmptyGroup in $EmptyGroups){ ##Write to log file## $Loginfo = 'Removed empty group ' + $EmptyGroup "$Loginfo" | Out-File $Logfile -append $DN = $EmptyGroup.DistinguishedName $GroupClassName = $EmptyGroup.Name Write-Host "Removing Empty Group $GroupClassName" -ForegroundColor DarkGreen Remove-ADObject -Identity $DN -Confirm:$False } } } else { Write-Host "SIMS set as authoritative source for student email addresses." -ForegroundColor Green $Loginfo = 'SIMS set as authoritative source for student email addresses.' "$Loginfo" | Out-File $Logfile -append $ClassList = Import-Csv -Path $NewSIMSStudList $SIMSClassCSVMail = $SIMSClassCSVMail -replace "`r`n ,","," -replace "/","-" -replace '"','' -replace ':','' -replace ' ','_' #During the CSV reformat, some characters are replaced. This line ensures that the correct CSV header is being used. $FilteredCL = ($ClassList | Where {($_.Class -ne $null) -and ($_.$SIMSClassCSVMail -ne $null)}) $FilteredCount = @($FilteredCL).count Write-Host "$FilteredCount student/class records found" -ForegroundColor Green foreach ($Item in $FilteredCL){ $Class = $Item.Class $ClassAlias = $SchoolPrefix + $AcaYear + "_" + $Class $StudEmail = $Item.$SIMSClassCSVMail if (!$Class) {"Group name blank"} else { $Classfile = $Classfilepath + $ClassAlias + ".csv" if((Test-Path -Path $Classfile )){ #Check to see if file has already been created #"Class file already exists" } else { $Classhead = 'Email' "$Classhead" | Out-File $Classfile -append #Create Blank class file } "$StudEmail" | Out-File $Classfile -append #Add student's email address to class file. } } } To be continued.....

Requirements; A tested and operational GAMADV (4.83.04+) installation. An agreed class alias prefix for your school. Your teacher’s email addresses to be stored in SIMS and set as their “Work Email”. It is technically possible to use AD for this, but the amount of customisation required versus entering 100 email addresses into SIMS is difficult to justify. Either your student’s email address stored in SIMS and set as their “Primary Email”, or their email address stored in AD, along with their SIMS Admission Number. A clean(ish) Google Classroom environment. The ideal would be to run this at the beginning of a new academic year, after all your old classes have been archived. You could however, write a script to set the Aliases of your existing classes to their expected values and start running this script against already created classes. Getting the data from SIMS; I like to use SIMS built in CommandReporter tool to schedule data exports. Strangely, the CSVs it exports are a lot easier to read than those generated from within SIMS.net. In our environment, we have setup a SIMS user account with the minimum permissions required to run our reports. We then schedule a task to run on the SIMS server itself to execute the following command; Program/Script: “C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe” Arguments: /USER:useraccount /PASSWORD:password /SERVERNAME:simsserver\sqlinstance /DATABASENAME:sims /REPORT:Reportname /OUTPUT:"C:\GoogleClasses\reportname.csv" Two reports are required for this script; The first SIMS report we use is focused on “Class” and pulls SIMS Class names and associated Staff Email addresses that we use to determine the Google classroom names and owners. To remove some invalid entries, the report is then filtered; The second SIMS report we use is focused on “Student” and pulls Admission number, Primary Email and associated Class names. As long as you save the SIMS report with the same name referenced in the scheduled task above, running the task should output a CSV. You can either run a separate task for each report, or add two actions to a single task. As long as you have two csv files ready to go before you run the script, then you’re good to go! What The Script Does; Line 2 determines whether we will be pulling Student email addresses directly from SIMS reports or matching them against AD. Lines 3-8 set our AD variables. If you’re purley running against SIMS data, these do not need to be set. The last two options here determine whether you would like an AD group created for each class. We find this useful for print management and file screens/quotas. AD group creation is only currently possible if AD is your source for Student email addresses. Lines 9-13 specify the location of our two exported SIMS reports, the minimum expected size of a valid student/class report and the report field containing student email addresses (if that’s what you chose on line 2). Lines 14-19 set where we’ll be storing our logs and class data (relative to the powershell script), as well as a minimum expected size for a list of existing Google Classrooms (If you’re testing this script and don’t have any Google Classrooms yet, set this to “0KB”) Lines 20 - 25 set your school prefix (used in the Class Alias) and your email settings for reporting. Lines 28 - 67 determine the script execution time and setup counters for reporting. The current academic year is determined for use in the Class Alias and End of Year process. File paths are checked and created. Lines 69 - 79 clears previously used Google Class data, downloads the current list of Google Classes and checks that the list contains data. Lines 81-116 perform the end of year process. This checks whether it is currently August, and if so, all classes with an Alias matching the current academic year are set to archived. If you chose to enable AD group creation on line 7, these groups are also removed. Lines 121-137 check for the existence of the SIMS student/class report and then import and modify the SIMS reports. Characters in class names that would later cause the script to fail are replaced. Lines 139-237 are run if AD is the source of Student email addresses; Lines 144-150 search the specified AD OU for users with an Admission number and email address. Lines 153-167 imports the SIMS student/class report and matches the data against AD, creating a CSV file for each class containing student email addresses from AD. Lines 169-193 add the matching AD students to an AD group (if set on line 7). Lines 195-237 remove invalid members from the AD groups, and removes any empty groups (if set on line 7). This process was previously used to also determine whether Google Classes could be archived mid-year, but this has not been implemented here. Lines 239-264 are run if SIMS is the source of Student email addresses. The SIMS student/class report is filtered and split, creating a CSV file for each class containing student email addresses. Lines 266-270 imports and filters the Teacher/Class SIMS report to ensure each Class has a corresponding teacher email address (I know the SIMS report is supposed to do this during creation, but it doesn’t always). Lines 271-278 determine the Class Alias for each class in the SIMS report, using the School prefix, academic year and SIMS cass name. The list of existing Google Classes we generated in lines 69-79 is then imported and filtered to only include classes matching our school’s prefix. Line 279 checks that a CSV file exists for each class containing student email addresses. If not, then the class is not created/updated. Lines 281-298 are run if the Class already exists in Google Classroom. The existing owner is checked, and if it does not match, a new Class owner is assigned and the class status is reset to “PROVISIONED”. Lines 300-306 are run if the Class does not already exist. The Class is created with the teacher specified in the SIMS report. Lines 309-314 Sync the student membership of each valid Google Class with the Student Email CSV files we generated in lines 153-167 or 239-264. Lines 316-329 are executed if a class exists in SIMS, has a valid teacher email address, but does not have any matching students. No new classroom is created, and if the Class already existed, it is archived. Lines 331-345 determine the total changes made, and the time taken to execute the script. Lines 347-417 email summaries and log files.

Happens to all of us

Hi I could write the script for you, but a this is writing to your AD, it's probably a better idea for you to do a bit of reading, and figure out the next steps yourself. That way you'll have a good idea what to fix when it goes wrong.

A simple script could be; Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot $OU="OU=Supply Staff,OU=Users,DC=MyDomain,DC=sch,DC=uk" $FirstName = Read-Host "First Name?" $LastName = Read-Host "Last Name?" $FullName = $FirstName + " " + $LastName $SAM = Read-Host "User Name?" $UPN = $SAM + "$dnsroot" Write-Host "Creating user account $SAM for $FullName" New-ADUser -Name "$FullName" -SamAccountName $SAM -UserPrincipalName $UPN -DisplayName "$FullName" -GivenName $FirstName -Surname $LastName -AccountPassword (ConvertTo-SecureString “Password123” -AsPlainText -force) -Enabled $true -Path "$OU"

Yes - You could write a simple script using Read-Host to prompt for each variable. I prefer to dump them in a CSV, even if it's just one user - That way a typo is more likely to be noticed before the account is created and not create more work.

We've moved to individual accounts for supply teachers. I hate having generic accounts, as inevitably, the kids find the login credentials. This doesn't create a huge workload. We have a powershell script setup for cover staff creation, so all we need to do is put their name and username into a csv and press "run".

I'll soon be starting on a new version of this script, as we've joined a MAT's Google domain, and the old script would likely cause havoc with the other school's classes. While I'm working on this, are there any features/changes you would like to see? I'll be using GAMADV with the new script, so there are plenty of new options available.

Thanks for letting me know. That pretty much rules out Smoothwall. Not that the cosmetics really bother me, but the inconsistency at this point is worrying, as well as your lack of clarity on your future plans.......

I appreciate the sentiment, but I was told last week that your devs are working on a reskin. Both statements could only be true if you don't pay your devs.......

Quick update; Spoke to Smoothwall this morning. System looks dated, but the filtering looks solid enough, and the reporting looks great. Saw the Sophos XG demo this afternoon. As expected, it looks like a decent firewall, with filtering bolted on the side. The reporting is as good as our existing solution - Which we're looking to replace because the reporting is ####. On to the next demo.........

I'm sure they'd offer a deal, but the $6.88 per user per year is a bit pricey........

iBoss certainly looks interesting. I am a bit concerned about their BYOD protection; https://www.iboss.com/business/cloud-dns-protection-for-byod-and-guest-networks/ Surely this would only work when paired with some on-site ACL or traffic shaping? Otherwise clients would be free to set any DNS server they like and simply bypass iBoss' filtering.........

https://www.netiq.com/products/securelogin/ Used that for pretty much everything at my last school. edit - Doesn't appear to support Chrome https://www.netiq.com/products/securelogin/technical-information/

Hi We're concentrating on the filtering at the moment. I may be wrong, but from my experience of currently running Sophos UTM at home, Sophos doesn't seem to have the kind of filtering system we're looking for. If I'm mistaken, please reply with some information on how the XG would provide more than just the normal Whitelist/Blacklist/L7/keyword filtering that gets bolted on as an afterthought to most firewalls, and then targeted at schools as a viable complete solution. I'm not having a go. My last school was completely happy with the filtering feature set of the Palo Alto firewall we used, but that was an all-girls school, and did not have the issues we are facing.

Hi We've got a demo booked with Smoothwall next week, but I was hoping to get some recommendations on filtering systems that can scale up to protect a ~20 school MAT. We're looking for something a bit cleverer than the basic whitelist/blacklist system we currently use, and obviously need to focus on safeguarding. We also need to protect student's BYOD in a way that allows monitoring without being so obtrusive that they stick to using 4G. As far as the scale of the system, we'd obviously prefer not to have ~20+ bare metal installations, and would prefer to either centralise the filtering, or go cloudy. Any suggestions?

Still working - Thank you!

Where's the fun in that!

Google Classroom creation using SIMS report data. I'm currently using powershell, as I'm also using AD user and group data. I'm now looking at creating the process in a flow chart and feeding it test scenarios to see if my planned changes will work. If so, then I can hopefully tackle one function at a time, and get it done in a few weeks............

Thanks David - This appears to be quite a deep rabbit-hole!

I need to rewrite a fairly hefty script, as it's gotten so bloated over the past few months that I feel a rewrite would be more efficient than trying to adapt it further (It needs adapting as it couldn't cope with some unexpected user behaviour.....). Am stumped on how to start over. The original script evolved over time, so I thought breaking it down into sections would give me the building blocks I'd need for the new version. But I'm just staring at them, and not seeing the order in which they need to go. How do the professionals do it? Is there a standard method I can look at?

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.

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 }

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))