Shaun_Dark_Lord

Version 4.60 of GAM now allows for exporting of Classroom owner. This now allows the script to check the SIMS data against this info, set the new owner if different, and reset the Class status to "Provisioned" so that classes that are handed over to new teachers are not active until they are ready. ##Set paths and preferences $LogfilePath = ".\Logs\" ##Path where log files are created. $Classfilepath = ".\ClassData\" ##Path where data file is created. $GAMPath = "C:\GAM\gam.exe" ##Path where GAM is installed. $GAMData = ".\GAMData\" ##Path where GAM data is recorded. $GAMDataSize = "10KB" ##Minimum expected size for successfully exported GAM Data $SIMSUPNReport = "\\SIMSServer\ReportShare$\UPNClasses.csv" ##Report containing list of current students $SIMSTeachList = "\\SIMSServer\ReportShare$\ClassTeachers.csv" $ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report $SearchOU = "OU=Students,OU=Users,DC=MySchool,DC=sch,DC=uk" #OU Containing existing Students $GroupOU = "OU=Classes,OU=Groups,DC=ccw,DC=kent,DC=sch,DC=uk" $StudMail = "@myschool.co.uk" ##Mail domain for student accounts $UPNVariable = "POBox" ##AD option used to store SIMS UPN $SMTPServer = "smtp.myschool.co.uk" ##SMTP server for emailing logs. $SMTPTo = "Alerts " ##Recipient address for logs. $SMTPFrom = "DC001 " ##Sender address for logs. ##/Set paths and preferences ##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. } ##Revert Custom Class Names to match SIMS Data $CustomClassList = Import-Csv -Path CourseCorrection.csv $FilteredCustomCL = ($CustomClassList | Where {$_.FriendlyName -ne "" }) foreach ($Item in $FilteredCustomCL){ $CustID = $Item.id $CustSIMSName = $Item.SIMSName $CustFriendlyName = $Item.FriendlyName Write-Host "Renaming $CustFriendlyName to $CustSIMSName" -ForegroundColor Green & $GAMPath update course $CustID name $CustSIMSName } ##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 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) { ##Grab current course participants Write-Host "Grabbing current Google Classroom participants......." -ForegroundColor Green $CourseList = Import-Csv -Path $GAMCurrent foreach ($Item in $CourseList){ $CourseName = $Item.name $CourseName = $CourseName.replace("\","-") $CourseName = $CourseName.replace("/","-") $CourseID = $Item.id $CourseCSV = $GAMData + $CourseName + '.csv' $CourseOwner = $Item.ownerEmail & $GAMPath print course-participants course $CourseID > $CourseCSV 2> $null } ## Check that source data exists - Running without source data would be bad! if ((Test-Path -Path $SIMSUPNReport) -And (Get-Item $SIMSUPNReport).Length -gt $ReportMinSize) { $Logfilename = Get-Date -UFormat "%Y%m%d" $Logfile = $Logfilepath + $Logfilename + ".txt" ##Clear old class data Write-Host "Clearing old SIMS data files...." -ForegroundColor Green Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force ##Import and modify SIMS Report Write-Host "Importing SIMS data from $SIMSUPNReport" -ForegroundColor Blue $NewSIMSUPNReport = $Classfilepath + "UPNClasses.csv" Write-Host "Modifying SIMS data." -ForegroundColor Blue Get-Content $SIMSUPNReport -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSUPNReport Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot Write-Host "Grabbing students from $SearchOU" $FileList = Get-ADUser -SearchBase $SearchOU -Filter * foreach ($User in $FileList){ $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties $FullName = $UserInfo.displayname $SAM = $UserInfo.samAccountName $UPN = $UserInfo.$UPNVariable if (!$UserInfo.$UPNVariable) { "UPN Missing from user $SAM" } ELSE { $ClassList = Import-Csv -Path $NewSIMSUPNReport $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.$UPNVariable }) foreach ($Item in $FilteredCL){ $Class = $Item.Class if (!$Class) {"Group name blank"} else { $Classfile = $Classfilepath + $Class + ".csv" if((Test-Path -Path $Classfile )){ #Check to see if file has already been created #"Class file already exists" } else { $Classhead = 'UPN,ADUser' "$Classhead" | Out-File $Classfile -append } if (dsquery group -name $Class) { #Write-Host "Group $Class already exists" -ForegroundColor Blue } else { $Loginfo = 'Created group ' + $Class "$Loginfo" | Out-File $Logfile -append $GroupEmail = $Class + $StudMail Write-Host "Creating Group $Class with address $GroupEmail in path $GroupOU" -ForegroundColor Green New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU } ##Check if user is already group member $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty samAccountName If ($Members -contains $SAM) { $StudData = $UserInfo.$UPNVariable + ',' + $SAM "$StudData" | Out-File $Classfile -append } else { Write-Host "Adding $SAM to group $Class" -ForegroundColor Green $Loginfo = 'Added student ' + $SAM + " to " + $Class "$Loginfo" | Out-File $Logfile -append Add-ADGroupMember -Identity $Class -Members $SAM $StudData = $UserInfo.$UPNVariable + ',' + $SAM "$StudData" | Out-File $Classfile -append } } } } } ##Cleanup Groups Write-Host "Removing old students from groups." -ForegroundColor Green $GroupList = Get-ADGroup -filter * -searchbase $GroupOU 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.ADUser -match $SAM ) { #"User $SAM found in class list for $GN" } else { Write-Host "User $SAM no longer member of $GN" $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN "$Loginfo" | Out-File $Logfile -append Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false & "C:\GAM\gam.exe" course $GN remove student $StudEmail } } } else { Write-Host "Group file for $GN does not exist" } } $EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} foreach ($EmptyGroup in $EmptyGroups){ ##Write to log file## $Loginfo = 'Removed empty group ' + $Class "$Loginfo" | Out-File $Logfile -append $DN = $EmptyGroup.DistinguishedName Remove-ADObject -Identity $DN -Confirm:$False } ##Grab Class Teachers ##Import and modify SIMS Report ##Change Header $NewSIMSTeachList = $Classfilepath + "ClassTeachersTemp.csv" Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content $NewSIMSTeachList ##Reformat Names $Names = import-csv $NewSIMSTeachList $Names | foreach-object { $_.FullName = $_.FullName.replace("Mr ","") $_.FullName = $_.FullName.replace("Mrs ","") $_.FullName = $_.FullName.replace("Ms ","") $_.FullName = $_.FullName.replace("Miss ","") $_.FullName = $_.FullName.replace(" ","") $_.FullName = $_.FullName.replace("-","") } $TempSIMSTeachList = $Classfilepath + "ClassTeacherSAM.csv" $Names | export-csv $TempSIMSTeachList -notype $FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv" ##Reformat class/teacher list Get-Content $TempSIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot ##Create the classes! $ClassList = Import-Csv -Path $FinalSIMSTeachList $FilteredCL = ($ClassList | Where {$_.FullName -ne "" }) foreach ($Item in $FilteredCL){ $Class = $Item.Class $SAM = $Item.FullName $TeachEmail = $SAM + $StudMail if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) { $CourseCSV = $GAMData + $Class + '.csv' if (Test-Path -Path $CourseCSV) { #Write-Host "Google Classroom $Class already exists." $CourseList = Import-Csv -Path $GAMCurrent $FilteredCourseList = ($CourseList | Where {$_.name -eq $Class }) foreach ($FilteredItem in $FilteredCourseList){ $CurrentOwner = $FilteredItem.ownerEmail $CourseName = $FilteredItem.name $CourseName = $CourseName.replace("\","-") $CourseName = $CourseName.replace("/","-") } if (($CurrentOwner -eq $TeachEmail)) { #"Owner already set" } else { $Loginfo = 'Google Classroom ' + $Class + ' already exists - changing owner to ' + $TeachEmail + ' and reset course to provisioned.' "$Loginfo" | Out-File $Logfile -append & $GAMPath course $Class add teacher $TeachEmail 2> $null & $GAMPath update course $Class owner $TeachEmail status PROVISIONED 2> $null } } else { Write-Host "Creating Google Classroom $Class with teacher $TeachEmail" $Loginfo = 'Created Google Classroom ' + $Class + ' with ' + $TeachEmail "$Loginfo" | Out-File $Logfile -append & $GAMPath create course alias $Class name $Class teacher $TeachEmail 2> $null & $GAMPath course $Class add teacher $TeachEmail 2> $null } ##Get Class Students $Students = Get-ADGroupMember $Class foreach ($Student in $Students) { $UserInfo = Get-ADUser -Identity $Student -Properties * $StudName = $UserInfo.displayname $StudUsername = $UserInfo.samAccountName $StudEmail = $UserInfo.EmailAddress if (Test-Path -Path $CourseCSV) { ##Google classroom already exists - check if already a member. $Participants = Import-CSV -Path $CourseCSV if ($Participants."profile.emailAddress" -match $StudEmail ) { #Write-Host "$StudUsername already member of $Class" } else { Write-Host "Adding Student $StudEmail to Google Classroom $Class" & $GAMPath course $Class add student $StudEmail 2> $null $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class "$Loginfo" | Out-File $Logfile -append } } else { ##New Google classroom - add the students. Write-Host "Adding Student $StudEmail to Google Classroom $Class" & $GAMPath course $Class add student $StudEmail 2> $null $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class "$Loginfo" | Out-File $Logfile -append } } } else { Write-Host "Class $Class does not contain any students or Teacher does not exist" } } ##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" 'Body' = "See Attached" 'Attachments' = $Logfile } Send-MailMessage @emailOptions } else { $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "No changes today." } Send-MailMessage @emailOptions } ##If SIMS data does not exist; } else { Write-Host "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" -ForegroundColor Red $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "$SIMSUPNReport 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" $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 }

