<#
==Synopsis==
This script takes a mass export of students and creates accounts in both Google (via GAM) and Active Directory (via powershell).
The same username and password for each user is the same in all locations.
Originally designed for use with Skyward with Apple's School Manager export structure.
==Critical Notes==
1. AD samAccountName has a unchangeable max of 20 characters.
2. Processing is dependant on CSV headers matching up. Obviously.
3. Graduation Year is calculated by comparison current date. Ex) A 5th grader in January graduates a different year than a 5th grader in November.
==Synopsis==
1. Start a transcript for reviewing errors.
2. Check Google & AD Org Units. Create if missing.
3. Split the mass CSV of students into separate grades. This makes GAM processing faster than doing one student at a time and
makes it easier to dump them all in the same OU.
4. Each new CSV is imported to Google via GAM. Creates new users, updates passwords for existing users, and makes sure they're in specified student group.
5. Active Directory user and home directory are created. Existing users have their passwords updated.
Author: Andrew Schott
Company: Joletec, Inc.
Last Modifed: 2018-02-20
#>
$transcriptPath = 'C:\StudentAccounts.log'
[int]$year = (Get-Date -Format "yyyy")
$studentsFile = 'C:\Users\administrator.BSCS\Desktop\Google School Directory Sync\students.csv'
$gradeFiles = 'C:\Users\administrator.BSCS\Desktop\Google School Directory Sync\grade_level'
$AD = 'BSCS'
$emailDomain = '@bscs.k12.mi.us'
$ADou = 'OU=Students,OU=Users,OU=BSCS,DC=bscs,DC=local'
$HomeDriveLetter='H:\'
$FileServer = 'FS-01.bscs.local'
Start-Transcript -path $transcriptPath -append
#Google OU check. Create if missing
$i = $year
While ($i -ne ($year+15)){
Write-Host "Check Google OU $i"
if(-Not(GAM info org "Students/$i")){
#Create missing OU
GAM create org "$i" parent "/Students"
Write-Host "Created Google OU $i"
}
$i++
}
#AD OU check. Create if missing
$i = $year
While ($i -ne ($year+15)){
Write-Host "Check AD OU $i"
if(-Not([adsi]::Exists("LDAP://OU=$i,$ADou"))){
#Create missing OU
NEW-ADOrganizationalUnit $i –path $ADou
Write-Host "Created AD OU $i"
}
$i++
}
#Split student export into individual files sorted by grade
$data = Import-CSV -Delimiter "," -Path $studentsFile -Header school_id,student_id,first_name,last_name,grade_level,user_name,password
foreach ($group in $data | Group grade_level){
if ($group.name -ne 'grade_level'){
$data | Where-Object {$_.grade_level -eq $group.name} |
ConvertTo-Csv -NoTypeInformation |
foreach {$_.Replace('"','')} |
Out-File "$gradeFiles\$($group.name).csv"
}
}
Write-Host "Separated grade levels"
#Import each grade into Google
$Grades = Get-ChildItem -Path $gradeFiles
foreach($file in $Grades){
Import-Csv -Delim ',' $file.FullName |
# Modify grade_level to grad_year
ForEach-Object {$_.grade_level = "Students/$(IF((Get-Date) -ge (Get-Date -Month 09 -Day 01)){"{0:yyyy}" -f (Get-Date).AddYears(13 - [int]$_.grade_level)}else{"{0:yyyy}" -f (Get-Date).AddYears(12 - [int]$_.grade_level)})";$_} |
# Modify user_name to email_address
ForEach-Object {$_.user_name = "$($_.user_name+$emailDomain)";$_} |
Export-Csv $file -Delim ',' -NoTypeInformation
# Update existing users
gam csv $file gam update user ~user_name password ~password
# Create new users
gam csv $file GAM create user ~user_name firstname ~first_name lastname ~last_name password ~password org ~grade_level
# Add to Student group
gam csv $file GAM update group students add member ~user_name
Write-Host "Finished $file"
}
# Active Directory User
import-csv $studentsFile | % {
$GivenName = $_.first_name
$GivenName = (Get-Culture).TextInfo.ToTitleCase($GivenName.ToLower())
$Initials = $_.Initials
$SurName = (Get-Culture).TextInfo.ToTitleCase($_.last_name)
$SurName = (Get-Culture).TextInfo.ToTitleCase($SurName.ToLower())
$DisplayName = "$GivenName $SurName"
echo $DisplayName
$SamAccountName = $_.user_name
echo $SamAccountName
[int]$GradYear = IF((Get-Date) -ge (Get-Date -Month 09 -Day 01)){"{0:yyyy}" -f (Get-Date).AddYears(13 - $_.grade_level)}else{"{0:yyyy}" -f (Get-Date).AddYears(12 - $_.grade_level)}
$Email = "$SamAccountName$emailDomain"
$Path = "OU=$GradYear,$ADou"
$HomeDrive = $HomeDriveLetter
$HomeDirectory = "\\$FileServer\$GradYear\$SamAccountName"
$Password = $_.password
if(dsquery user -samid $SamAccountName){
#User Exists. Updating password instead
Set-ADAccountPassword -Identity $SamAccountName -NewPassword (ConvertTo-SecureString $Password -AsPlainText -force)
echo '--Password reset.'
}else{
New-ADUser -GivenName $GivenName -Initials $Initials -SurName $SurName -Name $DisplayName -DisplayName $DisplayName -SamAccountName $SamAccountName -Email $Email -UserPrincipalName $Email -CannotChangePassword $TRUE -Path $Path -homeDrive $HomeDrive -homeDirectory $HomeDirectory -AccountPassword (ConvertTo-SecureString $Password -AsPlainText -force) -Department $Department -Enabled $true
Set-ADUser -Identity $SamAccountName -Add @{ProxyAddresses="SMTP:"+$Email}
write (($HomeDirectory.Split("\") |measure-object).Count -1)
if((($HomeDirectory.Split("\") |measure-object).Count -1) -gt 3)
{
New-Item –path $HomeDirectory -type directory -force
$colRights = [System.Security.AccessControl.FileSystemRights]"FullControl"
$InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"
$PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None
$objType =[System.Security.AccessControl.AccessControlType]::Allow
$objUser = New-Object System.Security.Principal.NTAccount($AD+'\'+$SamAccountName)
$objACE = New-Object System.Security.AccessControl.FileSystemAccessRule `
($objUser, $colRights, $InheritanceFlag, $PropagationFlag, $objType)
$objACL = Get-ACL $HomeDirectory
$objACL.AddAccessRule($objACE)
Set-ACL $HomeDirectory $objACL
}
echo '--Account Created.'
}
}
Stop-Transcript