Automatically Create Student Active Directory Accounts

Issue:

UMRA is getting more complicated and the more users you have the more expensive it becomes.  PowerShell is only complicated if you don’t have an example to work from.  Below is an example on how to automatically create Student accounts for Active Directory by pulling data from an SIS database.  There is example code on how to inactivate accounts for inactive students.  This code will also move students from one school to another.  This will help you to transition from UMRA to using PowerShell so that student account creation and Office 365 licensing can be completely automated.  The code below is an example.  There are many variables, groups, and paths that will need to be changed to get this working for your organization.  Test the script thoroughly to verify that it is working as expected.

The last remaining item is to make an easy way to reset student passwords for staff.  I will make a future article to discuss this.

Solution:

Auto Create Student Accounts Steps Overview:

Import-Module ActiveDirectory
Import-Module ADSync

<#######################################################################################################################

 Object Name: CreateStudentADAccounts.ps1
 Object Type: Powershell
 Related Objects:
 Object Description:
 #         This script is an example script on how to create Student user accounts from an SIS in an environment with
 #         Active Directory and Exchange Hybrid Mode with Office 365.  If student accounts are setup consistently and 
 #         automatically this makes things easier with SSO integration, Clever, or other 3rd party intregrations.
 #
 #         
 Comments:
 #         This script has to run from the AD Connect Server where Office 365 and AD Synchronization of User Accounts occur.
 #         Once you get the variables set properly.  
 #         Debug in sections to find problems and use Write-Host to confirm the end result variable values as needed
 #         Everyone's Active Directory setup and Email Mailbox configuration can be different. 
 #         Examine how user accounts are currently setup and use the powershell code below as an example to get it setup right
 #         Organization Units need to be already created and this script OU's updated to reflect the current Active Directory environment

#######################################################################################################################
#>


#Create Student Active Directory Accounts
#Steps Overview:

    #Activate any student accounts that returned
    #Create any accounts that don't exist
    #Move any student accounts that need to be moved
    #Delete any student accounts that are inactive
    #Call another powershell script to auto license all unlicensed students in Office 365 
       #http://www.questiondriven.com/2016/01/19/office-365-license-user-and-provision-onedrive-via-powershell-scheduled-task/


#School Sites
#School1
#School2


#initialize variables
$usernames = @()
$csvusernames = ''


#Editable Variables
$domain = "contoso"
$moveMailboxToOffice365 = $True
$addClassroomLic = $True
$databaseServer = 'DatabaseServerName'
$Database = 'ActiveUsers'
$localExchangeConnectionURI = 'http://LOCALEXCHANGESERVER/PowerShell/'
$targetEmailDomain = '@***.mail.onmicrosoft.com'
$office365EmailDomain = '@contoso.com'
$administratorUsername = "SomeGlobalAdmin@contoso.com"

#Value for AD Property Company to save on each student account
$company='CONTOSO'

#Value for AD Property profilepath for each student account
$profilePath = ''
#$profilePath = 'C:\Profile.man'

#Value for AD Property script path if needed for student user accounts
$scriptPath = ''   

#Connect to Local Exchange stored password (if using a stored password)
#$passwordfile = 'c:\temp\storedpasswordforpowershell.txt';

#$secpasswd = ConvertTo-SecureString "NewPassword" -AsPlainText -Force
#$secpasswd = Get-Content $passwordfile | ConvertTo-SecureString;
#$cred = New-Object System.Management.Automation.PSCredential($administratorUsername, $secpasswd);

$cred = Get-Credential -Message "Domain Credential" -UserName $administratorUsername 


$logfile = 'c:\temp\CreateADAccountsLog.txt'

Set-Content $logfile ""




#Add any schools as needed through this script
#Change AD Groups and OU's throughout this script as needed

#End Editable Variables



#Functions