Thank you!!!!!!!!!

Hi - Are these settings still valid, and do you have any additional Incoming settings? Thanks

I'm not sure that anyone outside of Edugeek's target audience would be interested in this.

Am not going to list changes line by line, but here are the highlights; Now checks for and creates missing folders prior to running. Now checks that GAM is able to grab csv of existing classrooms before proceeding. Now grabs csv of existing active/provisioned Google Classrooms. Now grabs csv of participants of each active/provisioned Google Classroom. GAM results were being displayed as NativeCommandErrors - These are redirected to $null for a cleaner script output - Not ideal, but less messy. Google Classrooms are now only created if they do not already exist - This should speed up the runtime. Students are only added to Google Classrooms if they are not already members. ##Set paths and preferences $LogfilePath = ".\Logs\" ##Path where log files are created. $Classfilepath = ".\ClassData\" ##Path where data file is created. $GAMPath = "C:\GAM\gam.exe" ##Path where GAM is installed. $GAMData = ".\GAMData\" ##Path where GAM data is recorded. $GAMDataSize = "10KB" ##Minimum expected size for successfully exported GAM Data $SIMSUPNReport = "\\SIMSServer\ReportShare$\UPNClasses.csv" ##Report containing list of current students $SIMSTeachList = "\\SIMSServer\ReportShare$\ClassTeachers.csv" $ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report $SearchOU = "OU=Students,OU=Users,DC=MySchool,DC=sch,DC=uk" #OU Containing existing Students $GroupOU = "OU=Classes,OU=Groups,DC=MySchool,DC=sch,DC=uk" $StudMail = "@MySchool.com" ##Mail domain for student accounts $UPNVariable = "POBox" ##AD option used to store SIMS UPN $SMTPServer = "smtp.myschool.com" ##SMTP server for emailing log files. $SMTPTo = "me " ##Recipient address for log files. $SMTPFrom = "someone " ##Sender address for log files. ##/Set paths and preferences ##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. } ##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 fields id fields name fields courseState > $GAMCurrent 2> $null ## Check that GAM data exists if ((Test-Path -Path $GAMCurrent) -And (Get-Item $GAMCurrent).Length -gt $GAMDataSize) { ##Grab current course participants Write-Host "Grabbing current Google Classroom participants......." -ForegroundColor Green $CourseList = Import-Csv -Path $GAMCurrent foreach ($Item in $CourseList){ $CourseName = $Item.name $CourseID = $Item.id $CourseCSV = $GAMData + $CourseName + '.csv' #"$CourseName - $CourseCSV" & $GAMPath print course-participants course $CourseID > $CourseCSV 2> $null } ## Check that source data exists - Running without source data would be bad! if ((Test-Path -Path $SIMSUPNReport) -And (Get-Item $SIMSUPNReport).Length -gt $ReportMinSize) { $Logfilename = Get-Date -UFormat "%Y%m%d" $Logfile = $Logfilepath + $Logfilename + ".txt" ##Clear old class data Write-Host "Clearing old SIMS data files...." -ForegroundColor Green Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force ##Import and modify SIMS Report Write-Host "Importing SIMS data from $SIMSUPNReport" -ForegroundColor Blue $NewSIMSUPNReport = $Classfilepath + "UPNClasses.csv" ##This gives me new lines after each $ Write-Host "Modifying SIMS data." -ForegroundColor Blue Get-Content $SIMSUPNReport -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSUPNReport Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot Write-Host "Grabbing students from $SearchOU" -ForegroundColor Green $FileList = Get-ADUser -SearchBase $SearchOU -Filter * foreach ($User in $FileList){ $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties $FullName = $UserInfo.displayname $SAM = $UserInfo.samAccountName $UPN = $UserInfo.$UPNVariable if (!$UserInfo.$UPNVariable) { "UPN Missing from user $SAM" } ELSE { #"Looking for $UPN" $ClassList = Import-Csv -Path $NewSIMSUPNReport $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.$UPNVariable }) foreach ($Item in $FilteredCL){ $Class = $Item.Class if (!$Class) {"Group name blank"} else { $Classfile = $Classfilepath + $Class + ".csv" if((Test-Path -Path $Classfile )){ #Check to see if file has already been created #"Class file already exists" } else { $Classhead = 'UPN,ADUser' "$Classhead" | Out-File $Classfile -append } if (dsquery group -name $Class) { #Write-Host "Group $Class already exists" -ForegroundColor Blue } else { ##Write to log file## $Loginfo = 'Created group ' + $Class "$Loginfo" | Out-File $Logfile -append $GroupEmail = $Class + $StudMail Write-Host "Creating Group $Class with address $GroupEmail in path $GroupOU" -ForegroundColor Green New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU } ##Check if user is already group member $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty samAccountName If ($Members -contains $SAM) { #"User $SAM already member of $Class" #Add-ADGroupMember -Identity $Class -Members $SAM $StudData = $UserInfo.$UPNVariable + ',' + $SAM "$StudData" | Out-File $Classfile -append } else { Write-Host "Adding $SAM to group $Class" -ForegroundColor Green $Loginfo = 'Added student ' + $SAM + " to " + $Class "$Loginfo" | Out-File $Logfile -append Add-ADGroupMember -Identity $Class -Members $SAM $StudData = $UserInfo.$UPNVariable + ',' + $SAM "$StudData" | Out-File $Classfile -append } } } } } ##Cleanup Groups Write-Host "Removing old students from groups." -ForegroundColor Green $GroupList = Get-ADGroup -filter * -searchbase $GroupOU 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.ADUser -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## $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN "$Loginfo" | Out-File $Logfile -append ##Remove User from Class Group Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false ##Remove User from Google Classroom & "C:\GAM\gam.exe" course $GN remove student $StudEmail } } } 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 $GroupOU | where {!$_.members} | where {!$_.membersof} foreach ($EmptyGroup in $EmptyGroups){ ##Write to log file## $Loginfo = 'Removed empty group ' + $Class "$Loginfo" | Out-File $Logfile -append $DN = $EmptyGroup.DistinguishedName Write-Host "Removing Empty Group $DN" -ForegroundColor DarkGreen Remove-ADObject -Identity $DN -Confirm:$False } ##Grab Class Teachers ##Import and modify SIMS Report ##Change Header Write-Host "Importing class teacher data from $SIMSTeachList" -ForegroundColor Green $NewSIMSTeachList = $Classfilepath + "ClassTeachersTemp.csv" Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content $NewSIMSTeachList ##Reformat Names Write-Host "Reformatting class teacher data." -ForegroundColor Green $Names = import-csv $NewSIMSTeachList $Names | foreach-object { $_.FullName = $_.FullName.replace("Mr ","") $_.FullName = $_.FullName.replace("Mrs ","") $_.FullName = $_.FullName.replace("Ms ","") $_.FullName = $_.FullName.replace("Miss ","") $_.FullName = $_.FullName.replace(" ","") $_.FullName = $_.FullName.replace("-","") $_.FullName = $_.FullName.replace("BSmith","WSmith") } $TempSIMSTeachList = $Classfilepath + "ClassTeacherSAM.csv" $Names | export-csv $TempSIMSTeachList -notype $FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv" ##Reformat class/teacher list Get-Content $TempSIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot ##Create the classes! Write-Host "Creating the classes!" -ForegroundColor Green $ClassList = Import-Csv -Path $FinalSIMSTeachList $FilteredCL = ($ClassList | Where {$_.FullName -ne "" }) foreach ($Item in $FilteredCL){ $Class = $Item.Class $SAM = $Item.FullName $TeachEmail = $SAM + $StudMail if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) { $CourseCSV = $GAMData + $Class + '.csv' if (Test-Path -Path $CourseCSV) { Write-Host "Google Classroom $Class already exists." -ForegroundColor Blue & $GAMPath course $Class add teacher $TeachEmail 2> $null } else { Write-Host "Creating Google Classroom $Class with teacher $TeachEmail" -ForegroundColor Green ##Write to log file## $Loginfo = 'Created Google Classroom ' + $Class + ' with ' + $TeachEmail "$Loginfo" | Out-File $Logfile -append & $GAMPath create course alias $Class name $Class teacher $TeachEmail 2> $null & $GAMPath course $Class add teacher $TeachEmail 2> $null } ##Get Class Students $Students = Get-ADGroupMember $Class foreach ($Student in $Students) { $UserInfo = Get-ADUser -Identity $Student -Properties * $StudName = $UserInfo.displayname $StudUsername = $UserInfo.samAccountName $StudEmail = $UserInfo.EmailAddress if (Test-Path -Path $CourseCSV) { ##Google classroom already exists - check if already a member. $Participants = Import-CSV -Path $CourseCSV if ($Participants."profile.emailAddress" -match $StudEmail ) { Write-Host "$StudUsername already member of Google Classroom $Class" -ForegroundColor Blue } else { Write-Host "Adding Student $StudEmail to Google Classroom $Class" -ForegroundColor Green & $GAMPath course $Class add student $StudEmail 2> $null ##Write to log file## $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class "$Loginfo" | Out-File $Logfile -append } } else { ##New Google classroom - add the students. Write-Host "Adding Student $StudEmail to Google Classroom $Class" -ForegroundColor Green & $GAMPath course $Class add student $StudEmail 2> $null ##Write to log file## $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class "$Loginfo" | Out-File $Logfile -append } } } else { Write-Host "Class $Class does not contain any students or Teacher does not exist" -ForegroundColor Red } } ##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" 'Body' = "See Attached" 'Attachments' = $Logfile } Send-MailMessage @emailOptions } else { $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "No changes today." } Send-MailMessage @emailOptions } ##If SIMS data does not exist; } else { Write-Host "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" -ForegroundColor Red $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "$SIMSUPNReport 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 }

