UMRA – Staff / Faculty Roster Management

I’ve been tasked with doing an UMRA Automation project for a local Washington School district to automate Roster Creation, and Group / Resource management on these Rosters. Due to the Faculty / Staff to student relationship, we are taking over 30k records to manage, and make sure this UMRA Automation project was able to run multiple times a day. What is Staff / Faculty Roster Management you ask? For this schools definition was to have Security Groups in Active Directory created, and then students who are in the Staff / Faculty Roster (Data in SQL Database) to be added / removed from these groups. Then each group will have a corresponding folder system created for the individual Roster Groups, that give different permissions depending on the Roster Group. Now, this is no easy task to auto check these rosters within a UMRA Automation project. So I will try to give you a very top level overview of how I handled this project, and really cut each piece down into smaller pieces, to get this project to work.

UMRA – Staff / Faculty Roster Management Setup

When creating a UMRA Automation project to manage Staff / Faculty Rosters automatically you will need to break down each of the main tasks, into smaller tasks, since there are a lot of moving parts. First you will need 2 stored procedures or views in your SQL database. In my case I had 2 stored procedures I called, 1 with a staff / faculty to roster relationship, and another for the student to staff / faculty relationship. Now, when you’re dealing with 30K+ records you really need to focus on how to handle your data, and cycle through your data quickly. One was I did this was to have my UMRA datatable run specific queries against the stored procedures. Here are the steps below I did to break this project down into more simpler stream lined processes.

Step 1: Get Roster / GroupName mappings, how will the Rosters be named? Once you have this figured out, you can now have your first part of your UMRA Automation loop through the stored procedure and make sure all groups that need to be in Active Directory are there, if they are not create the group in the location it needs to be in.

Tip – If possible try to have the School – Faculty ID or Faculty Name in the GroupName, this will make it easier to parse later without additional UMRA datatable loops.

Step 2: Now that we have all the groups created in Active Directory, we can now loop through these groups and add/remove Roster members. Now there are a lot of ways you can do this, however the quickest way to do this is. Grab all groups that match your group creation above, put those into a UMRA datatable, now loop through that table, pass the GroupName (or your other ad attributes) to another UMRA Automation project, this project will pass data to your SQL stored procedure with the student to Roster mappings, this table will only bring back the results for what users should be in this roster, clear all members out of the roster, then loop through that table and add the users in 1 by 1, or turn your UMRA datatable into a multivalve variable and add them all to the group at once, both ways are fine.

Tip - Remember to do error checking, make sure you can do all pre tasks before adding users to their Rosters.

Tip – Remove ALL all users in the roster, BEFORE you do your loop to add rostered users.

Step 3: Now that we have all of our Groups for our Rosters created, and our students assigned to their current rosters, we can now loop through and create the Roster Home Folders. In my case, the Roster Home Folders were created in the same structure as the Active Directory Group mapping. So I created another table of all groups that met my group naming convention above, and looped through that table to create my Home Folders.

Tip – Make sure you do a check, IF the home folder exists, then go to the next record, IF it dosen't exist then create it.

This should get your started if not through most of having Staff / Faculty Roster Management Setup automaticly with a UMRA Automation Project. If you have any questions please feel free to email me or comment.