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:<\/p>\n
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.<\/p>\n
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.<\/p>\n
For ease of reading I have broken it up into a couple of sections<\/p>\n
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.<\/p>\n
—————————-<\/p>\n
# Source SQL Server<\/em> # Source SQL Database<\/em> # SQLQuery<\/em> # Source SQL Username<\/em> # Source SQL Password<\/em> # SQL Server Connection Settigs<\/em> # Issue SQL Connection Commands for Query1<\/em> # Pull data<\/em> $SQLQuery1DataSet = $SQLDataSet.Tables[0]<\/em><\/p>\n This section:<\/p>\n —————————<\/p>\n #Set the variables to be used<\/em><\/p>\n $CurrentYear = (Get-Date).ToString(‘yyyy’)<\/em> 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 […]<\/p>\n","protected":false},"author":1,"featured_media":78,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,3],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/64"}],"collection":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=64"}],"version-history":[{"count":13,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/64\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/64\/revisions\/77"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/media\/78"}],"wp:attachment":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=64"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=64"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=64"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n $SQLServerName =<\/em><\/p>\n
\n $SQLDatabase =<\/em><\/p>\n
\n $SQLQuery1 = “SELECT blah blah'”<\/em><\/p>\n
\n $SQLUsername =<\/em><\/p>\n
\n $SQLPassword =<\/em><\/p>\n
\n $SQLConnectionString = \u201cServer=$SQLServer;uid=$SQLUsername; pwd=$SQLPassword;Database=$SQLDatabase;Integrated Security=False;\u201d<\/em>
\n $SQLConnection = New-Object System.Data.SqlClient.SqlConnection<\/em>
\n $SQLConnection.ConnectionString = $SQLConnectionString<\/em><\/p>\n
\n $SQLCommand = New-Object System.Data.SqlClient.SqlCommand<\/em>
\n $SQLCommand.CommandText = $SQLQuery1<\/em>
\n $SQLCommand.Connection = $SQLConnection<\/em><\/p>\n
\n $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter<\/em>
\n $SQLAdapter.SelectCommand = $SQLCommand<\/em>
\n $SQLDataSet = New-Object System.Data.DataSet<\/em>
\n $SQLAdapter.Fill($SQLDataSet)<\/em><\/p>\nProcess Exiting Students<\/h2>\n
\n
\n $TodaysDate = (Get-Date).ToString(‘yyyy-MM-dd’)<\/em>
\n $DisabledOU =<\/em><\/p>\nUpdate The Exiting Students Log file<\/h2>\n
Send email of log<\/h2>\n
Conclusion<\/h1>\n