Found an error; In version 3, line 73; $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty Name This should be; $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty samAccountName

Tidied things up a bit, moved the variables to the top of the script, added a check to make sure the student data exists. ##Set paths and preferences $LogfilePath = ".\Logs\" ##Path where log files are created. $Classfilepath = ".\ClassData\" ##Path where data file is created. $SIMSUPNReport = "\\SIMSServer\ReportShare$\UPNClasses.csv" ##Report containing list of current students $SIMSTeachList = "\\SIMSServer\ReportShare$\ClassTeachers.csv" $ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report $SearchOU = "OU=Students,OU=Users,DC=MySchool,DC=sch,DC=uk" #OU Containing existing Students $GroupOU = "OU=Classes,OU=Groups,DC=MySchool,DC=sch,DC=uk" $StudMail = "@MySchool.com" ##Mail domain for student accounts $UPNVariable = "POBox" ##AD option used to store SIMS UPN $SMTPServer = "smtp.myschool.com" ##SMTP server for emailing log files. $SMTPTo = "Me " ##Recipient address for log files. $SMTPFrom = "someone " ##Sender address for log files. ##/Set paths and preferences ## Check that source data exists - Running without source data would be bad! if ((Test-Path -Path $SIMSUPNReport) -And (Get-Item $SIMSUPNReport).Length -gt $ReportMinSize) { $Logfilename = Get-Date -UFormat "%Y%m%d" $Logfile = $Logfilepath + $Logfilename + ".txt" ##Clear old class data Write-Host "Clearing old data files...." -ForegroundColor Green Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force ##Import and modify SIMS Report Write-Host "Importing SIMS data from $SIMSUPNReport" -ForegroundColor Blue $NewSIMSUPNReport = $Classfilepath + "UPNClasses.csv" #This gives me new lines after each $ Write-Host "Modifying SIMS data." -ForegroundColor Blue Get-Content $SIMSUPNReport -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSUPNReport Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot Write-Host "Grabbing students from $SearchOU" -ForegroundColor Green $FileList = Get-ADUser -SearchBase $SearchOU -Filter * foreach ($User in $FileList){ $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties $FullName = $UserInfo.displayname $SAM = $UserInfo.samAccountName $UPN = $UserInfo.$UPNVariable if (!$UserInfo.$UPNVariable) { "UPN Missing from user $SAM" } ELSE { #"Looking for $UPN" $ClassList = Import-Csv -Path $NewSIMSUPNReport $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.$UPNVariable }) foreach ($Item in $FilteredCL){ $Class = $Item.Class if (!$Class) {"Group name blank"} else { $Classfile = $Classfilepath + $Class + ".csv" if((Test-Path -Path $Classfile )){ #Check to see if file has already been created #"Class file already exists" } else { $Classhead = 'UPN,ADUser' "$Classhead" | Out-File $Classfile -append } if (dsquery group -name $Class) { #Write-Host "Group $Class already exists" -ForegroundColor Blue } else { ##Write to log file## $Loginfo = 'Created group ' + $Class "$Loginfo" | Out-File $Logfile -append $GroupEmail = $Class + $StudMail Write-Host "Creating Group $Class with address $GroupEmail in path $GroupOU" -ForegroundColor Green New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU } ##Check if user is already group member $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty Name If ($Members -contains $SAM) { #"User $SAM already member of $Class" #Add-ADGroupMember -Identity $Class -Members $SAM $StudData = $UserInfo.$UPNVariable + ',' + $SAM "$StudData" | Out-File $Classfile -append } else { Write-Host "Adding $SAM to group $Class" -ForegroundColor Green $Loginfo = 'Added student ' + $SAM + " to " + $Class "$Loginfo" | Out-File $Logfile -append Add-ADGroupMember -Identity $Class -Members $SAM $StudData = $UserInfo.$UPNVariable + ',' + $SAM "$StudData" | Out-File $Classfile -append } } } } } ##Cleanup Groups Write-Host "Removing old students from groups." -ForegroundColor Green $GroupList = Get-ADGroup -filter * -searchbase $GroupOU 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.ADUser -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## $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN "$Loginfo" | Out-File $Logfile -append ##Remove User from Class Group Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false ##Remove User from Google Classroom & "C:\GAM\gam.exe" course $GN remove student $StudEmail } } } 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 $GroupOU | where {!$_.members} | where {!$_.membersof} foreach ($EmptyGroup in $EmptyGroups){ ##Write to log file## $Loginfo = 'Removed empty group ' + $Class "$Loginfo" | Out-File $Logfile -append $DN = $EmptyGroup.DistinguishedName Write-Host "Removing Empty Group $DN" -ForegroundColor DarkGreen Remove-ADObject -Identity $DN -Confirm:$False } ##Grab Class Teachers ##Import and modify SIMS Report ##Change Header Write-Host "Importing class teacher data from $SIMSTeachList" -ForegroundColor Green $NewSIMSTeachList = $Classfilepath + "ClassTeachersTemp.csv" Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content $NewSIMSTeachList ##Reformat Names Write-Host "Reformatting class teacher data." -ForegroundColor Green $Names = import-csv $NewSIMSTeachList $Names | foreach-object { $_.FullName = $_.FullName.replace("Mr ","") $_.FullName = $_.FullName.replace("Mrs ","") $_.FullName = $_.FullName.replace("Ms ","") $_.FullName = $_.FullName.replace("Miss ","") $_.FullName = $_.FullName.replace(" ","") $_.FullName = $_.FullName.replace("-","") $_.FullName = $_.FullName.replace("BSmith","WSmith") } $TempSIMSTeachList = $Classfilepath + "ClassTeacherSAM.csv" $Names | export-csv $TempSIMSTeachList -notype $FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv" ##Reformat class/teacher list Get-Content $TempSIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot ##Create the classes! Write-Host "Creating the classes!" -ForegroundColor Green $ClassList = Import-Csv -Path $FinalSIMSTeachList $FilteredCL = ($ClassList | Where {$_.FullName -ne "" }) foreach ($Item in $FilteredCL){ $Class = $Item.Class $SAM = $Item.FullName $TeachEmail = $SAM + $StudMail if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) { "Creating class $Class with teacher $TeachEmail" ##Write to log file## #$Loginfo = 'Created ' + $Class + ' with ' + $TeachEmail #"$Loginfo" | Out-File $Logfile -append & "C:\GAM\gam.exe" create course alias $Class name $Class teacher $TeachEmail & "C:\GAM\gam.exe" course $Class add teacher $TeachEmail ##Get Class Students $Students = Get-ADGroupMember $Class foreach ($Student in $Students) { $UserInfo = Get-ADUser -Identity $Student -Properties * $StudName = $UserInfo.displayname $StudUsername = $UserInfo.samAccountName $StudEmail = $UserInfo.EmailAddress #"Adding Student $StudEmail to class $Class" & "C:\GAM\gam.exe" course $Class add student $StudEmail ##Write to log file## #$Loginfo = $Class + ',' + $StudEmail #"$Loginfo" | Out-File $Logfile -append } } else { Write-Host "Class $Class does not contain any students or Teacher does not exist" -ForegroundColor Red } } ##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" 'Body' = "See Attached" 'Attachments' = $Logfile } Send-MailMessage @emailOptions } else { $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "No changes today." } Send-MailMessage @emailOptions } } else { Write-Host "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" -ForegroundColor Red $EmailOptions = @{ 'SMTPServer' = $SMTPServer 'To' = $SMTPTo 'From' = $SMTPFrom 'Subject' = "Results from Google Classes Script" 'Body' = "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" } Send-MailMessage @emailOptions }

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 }

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.

Added lines 62-93 to remove students from classrooms if they are no longer members of the class. There may be other tweaks, but it was very late at night when I finally got it running, and I did doze off a few times whilst the test scripts were running. $Logfilename = Get-Date -UFormat "%Y%m%d" $Logfilepath = "C:\UserCreate\GoogleGroups\Logs\" $Logfile = $Logfilepath + $Logfilename + ".txt" ##Clear old class data $Classfilepath = "C:\UserCreate\GoogleGroups\ClassData\" Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force ##Import and modify SIMS Report $csvInFile="\\SIMSServer\ReportShare$\UPNClasses.csv" $csvOutFile="C:\UserCreate\GoogleGroups\ClassData\UPNClasses.csv" #This gives me new lines after each $ Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot $OU = "OU=Students,OU=Users,DC=mydomain,DC=sch,DC=uk" $GroupOU = "OU=Classes,OU=Groups,DC=mydomain,DC=sch,DC=uk" $FileList = Get-ADUser -SearchBase $OU -Filter * foreach ($User in $FileList){ $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties $FullName = $UserInfo.displayname $SAM = $UserInfo.samAccountName $UPN = $UserInfo.POBox if (!$UserInfo.POBox) { "UPN Missing from user $SAM" } ELSE { #"Looking for $UPN" $ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\ClassData\UPNClasses.csv' $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.POBox }) foreach ($Item in $FilteredCL){ $Class = $Item.Class if (!$Class) {"Group name blank"} else { $Classfile = $Classfilepath + $Class + ".csv" if((Test-Path -Path $Classfile )){ #Check to see if file has already been created #"Class file already exists" } else { $Classhead = 'UPN,ADUser' "$Classhead" | Out-File $Classfile -append } if (dsquery group -name $Class) {"Group $Class already exists"} else { ##Write to log file## $Loginfo = 'Created group ' + $Class "$Loginfo" | Out-File $Logfile -append $GroupEmail = $Class + "@mydomain.com" "Creating Group $Class with address $GroupEmail in path $GroupOU" New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU } Add-ADGroupMember -Identity $Class -Members $SAM $StudData = $UserInfo.POBox + ',' + $SAM "$StudData" | Out-File $Classfile -append } } } } ##Cleanup Groups $GroupList = Get-ADGroup -filter * -searchbase $GroupOU 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.ADUser -match $SAM ) { #"User $SAM found in class list for $GN" } else { #"User $SAM no longer member of $GN" ##Write to log file## $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN "$Loginfo" | Out-File $Logfile -append ##Remove User from Class Group Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false ##Remove User from Google Classroom & "C:\GAM\gam.exe" course $GN remove student $StudEmail } } } else { "Group file for $GN does not exist" } } $EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} foreach ($EmptyGroup in $EmptyGroups){ ##Write to log file## $Loginfo = 'Removed empty group ' + $Class "$Loginfo" | Out-File $Logfile -append $DN = $EmptyGroup.DistinguishedName "Removing Empty Group $DN" Remove-ADObject -Identity $DN -Confirm:$False } ##Grab Class Teachers ##Import and modify SIMS Report $csvInFile="\\SIMSServer\ReportShare$\ClassTeachers.csv" ##Change Header Get-Content $csvInFile -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content "C:\UserCreate\GoogleGroups\ClassData\ClassTeachersTemp.csv" ##Reformat Names $Names = import-csv "C:\UserCreate\GoogleGroups\ClassData\ClassTeachersTemp.csv" $Names | foreach-object { $_.FullName = $_.FullName.replace("Mr ","") $_.FullName = $_.FullName.replace("Mrs ","") $_.FullName = $_.FullName.replace("Ms ","") $_.FullName = $_.FullName.replace("Miss ","") $_.FullName = $_.FullName.replace(" ","") $_.FullName = $_.FullName.replace("-","") $_.FullName = $_.FullName.replace("MWonford","EWonford") $_.FullName = $_.FullName.replace("BBills","WBills") } $Names | export-csv "C:\UserCreate\GoogleGroups\ClassData\ClassTeacherSAM.csv" -notype ##Remove Temp Files ##Remove-Item -Path "C:\UserCreate\GoogleGroups\ClassTeachersTemp.csv" -Force -Recurse $csvInFile="C:\UserCreate\GoogleGroups\ClassData\ClassTeacherSAM.csv" $csvOutFile="C:\UserCreate\GoogleGroups\ClassData\ClassTeachers.csv" ##Reformat class/teacher list Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot ##Create the classes! $ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\ClassData\ClassTeachers.csv' $FilteredCL = ($ClassList | Where {$_.FullName -ne "" }) foreach ($Item in $FilteredCL){ $Class = $Item.Class $SAM = $Item.FullName $TeachEmail = $SAM + "@mydomain.com" if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) { "Creating class $Class with teacher $TeachEmail" ##Write to log file## #$Loginfo = 'Created ' + $Class + ' with ' + $TeachEmail #"$Loginfo" | Out-File $Logfile -append & "C:\GAM\gam.exe" create course alias $Class name $Class teacher $TeachEmail & "C:\GAM\gam.exe" course $Class add teacher $TeachEmail ##Get Class Students $Students = Get-ADGroupMember $Class foreach ($Student in $Students) { $UserInfo = Get-ADUser -Identity $Student -Properties * $StudName = $UserInfo.displayname $StudUsername = $UserInfo.samAccountName $StudEmail = $UserInfo.EmailAddress #"Adding Student $StudEmail to class $Class" & "C:\GAM\gam.exe" course $Class add student $StudEmail ##Write to log file## #$Loginfo = $Class + ',' + $StudEmail #"$Loginfo" | Out-File $Logfile -append } } else { "Class $Class does not contain any students or Teacher does not exist" } }

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

In a previous post I showed how I'm grabbing SIMS data to create AD accounts using powershell. That was a side-quest to this, my main objective of automatically creating Google Classrooms using SIMS data. I'm already using GCDS to sync all my staff and students over to G-Suite. That was fairly straightforward to do. I was initially just going to allow staff to create their own classrooms, and use the SIMS data to populate distribution lists with students so that a teacher could invite their entire class in one simple step. However, at this moment there is not a simple/fast way to update the required permissions on these groups, so I decided to just create and pre-populate all of the classes myself. However, as I had already created all of the distribution groups, I used these to determine class membership. The powershell script I'm using could be adapted to bypass this step and just compare two reports. To export the reports, I'm using CommandReporter. See my previous post for the permissions and command line I use. The first SIMS report I created contains the Student UPNs and Class code; This needed some reformatting as some invalid characters have been used in our SIMS class codes. This reformatting is all done automatically in the first few lines of the powershell script. The second SIMS report contains the same class codes and the full name of the class teacher; Lines 64-82 of the powershell script deal with removing titles from the staff name to match our staff usernames (initial+surname) and again reformat the class code to match those in the previous report. To interact with Google Classroom, I'm using GAM. I've only scratched the surface of what this tool can do, and I'm looking forward to experimenting with it further. The powershell script; ##Import and modify SIMS Report $csvInFile="\\SIMSSERVER\Sharedfolder$\UPNClasses.csv" $csvOutFile="C:\UserCreate\GoogleGroups\UPNClasses.csv" #This gives me new lines after each $ Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot ##Creates mail distribution group for each class $OU = "OU=Students,OU=CCW Users,DC=ccw,DC=kent,DC=sch,DC=uk" $GroupOU = "OU=Classes,OU=CCW Groups,DC=ccw,DC=kent,DC=sch,DC=uk" Import-Module ActiveDirectory $FileList = Get-ADUser -SearchBase $OU -Filter * foreach ($User in $FileList){ $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties $FullName = $UserInfo.displayname $SAM = $UserInfo.samAccountName $UPN = $UserInfo.POBox if (!$UserInfo.POBox) { "UPN Missing from user $SAM" } ELSE { #"Looking for $UPN" $ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\UPNClasses.csv' $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.POBox }) foreach ($Item in $FilteredCL){ $Class = $Item.Class if (!$Class) {"Group name blank"} else { if (dsquery group -name $Class) {"Group $Class already exists"} else { $GroupEmail = $Class + "@theccw.net" "Creating Group $Class with address $GroupEmail in path $GroupOU" New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU } Add-ADGroupMember -Identity $Class -Members $SAM } } } } ##Cleanup Empty Groups $EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} foreach ($EmptyGroup in $EmptyGroups){ $DN = $EmptyGroup.DistinguishedName "Removing Empty Group $DN" Remove-ADObject -Identity $DN -Confirm:$False } #$Logfilename = Get-Date -UFormat "%Y%m%d" #$Logfilepath = "C:\UserCreate\GoogleGroups\Logs\" #$Logfile = $Logfilepath + $Logfilename + ".csv" ##Grab Class Teachers ##Import and modify SIMS Report $csvInFile="\\SIMSSERVER\sharedfolder$\ClassTeachers.csv" ##Change Header Get-Content $csvInFile -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content "C:\UserCreate\GoogleGroups\ClassTeachersTemp.csv" ##Reformat Teacher Names $Names = import-csv "C:\UserCreate\GoogleGroups\ClassTeachersTemp.csv" $Names | foreach-object { $_.FullName = $_.FullName.replace("Mr ","") #Get rid of titles $_.FullName = $_.FullName.replace("Mrs ","") $_.FullName = $_.FullName.replace("Ms ","") $_.FullName = $_.FullName.replace("Miss ","") $_.FullName = $_.FullName.replace(" ","") #Get rid of spaces $_.FullName = $_.FullName.replace("-","") $_.FullName = $_.FullName.replace("BSmith","WSmith") #If you have any staff whose legal names don't match their usernames, you can swap them one at a time like this. } $Names | export-csv "C:\UserCreate\GoogleGroups\ClassTeacherSAM.csv" -notype $csvInFile="C:\UserCreate\GoogleGroups\ClassTeacherSAM.csv" $csvOutFile="C:\UserCreate\GoogleGroups\ClassTeachers.csv" #Reformat class/teacher list Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile Import-Module ActiveDirectory $dnsroot = '@' + (Get-ADDomain).dnsroot ##Create the classes! $ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\ClassTeachers.csv' $FilteredCL = ($ClassList | Where {$_.FullName -ne "" }) foreach ($Item in $FilteredCL){ $Class = $Item.Class $SAM = $Item.FullName $TeachEmail = $SAM + "@theccw.net" if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) { "Creating class $Class with teacher $TeachEmail" ##Write to log file## #$Loginfo = $Class + ',' + $TeachEmail #"$Loginfo" | Out-File $Logfile -append & "C:\GAM\gam.exe" create course alias $Class name $Class teacher $TeachEmail ##Get Class Students $Students = Get-ADGroupMember $Class foreach ($Student in $Students) { $UserInfo = Get-ADUser -Identity $Student -Properties * $StudName = $UserInfo.displayname $StudUsername = $UserInfo.samAccountName $StudEmail = $UserInfo.EmailAddress #"Adding Student $StudEmail to class $Class" & "C:\GAM\gam.exe" course $Class add student $StudEmail ##Write to log file## #$Loginfo = $Class + ',' + $StudEmail #"$Loginfo" | Out-File $Logfile -append } } else { "Class $Class does not contain any students or Teacher does not exist" } #"Adding $SAM to group $Class" #Add-ADGroupMember -Identity $Class -Members $SAM #Remove-ADGroupMember -Identity $Class -Members $SAM } It takes a few hours to run, but I end up with a mail distribution group for every class (that can't be used to send class invites ) and all of my classes setup in Google Classroom ready for the teacher to activate. Again, I hope that this is of use to someone else. I was planning to write a longer thread with more annotation, but winter is coming!

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.