function ExecuteReader-SQL($sqlText, $database, $server, $parameters=@{})
{
    #Execute a sql statement.  Parameters are allowed.
    #Input parameters should be a dictionary of parameter names and values.
    #Return value will usually be a list of datarows.
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $results = @()
    Try
    {
        $connection.Open();
        $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

        foreach($p in $parameters.Keys)
        {
            [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
        }
        $reader = $cmd.ExecuteReader()

        $results = @()
        while ($reader.Read())
        {
            $row = @{}
            for ($i = 0; $i -lt $reader.FieldCount; $i++)
            {
                $row[$reader.GetName($i)] = $reader.GetValue($i)
            }
            $results += new-object psobject -property $row            
        }
    }
    catch
    {
        $results = $Null
    }
    finally
    {
        $connection.Close();
    }

    $results
}


function ExecuteNonQuery-SQL($sqlText, $database = "", $server = "", $parameters=@{})
{
    #Execute a sql statement.  Parameters are allowed.
    #Input parameters should be a dictionary of parameter names and values.
    #Return value will usually be a list of datarows.
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $rowsAffected = 0
    try
    {
        $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

        $connection.Open();
        foreach($p in $parameters.Keys)
        {
            [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
        }
        $rowsAffected = $cmd.ExecuteNonQuery()
        
    }
    catch
    {
        $rowsAffected = -1
    }
    finally
    {
        $connection.Close();
    }
    $rowsAffected
}



#End Functions

###################################### Create Student Accounts, Move Student Accounts, or Re-enable prevously inactivated student accounts

Add-Content $logfile "Starting Creation of Users"

#Pull Students that have moved schools or new students from Database and Loop with code below
#See the example query near the bottom of this script
#This query needs to pull active students with enrollment date changes recently (firstName, initials, lastName,samAcountName,department,grade) are the columns expected
$query = 'SELECT firstName,initials,lastName,samAccountName,department,grade FROM ActiveStudentUsers'
$params = @{};
#$params.Add($key, $value)
$rows = ExecuteReader-SQL -sqlText $query -database $Database -server $databaseServer -parameters $params

foreach ($row in $rows)
{
    #I renamed the column aliases that the  query pulls to be the property names that will end up in AD to avoid confusion 
    $firstName = "$($row.firstName)".Trim()
    $initials = "$($row.initials)".Trim()
    $lastName= "$($row.lastName)".Trim()
    $samAccountName = "$($row.samAccountName)".Trim()
    $department = "$($row.department)".Trim()   #department is intended to mean school.   It will be stored in AD in the department field
    $grade = "$($row.grade)".Trim()

    #change this if you want a different display name template
    $displayName = "$lastName, $firstName $initials".Trim()

    #Change this path as needed for where student documents shares will be stored
    $homeDirectory = "\\CONTOSO.com\Schools\Students\$samAccountName"
    
    
	
    #change the upn template as needed
    $userPrincipalName = "$samAccountName" + "$office365EmailDomain"
    $ADOUPath = ''

    #Switch Statement for each school to set some variables that change per school
    #Change OU's as needed per school
    ################# Set Variables for user based on Assigned School
    switch($department)
    {
        "School1" {
	        $ADOUPath = 'OU=Students,OU=Instructional,OU=School1,OU=School Sites,DC=CONTOSO,DC=com'
            $jobTitle = 'School1 Student'
            $studentpassword = 'DefaultPasswordForThisSchool1'
	        break;
        }
        "School2" {
	        $ADOUPath = 'OU=Students,OU=Instructional,OU=School2,OU=School Sites,DC=CONTOSO,DC=com'
            $jobTitle = 'School2 Student'
            $studentpassword = 'DefaultPasswordForThisSchool2'
	        break;
        }
        

    }

    #If the user exists in the correct OU we just need to make sure the current active student has an active account
    $userExist1 = Get-ADUser -Filter {sAMAccountName -eq $samAccountName} -SearchBase $ADOUPath
    If ($userExist1 -ne $Null) 
    {
        ##################### User Already Exists in the correct OU  
	    #Do Nothing user is already created and in correct OU/School groups
	    WRITE-HOST "Username already exists in correct OU, No change needed: $samAccountName"
	    Add-Content $logfile "Username already exists in correct OU, No change needed: $samAccountName"
        
        #If the user exists and is disabled then enable the student that returned to the same school
        If ($userExist1.enabled -eq $False)
        {
            Enable-ADAccount -Identity $samAccountName -Confirm:$false
            Write-Host "Enabled Student Account: $samAccountName"
            Add-Content $logfile "Enabled Student Account: $samAccountName"
        }
        #Unlock-ADAccount -Identity $samAccountName -Confirm:$false
    }
    else
    {
        #Find the user if it exists anywhere in Active Directory
        #If the account is in the wrong OU then we just need to move the account into the correct Schools' OU
        $userExist2 = Get-ADUser -Filter {sAMAccountName -eq $samAccountName} 
        If ($userExist2 -ne $Null) 
        {
            #######################  Users Exists but Move User to Correct OU and Add Groups
            Write-Host "Move User: $samAccountName"
            
            #Move the Student user Account because of school change
            $oldDN = $($userExist2.distinguishedname)
        
            #Remove Groups from a student user because of school change
            $membergroups = Get-ADPrincipalGroupMembership $samAccountName | Select samAccountName
            foreach ($mgroup in $membergroups)
            {
                $groupname = $($mgroup.samAccountName)
                if ($groupname -ne 'Domain Users')
                {
                    Remove-ADGroupMember -identity $groupname -Member $samAccountName -Confirm:$false
                }
            }
        
            #Add the groups to the student for the new school
            try
            {
			    ################## Add Groups to the User

                #Groups All users need
                #change goups as needed
			    Add-ADGroupMember "Students" $samAccountName
               
                #Add more schools as needed
                #change groups as needed
                switch($department)
                {
                    "School1" {
	                    Add-ADGroupMember "School1 Students" $samAccountName
	                    break;
                    }
                    "School2" {
	                    Add-ADGroupMember "School2 Students" $samAccountName
	                    break;
                    }
                    

                }

                #change grade level groups as needed
                switch ($grade)
			    {
                    "00" {
                        Add-ADGroupMember "Student K-3" $samAccountName
                    }
                    "01" {
                        Add-ADGroupMember "Student K-3" $samAccountName
                    }
                    "02" {
                        Add-ADGroupMember "Student K-3" $samAccountName
                    }
                    "03" {
                        Add-ADGroupMember "Student K-3" $samAccountName
                    }
                    "04" {
                        Add-ADGroupMember "Student 4-6" $samAccountName
                    }
                    "05" {
                        Add-ADGroupMember "Student 4-6" $samAccountName
                    }
                    "06" {
                        Add-ADGroupMember "Student 4-6" $samAccountName
                    }
                    "07" {
                        Add-ADGroupMember "Student 7-8" $samAccountName
                    }
                    "08" {
                        Add-ADGroupMember "Student 7-8" $samAccountName
                    }

                }
                        
		    }
            catch
            {
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                Write-Host "Move Student Add Groups Error: $samAccountName $FailedItem $ErrorMessage"
                Add-Content $logfile "Move Student Add Groups Error: $samAccountName $FailedItem $ErrorMessage"
            }


            #Edit User Properties for the OU move
            try
            {
                #If we have the basic data needed then set the properties
                if ($jobTitle)
                { 
                    Set-ADUser -Identity $samAccountName -Replace @{ Title=$jobTitle }
                    Set-ADUser -Identity $samAccountName -Description $jobTitle
                    Enable-ADAccount -Identity $samAccountName -Confirm:$false
                    Set-ADAccountPassword -Identity $samAccountName -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $studentpassword -Force)
                    Unlock-ADAccount -Identity $samAccountName -Confirm:$false
                    Set-ADUser -Identity $samAccountName -ChangePasswordAtLogon $false 
                    Write-Host "Moving User setting properties: $samAccountName"
                    Add-Content $logfile "Moving User setting properties: $samAccountName"
                }
                if ($department)
                { 
                    Set-ADUser -Identity $samAccountName -Replace @{ physicalDeliveryOfficeName=$department }
                }
                if ($grade)
                {
                    Set-ADUser -Identity $samAccountName -Replace @{ facsimileTelephoneNumber=$grade }
                }
            }
            catch
            {

            }


            try
            {
                #Move Student's user to the correct OU because of a school change
                Move-ADObject -Identity $oldDN -TargetPath $ADOUPath
                Write-Host "Moved User: $samAccountName to $ADOUPath"
                Add-Content $logfile "Moved User: $samAccountName to $ADOUPath"
            }
            catch
            {
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                Write-Host "Move Student Move AD Object Error: $samAccountName $FailedItem $ErrorMessage"
                Add-Content $logfile "Move Student Move AD Object Error: $samAccountName $FailedItem $ErrorMessage"
            }

        }
        else
        {
            ##################### User Does Not Exist, Create the User 
            #Student's user account does not exist anywhere in AD then we need to create it.
            #Create the New Student User Account
		    Write-Host "Create User: $samAccountName"
            $majorError = 0
            Write-Host "lastName: $lastName"
            Write-Host "firstName: $firstName"
            Write-Host "initials: $initials"
            WRITE-HOST "userPrincipalName: $userPrincipalName"
            WRITE-HOST "HomeDirectory: $homeDirectory"
            WRITE-HOST "ADOUPath: $ADOUPath"
            WRITE-HOST "department: $department"
            WRITE-HOST "grade: $grade"
            

		    try
            {
			    ################### Create the User in Active Directory
			    #https://technet.microsoft.com/en-us/library/ee617215.aspx
			    #https://technet.microsoft.com/en-us/library/hh852238(v=wps.630).aspx
			    $newuser = New-ADUser -SamAccountName $samAccountName -UserPrincipalName $userPrincipalName -Name $samAccountName -DisplayName $displayName -GivenName $firstName -SurName $lastName -Initials $initials -Department $department -Office $department -Company $company -Description $jobTitle -Path $ADOUPath -HomeDirectory $homeDirectory -HomeDrive 'H' -ScriptPath $scriptPath -AccountPassword (ConvertTo-SecureString $studentpassword -AsPlainText -force) -ChangePasswordAtLogon $False -Enabled $True -PasswordNeverExpires $False -PassThru 
                Add-Content $logfile "Created User: $samAccountName"
            }
            catch
            {
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                Write-Host "Create User Error: $samAccountName $FailedItem $ErrorMessage"
                Add-Content $logfile "Create User Error: $samAccountName $FailedItem $ErrorMessage"
                $majorError = 1
            }	
            if ($majorError -eq 0)
            {	
                try
                {		
                    ################### Set User Properties  
                        
                    #Add usernames to array for migration of Office 365 mailboxes
				    $usernames += $samAccountName 
                        
                    #Wait for user to be created
                    Start-Sleep -Seconds 60
                        
                        
				    if ($jobTitle)
                    { 
                        $newuser | SET-ADUSER -Replace @{ Title=$jobTitle }
                    }
                    if ($department)
                    { 
                        $newuser | SET-ADUSER -Replace @{ physicalDeliveryOfficeName=$department }
                    }
                    if ($grade)
                    {
                        $newuser | SET-ADUSER -Replace @{ facsimileTelephoneNumber=$grade }
                    }

				    
                        
			    }
                catch
                {
                    $ErrorMessage = $_.Exception.Message
                    $FailedItem = $_.Exception.ItemName
                    Write-Host "Set User Properties Error: $FailedItem $ErrorMessage"
                    Add-Content $logfile "Set User Properties Error: $FailedItem $ErrorMessage"
                }

                try
                {
				    ################### Set "Remote Desktop Services Profile" tab options
				    $distinguishedName = $($newuser.distinguishedName)
				    $user = [ADSI] "LDAP://$distinguishedName"
				    #$user.psbase.invokeset("TerminalServicesProfilePath",$profilePath)
				    $user.psbase.Invokeset("TerminalServicesHomeDirectory",$homeDirectory)
				    $user.psbase.invokeset("TerminalServicesHomeDrive","H:")
				    $user.setinfo()
                   
			    }
                catch
                {
                    $ErrorMessage = $_.Exception.Message
                    $FailedItem = $_.Exception.ItemName
                    Write-Host "Remote Desktop Services Profile Error: $samAccountName $FailedItem $ErrorMessage"
                    Add-Content $logfile "Remote Desktop Services Profile Error: $samAccountName $FailedItem $ErrorMessage"
                }

                try
                {
				    ################### Create Home Directory
				    If (!(Test-Path $homeDirectory))
				    {
					    New-Item -ItemType Directory -Path $homeDirectory
				    }
				
				    #Start Set Folder Permissions
				    $UsersAm = "$domain\$samAccountName" #presenting the sAMAccountname in this format 
				    #stops it displaying in Distinguished Name format 

				    #Define FileSystemAccessRights:identifies what type of access we are defining, whether it is Full Access, Read, Write, Modify

				    $FileSystemAccessRights = [System.Security.AccessControl.FileSystemRights]"FullControl"

				    #define InheritanceFlags:defines how the security propagates to child objects by default
				    #Very important - so that users have ability to create or delete files or folders 
				    #in their folders
				    $InheritanceFlags = [System.Security.AccessControl.InheritanceFlags]::"ContainerInherit", "ObjectInherit"

				    #Define PropagationFlags: specifies which access rights are inherited from the parent folder (users folder).
				    $PropagationFlags = [System.Security.AccessControl.PropagationFlags]::None

				    #Define AccessControlType:defines if the rule created below will be an 'allow' or 'Deny' rule
				    $AccessControl =[System.Security.AccessControl.AccessControlType]::Allow 
				    #define a new access rule to apply to users folfers
				
				    $NewAccessrule = New-Object System.Security.AccessControl.FileSystemAccessRule ($UsersAm, $FileSystemAccessRights, $InheritanceFlags, $PropagationFlags, $AccessControl) 
				
				    $currentACL = Get-ACL -path	$homeDirectory
				    $currentACL.SetAccessRule($NewAccessrule)
				    Set-ACL -path $homeDirectory -AclObject $currentACL
                    Write-Host "Create Home Folder: $samAccountName"
                    Add-Content $logfile "Create Home Folder: $samAccountName"
				    #End Folder Permissions
			    }
                catch
                {
                    $ErrorMessage = $_.Exception.Message
                    $FailedItem = $_.Exception.ItemName
                    Write-Host "Create Home Directory Error: $samAccountName $FailedItem $ErrorMessage"
                    Add-Content $logfile "Create Home Directory Error: $samAccountName $FailedItem $ErrorMessage"
                }
				
                    

			    try
                {
				    ################## Add Groups to the Student User Account

                    #Groups All users need
				    #change groups as needed
                    Add-ADGroupMember "Students" $samAccountName
				    

                    #Add schools as needed
                    #Add/change groups as needed
                    switch($department)
                    {
                        "School1" {
	                        Add-ADGroupMember "School1 Students" $samAccountName
	                        break;
                        }
                        "School2" {
	                        Add-ADGroupMember "School2 Students" $samAccountName
	                        break;
                        }
                        

                    }

                    #change grade level groups as needed
                    switch ($grade)
				    {
                        "00" {
                            Add-ADGroupMember "Student K-3" $samAccountName
                        }
                        "01" {
                            Add-ADGroupMember "Student K-3" $samAccountName
                        }
                        "02" {
                            Add-ADGroupMember "Student K-3" $samAccountName
                        }
                        "03" {
                            Add-ADGroupMember "Student K-3" $samAccountName
                        }
                        "04" {
                            Add-ADGroupMember "Student 4-6" $samAccountName
                        }
                        "05" {
                            Add-ADGroupMember "Student 4-6" $samAccountName
                        }
                        "06" {
                            Add-ADGroupMember "Student 4-6" $samAccountName
                        }
                        "07" {
                            Add-ADGroupMember "Student 7-8" $samAccountName
                        }
                        "08" {
                            Add-ADGroupMember "Student 7-8" $samAccountName
                        }

                    }
                    Write-Host "Assigned Groups to user: $samAccountName"
                    Add-Content $logfile "Assigned Groups to user: $samAccountName"
                        
			    }
                catch
                {
                    $ErrorMessage = $_.Exception.Message
                    $FailedItem = $_.Exception.ItemName
                    Write-Host "Assign AD Groups Error: $samAccountName $FailedItem $ErrorMessage"
                    Add-Content $logfile "Assign AD Groups Error: $samAccountName $FailedItem $ErrorMessage"
                }
                $Session = $Null
                try
                {
                    Start-Sleep -Seconds 90
                    ################## Create the Mailbox
                    $remotemailbox = "$samAccountName" + "$targetEmailDomain"
                    $userm = Get-ADUser -Identity $samAccountName
                    $newDN = $($userm.distinguishedname)
			        $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $localExchangeConnectionURI  -Authentication Kerberos -Credential $cred
			        Import-PSSession $Session
			        
                    #If you do not have Office 365 with Hybrid mode you just create the local exchange mailbox here instead.
                    #Enable-Mailbox -Identity $samAccountName
                    
                    #This is for Exchange and Office 365 Hybrid Mode.  This set the default AD mail properties without creating a mailbox locally.
                    #Set the default mail properties in AD without creating a local mailbox so that Office 365 mailbox will be created once licensed
                    Enable-MailUser -Identity $samAccountName -ExternalEmailAddress $remotemailbox
			        #delay so that enable-mailuser is applied first
                    Start-Sleep -Seconds 60
                    
                    #This is for Office 365 and Exchange Hybrid Mode
                    #Force the student user account to be a remote mailbox on Office 365 without ever having a local mailbox after all the other properties have been set by Enable-MailUser
                    #This allow the mailbox to be created in Office 365 directly without ever being a local mailbox
                    #This has the consequence that the mailbox cannot be migrated back locally, but with the benefit of never creating a local mailbox or migrating it.
                    Set-ADUser -Identity $samAccountName -Replace @{msExchHideFromAddressLists = $True; msExchRecipientDisplayType = "-2147483642"; msExchRecipientTypeDetails = "2147483648"; msExchRemoteRecipientType = "4"};
                    
                    Start-Sleep -Seconds 30
                    #Get the email address policy to apply
                    Set-RemoteMailbox -Identity $newDN -EmailAddressPolicyEnabled $true
                    
                    Write-Host "Enabled Exchange AD Properties: $samAccountName"
                    Add-Content $logfile "Enabled Exchange AD Properties: $samAccountName"
                    
                }
                catch
                {
                    $ErrorMessage = $_.Exception.Message
                    $FailedItem = $_.Exception.ItemName
                    Write-Host "Create Local Mailbox Error: $samAccountName $FailedItem $ErrorMessage"
                    Add-Content $logfile "Create Local Mailbox Error: $samAccountName $FailedItem $ErrorMessage"
                }
                finally
                {
                    #Disconnect from local exchange
                    Remove-PSSession $Session
                }
        
            }
            else
            {
                Write-Host "Major Error User failed to create:  $samAccountName"
                Add-Content $logfile "Major Error User failed to create:  $samAccountName"
            }
        }
    }
}
Add-Content $logfile "Finished Creating New Users"

#Run Active Directory Synch
Write-Host "Synchronizing local AD with Azure AD"
Start-ADSyncSyncCycle -PolicyType Delta
Start-Sleep -Seconds 700
Write-Host "Finished Synchronizing local AD with Azure AD"


####################################### License All the Student User Accounts that are not licensed

#License the Students and Provision OneDrive
Write-Host "Licensing students in Office365"
#change this to be run from DIRSYNC locally
#See this website for sample script to auto license students with Office 365
#http://www.questiondriven.com/2016/01/19/office-365-license-user-and-provision-onedrive-via-powershell-scheduled-task/
C:\ScheduledTasks\AssignLicensesToStudentsInOffice365.ps1
Write-Host "Finished Licensing students in Office365"
Add-Content $logfile "Finished Licensing students in Office365"


#####################################  Inactivate or Delete Student User Accounts that have left

#Delete students that have left
#I chose to periodically delete student accounts during the regular school year.
$intdayOfWeek = ([Int] (Get-Date).DayOfWeek)
$intmonth = ([Int] (Get-Date).Month)
$intday = ([Int] (Get-Date).Day)
#Only run the delete/inactivate during the school year and at the end of the week
If ($intdayOfWeek -eq 5 -and $intmonth -ne 7 -and (-not ($intmonth -eq 6 -and $intday -gt 3)) -and (-not ($intmonth -eq 8 -and $intday -lt 14)))
{
    #Delete Student Accounts that have not been logged in for 120 days or more
    #Get-AdUser -Filter {Memberof -eq "CN=Students,OU=School Sites,DC=CONTOSO,DC=com" -and Enabled -eq $false -and (lastlogondate -le $v120Days -or lastlogondate -notlike "*") -and (samAccountName -like '8*' -or samAccountName -like '9*')} -SearchBase 'OU=School Sites,DC=CONTOSO,DC=com' -Properties samAccountName,enabled,lastLogonTimestamp | Select samAccountName,enabled,lastLogonTimestamp,@{n="lastLogonDate1";e={[datetime]::FromFileTime($_.lastLogonTimestamp)}}
    $v120Days = (get-date).adddays(-120)
    $oldusers = Get-AdUser -Filter {Memberof -eq "CN=Students,OU=School Sites,DC=CONTOSO,DC=com" -and Enabled -eq $false -and lastlogondate -le $v120Days -and (samAccountName -like '8*' -or samAccountName -like '9*') } -SearchBase 'OU=School Sites,DC=CONTOSO,DC=com' | Select samAccountName, cn
    foreach ($olduser in $oldusers)
    {
        #If you don't want to delete student accounts you could delete or comment out this entire foreach.
        $samAccountName2 = $($olduser.samAccountName)
        ##Remove-ADUser -Identity $samAccountName2
        Add-Content $logfile "Deleted User: $samAccountName2"
        Write-Host "Deleted User: $samAccountName2"
    }

    #Delete Users that have never logged in
    #Don't know the date the account was disabled and do no have a lastlogondate so must periodically delete these accounts every once in a while
    #Only run this on the first Friday of the month specified in the if condition
    if (($intmonth -eq 11 -or $intmonth -eq 2 -or $intmonth -eq 5) -and $intday -lt 8 )
    {
        $oldusers1 = Get-AdUser -Filter {Memberof -eq "CN=Students,OU=School Sites,DC=CONTOSO,DC=com" -and Enabled -eq $false -and lastlogondate -notlike "*" -and (samAccountName -like '8*' -or samAccountName -like '9*') } -SearchBase 'OU=School Sites,DC=CONTOSO,DC=com' | Select samAccountName, cn
        foreach ($olduser1 in $oldusers1)
        {
            $samAccountName3 = $($olduser1.samAccountName)
            Remove-ADUser -Identity $samAccountName3
            Add-Content $logfile "Deleted User: $samAccountName3"
            Write-Host "Deleted User: $samAccountName3"
        }

    }


    #Inactivate Users that are no longer in use or have left their school
    #I run this after the remove-aduser so that accounts that have never logged in won't be deleted right away
    #This pulls a list of all student accounts and lists any that are not active students or not found in the SIS (orphaned student user accounts as well)
    $query1 = 'EXECUTE dbo.spGetInactiveStudents'
    $params1 = @{};
    #$params.Add($key, $value)
    $rowsd = ExecuteReader-SQL -sqlText $query1 -database $synergyDatabase -server $databaseServer -parameters $params1

    foreach ($row1 in $rowsd)
    {
        $samAccountName1 = "$($row1.samAccountName)"
        $userToInactive = Get-ADUser -Identity $samAccountName1
        If ($userToInactive.enabled -eq $True)
        {
            Disable-ADAccount -identity $samAccountName1
            Add-Content $logfile "Inactivated User: $samAccountName1"
            Write-Host "Inactivated User: $samAccountName1"
        }
        else
        {
            Write-Host "User Already Inactivated: $samAccountName1"
        }
    }
}

Add-Content $logfile "Finished Creating, Licensing, and Removing Student AD Accounts"


<#




#query used to pull inactive students in Active Directory even orphaned users
#Change the OU's as needed and AD filtering
#SQL Server has linked server to Active Directory for querying

#Linked Server Script for AD
#-- EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
#-- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'CONTOSO\administrator',@rmtpassword='########'


--Example Query to get inactive accounts when pulling accounts from AD and comparing with SIS active students
--Each School has to have less than 1000 students for this example to work
/****** Object:  StoredProcedure [dbo].[spGetInactiveStudents]    Script Date: 3/22/2017 3:39:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Preston Cooper
-- Create date: 03/14/2016
-- Description:	This query pulls student users from AD for each school separately and then excludes any users found in Synergy. 
--              Anything left is an inactive account student account.  Querying AD is limited to 1000 records.
-- =============================================
ALTER PROCEDURE [dbo].[spGetInactiveStudents]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- change the AD filters as needed below
    -- Make sure to test this query and see if it works as expected.  Test with results expected and without
    -- Make a union statement for each school because of the 1000 query result limitation
    -- I limited the results to 35 to limit changes for each school that could happen in a single day, this TOP value can be changed
    SELECT TOP 35 sAMAccountName 
	FROM
	(SELECT TOP 1000 sAMAccountName
	FROM OpenQuery  
		(ADSI,
		 '<LDAP://OU=School1,OU=Sites,DC=CONTOSO,DC=com>;(&(&(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))(memberOf=CN=Students,OU=Sites,DC=CONTOSO,DC=com))(|(sAMAccountName=1*)(sAMAccountName=2*)));sAMAccountName')
		 ) AS AD
	WHERE sAMAccountName NOT IN (
			--All Active Students at any school
			SELECT DISTINCT samAccountName
			FROM     ActiveStudentUsers
	)
	UNION ALL
	SELECT TOP 35 sAMAccountName 
	FROM
	(SELECT TOP 1000 sAMAccountName
	FROM OpenQuery  
		(ADSI,
		 '<LDAP://OU=School2,OU=Sites,DC=CONTOSO,DC=com>;(&(&(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))(memberOf=CN=Students,OU=Sites,DC=CONTOSO,DC=com))(|(sAMAccountName=1*)(sAMAccountName=2*)));sAMAccountName')
		 ) AS AD
	WHERE sAMAccountName NOT IN (
			--All Active Students at any school
			SELECT DISTINCT samAccountName
			FROM     ActiveStudentUsers
	)

	
END





#>

Resources:

Leave a Reply

%d bloggers like this: