Introduction

I have being playing around with Powershell a lot more lately and trying to learn and utilise it as much as possible. One of the repetitive tasks we deal with is when a student exits the school. There are a number of steps in AD that have to be undertaken each time a student leaves. These include:

• Disable the account
• Move the account to a ‘Disabled’ ou
• Remove the account from all Groups except ‘Domain Users’
• Hide the user in the Exchange Address List

I knew it would be pretty easy to get a Powershell script to carry out these tasks, the tricky bit was going to be getting it automated so I have to do a as little as possible. I could enter the usernames that were to be disabled, but even better would be to get a scheduled task going that would check for users to be disabled and then carry out the tasks.

Our school uses the SIMON system to manage students. When a student is leaving the administration staff update this in SIMON and IT get an email notifying us to disable the accounts. SIMON has an MS SQL back-end so I figured if I could pull the information on the students who were leaving, I could pass this to my Powershell script to process their accounts.

High Level Steps

1. Powershell script runs which queries the SQL database to see if any students are flagged to have exited in the last week. If there is, it outputs the details to a CSV.
2. Powershell script imports this CSV, checks the usernames to see if the accounts are enabled
3. If they are enabled it disables the account, moves it to a different OU etc…
4. Script updates a log to say which accounts have been disabled and when
5. Sends an email with the details of the accounts that have been disabled

Powershell Code

For ease of reading I have broken it up into a couple of sections

Querying the SQL Database

This section creates the connection to the SQL server, sends the query, then if there are results it outputs to a CSV file with that days date as the name.

—————————-

# Source SQL Server
$SQLServerName =

# Source SQL Database
$SQLDatabase =

# SQLQuery
$SQLQuery1 = “SELECT blah blah'”

# Source SQL Username
$SQLUsername =

# Source SQL Password
$SQLPassword =

# SQL Server Connection Settigs
$SQLConnectionString = “Server=$SQLServer;uid=$SQLUsername; pwd=$SQLPassword;Database=$SQLDatabase;Integrated Security=False;”
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $SQLConnectionString

# Issue SQL Connection Commands for Query1
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery1
$SQLCommand.Connection = $SQLConnection

# Pull data
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
$SQLDataSet = New-Object System.Data.DataSet
$SQLAdapter.Fill($SQLDataSet)

$SQLQuery1DataSet = $SQLDataSet.Tables[0]

Process Exiting Students

This section:

• imports the CSV with todays date (if it exists)
• loops through the CSV and first checks if the account is enabled
• then hides it from the address list, removes it from groups, moves it to a ‘Disabled OU’, disables the account

—————————

#Set the variables to be used

$CurrentYear = (Get-Date).ToString(‘yyyy’)
$TodaysDate = (Get-Date).ToString(‘yyyy-MM-dd’)
$DisabledOU =

Conclusion