Thank you - Will call Orbtalk.

Hi All I'm looking for another provider to quote for SIP trunks, FTTC and all associated costs. I already have a quote from a company I know and a quote from a company that cold-called me at the right time. I need at least one more quote, and would prefer to contact someone with a good reputation. Anyone else hosting their own PBX and happy with their VoIP provider? Ideally I'd want one that includes all landline/mobile costs in their monthly rental. Regards Shaun

Hi All Just wanted to share this in case it saves someone else a bit of work. My predecessor here had been using login hours to control when user accounts could be used for controlled assessments. As our lessons don't start/end on the hour, this potentially gave the students extra time. So I decided to have a play with powershell to try and come up with something better. I wanted a script that would disable all accounts in a particular OU, and also log them out. I found this post by Jeffery Hicks which is a fairly simple method of using logon/logoff scripts to track where your users are. This worked great, and required only a small change to line 24 to both the login and logout scripts to record the info I needed. I changed the login script line 24 to '$note = $env:computername' so that just the computer name was recorded, and the logout script line to '$note = "NA"' so that my script would ignore clients that were already logged off. I then cobbled together these; #EnableAccounts Import-Module ActiveDirectory Get-ADUser -Filter 'Name -like "*"' ` -SearchBase "OU=Year 11 Computer Science 2017,OU=Controlled Assessment,OU=Students,OU=CCW Users,DC=mydomain,DC=sch,DC=uk" | Enable-ADAccount #DisableAccountsAndLogoutClients #Set the search OU $OU = "OU=Year 11 Computer Science 2017,OU=Controlled Assessment,OU=Students,OU=CCW Users,DC=mydomain,DC=sch,DC=uk" Import-Module ActiveDirectory # Get list of users $UserList = Get-ADUser -SearchBase $OU -Filter * foreach ($User in $UserList){ $CurrentUserDetails = Get-ADuser -Identity $User -Properties * # Grab all user properties $Computer = $CurrentUserDetails.info # Set Computer variable to value of user info Disable-ADAccount -Identity $User # Disable User account write-host Account $User has been disabled. IF (($Computer -eq 'NA') -or (!$Computer)) # Check whether user has computer name in info field { write-host User not logged in } ELSE { write-host Testing connection to workstation $Computer IF (Test-Connection -ComputerName $Computer -Count 1 -Quiet) # Ping the computer { (gwmi win32_operatingsystem -ComputerName $Computer).Win32Shutdown(4) # Force log off the computer write-host $Computer Logged Off } ELSE { write-host workstation already logged off } } $info = "NA" #define a string to indicate status Set-ADUser -Identity $User -Replace @{info="$info"} #update the Info user property ; } I have the first scheduled to run a minute before the assessment is due to start and the second to run a minute after the assessment ends. I hope someone else finds it useful, and if anyone can suggest any improvements I'd love to hear them.

