Tools4ever Solutions
(UMRA) - User Management Resource Administrator: Download
(ESSOM) - Enterprise Single Sign-On Manager: Download
(SSRPM) - Self Service Reset Password Management: Download

Test Environment: Server - Server 2008 R2
UMRA - 10.5 (build 1638)
Student Information System - Pearson PowerSchool
Destination System - Active Directory + Google Apps

If you haven't already, make sure you go read my blog post on setting up a UMRA project to connect to your Google Apps environment. http://umrahelp.com/blog1.php/2010/11/29/umra-how-to-connect-to-google-apps-2

I did a post about a year back that talked about how UMRA can extract data from Pearson PowerSchool Student Information System, then sync that data with Active Directory and other downstream systems. Over the last year or so, this blog post has received thousands of hits, however it was an old blog post transferred from my older blog. So with this post I hope to go over the sync process in a little more detail, and give you some examples of what you can do while using UMRA automation the sync.

The fist thing you will need to do is figure out the connection method to your PowerSchool SIS. Typically there are 2 connection methods.

ODBC Connection - If you have a locally hosted PowerSchool SIS system, you can connect to the database and run a sql query, view, or stored proc to obtain the student or staff data.

.txt or .csv File - In some cases you wont have direct access to the Powerschool SIS system. In these cases the SIS is hosted remotely, so in this case you can either obtain a daily .txt or .csv file of the student or staff data.

Now, UMRA has both of these methods fully covered. UMRA uses a ODBC connection wizard to walk you through setting up the database connection string to your Powerschool SIS System. If you are only able to obtain a .txt or .csv UMRA is also able to read in these files, and display the data within them. Ok, so now that we have the data pulled within UMRA, I can now talk about some of the steps you can do with your UMRA automation.

Below is a typical sync I've done from Student data in PowerSchool to Active Directory.

Step 1 - Create a table of Active Directory Student Information
Step 2 - Create a table of Student data in PowerSchool
Step 3 - Join both AD and SIS data on a common key (username, student id etc.)
Step 4 - Based on this join, you will have 3 results (0,1,2)
Step 5 - Result 0 = Match in SIS and AD (compare user)
Step 6 - Result 1 = Record is in SIS data, but not AD (create user)
Step 7 - Result 2 = Record is in AD data, but not SIS data (retire user)

Below is a screen shot of how a UMRA project will look with some of the above steps. Keep in mind, we have no error logic added into our script, this can be added, but this is just an example of how it can look.

You can see, its an very easy process to figure out what student accounts within your student Powershcool data needs to either be created, compared, or disabled. Now that a defined process, we can go into more detail on what we can do on each of the result types. Now keep in mind that we are only going to go over the basics that I've seen in most UMRA automations.

In Detail Step 5 - Result 0 = Match in SIS and AD (compare user)
Since we have a match both in the Powerschool SIS data, and the AD data, we know that we can compare the data in both systems to see if anything has changed. An example of some of the common changes we've seen include, grade, school, status,name, or any other attribute you want to set and keep in sync with your SIS data.

In Detail Step 6 - Result 1 = Record is in SIS data, but not AD (create user)
Since a record shows up in your Powerschool SIS data but not Active Directory, we know we need to create the account in Active Directory. Upon the creation of an account, we can a lot of different procedures such as, setting group memberships, create home drives, map the user to a specific Active Directory OU, and even create a Google Apps Account.

Create Student Process Example Work Flow-
Step 1 - Create Account Active Directory
Step 2 - Apply Group Memberships based on school + grade etc.
Step 3 - Create Home Folder On Server + Setup Security on that folder
Step 4 - Create Google Apps Account

Just a small note on the Google Apps account. UMRA can do all the same actions you can do within your Google Apps admin console such as, creating an account, applying group memberships, suspend, unsuspend, even delete the account if needed.

In Detail Step 7 - Result 2 = Record is in AD data, but not SIS data (retire user)
Now this step can very depending on how you have your Powerschool data kick out displaying if someone has either withdrawn or graduated. In some causes the Student record just wont show up in the PowerSchool data anymore, or the status column could be a "w" or another flag to determine if the user is no long with the school district.
There are a lot of steps similar to the create, that we can do on the account that meets the specific flag. Below is an example of some of the steps UMRA can do.

Disable Process Example Work Flow-
Step 1 - Disable the account in Active Directory
Step 2 - Move the account in Active Directory to a Disabled OU
Step 3 - Suspend the Google Apps Account
Step 4 - Time / Date stamp the accounts description to say "Disabled by UMRA on 6/2/2011"
Step 5 - Check any Disabled Accounts to see if the account has been disabled for more the XX Days. If the account has been disabled for more then XX days.

Logging and Auditing
Some of you might be asking how does UMRA log the actions that it takes on object with Active Directory, or any other system. By default UMRA has a built in log, that logs every single action taken with any of the scripts performed with UMRA. On the other hand, you can also get a more details set of audit data. With most automation projects knowing what happened and what was changed in Active Directory is vital. So any actions that your UMRA projects performs, is recorded to a SQL database, or .txt / .csv file. UMRA can log out, date / time , who on, what attribute was changed, old value, new value, and any relevant notes on that action.

As you can see UMRA is a very powerful to automation and sync your Active Directory with PowerSchool student data. As always if you have any questions please feel free to email me, or leave a comment below.