Hi All Whilst I await a response from my SIMS support provider, I thought I'd ask here. All of my teachers have left for the summer, taking their laptops with them. All of the laptops are configured to remotely connect from home via Microsoft Direct Access. This is working fine and I can happily manage the clients whilst they're off site. We haven't had a SIMS upgrade since rolling out DA, but as SIMS was working and SOLUS3 was reporting the clients as online when they were off site, I was not expecting any problems............... What was I thinking? It appears that the SOLUS 3 agent reports it's IPv4 address to the server rather that it's DNS name. As a result, the SOLUS server tries to push upgrades to the IPv4 address rather than simply use DNS. I have no clue why it's doing this. The agents are reporting as online, and I can even uninstall/reinstall the agent to a remote PC through SOLUS. Has anyone else experienced this, and was there a workaround? Regards Shaun.

While testing Office365, I've found that Microsoft don't seem to keep their documentation up to date with the current version.

We're testing both right now. Google Apps is definitely easier to use, but Office365 gives a lot more control......

LOL Apparently that would be "too confusing for staff".

Yes - sorry.

Our SIP servers are virtual, so hooking up the analogue line would be tricky

I'd considered that, but didn't want too much hassle.

Anyone seen any good comparison articles / videos?

So, getting back on topic, fax to email anyone?