At St Mary’s we use Schoolbase from Furlong Solutions, for our MIS - we have done so for several years (indeed my understanding is that we were the second or third official school to have the system in production).

We have had, and continue to have, an excellent and honest relationship with Furlong.  From my experience, I cannot speak highly enough of their commitment, flexibility and moral approach to supporting schools.  Of the many MIS companies that I have been involved with in recent years (one way or another), Furlong often exceed expectation, when faced with a desperate requirement for a quick solution turnaround (very Agile like).

For blog integrity, I should register that IMHO, there are some aspects of Schoolbase which alas, impress me less (primarily concerning UX / UI) – this should however, not reduce any of the aforementioned compliments; the dream is that Furlong will someday employ a team of experienced professional UX designers - you could then have one exceptional product, backed by one exceptional company!

So, AD groups from Schoolbase?

Like most enterprise environments, we use Active Directory groups extensively for managing security permissions on resources (printers / folder etc).

To manually create these groups in AD, when they already exist in Schoolbase, does feel very inefficient; an ideal candidate for the DRY (do not repeat) principle?  Agreed it’s not specifically a software concern, but it is still a repetition process – and always potentially dangerous!

(picture credit: http://deviq.com/most-popular-principles/don-t-repeat-yourself)

I enjoy writing small utilities that serve specific purposes - I tend to do this in C# nowadays.

One such utility, is the ability to enumerate through group information held in Schoolbase, creating and populating Active Directory groups appropriately.  This maintains consistent synchronisation between the two systems – ensuring Schoolbase remains the prime point of entry (the Oracle, so to speak – Geek joke - pun intended!) thus reducing the chance of users being granted incorrect AD permissions to resources.

We previously managed this with an array of VBScripts – the C# solution is much more efficient!

The solution!

Luckily, we are able use an existing Schoolbase SQL View to gather all appropriate group information from the Schoolbase database – this view is called “GroupRep”.

We connect this sql view to a C# Console Application.  I recommend a console application, primarily because it can be triggered from a batch file running in task scheduler etc.

The workflow of this program is detailed below, followed by the actual code itself.

If viewing code is not your thing, please feel free to jump straight to the section below the code, which contains a link to the compiled standalone executable, a database connection configuration file and an example batch file for triggering the whole thing!

The workflow

• We use Linq to SQL to connect to the “GroupRep” view, passing in query filters - from the batch file - in order to only select those groups matching specific subject name(s) etc.
• We then enumerate through the data result, where necessary, removing existing members from any Schoolbase named AD group, in the specific OU – this will only remove group membership from these groups in this OU – it will not touch any other AD groups.
• We then use this same enumeration process to create new AD Groups based on the Schoolbase group name (where they do not exist already).
• Finally we populate / re-populate these Schoolbase AD groups, with appropriate students - based on the Schoolbase student ADS Username field.

It’s worth noting that there are numerous optimisations within the code, designed to prevent repetitive and unnecessary tasks.

namespace CreateADGroups
{
    class Program
    {
        static void Main(string[] args)
        {
            string activeDirPath = args[0];
            string activeDirOU = args[1];
            string usersLocation = args[2];
            string filterIn = args[3];
            string filterOut = args[4];

            var DBConnString = System.Configuration.ConfigurationManager.ConnectionStrings["CreateADGroups.Properties.Settings.SchoolbaseConnectionString"].ToString();
            GetGroupDataDataContext GroupData = new GetGroupDataDataContext(DBConnString);
            List<string> groupNameIncludeFilter = new List<string> { };
            List<string> groupNameExcludeFilter = new List<string> { };
            List<string> groupBeenChecked = new List<string> { };

            string[] filterInLoop = filterIn.Split('|');
            foreach (string filterInWord in filterInLoop)
            {
                groupNameIncludeFilter.Add(filterInWord);
            }

            string[] filterOutLoop = filterOut.Split('|');
            foreach (string filterOutWord in filterOutLoop)
            {
                groupNameExcludeFilter.Add(filterOutWord);
            }


            var groupRecord = from x in GroupData.GroupReps.AsEnumerable()
                              where groupNameIncludeFilter.Any(f => x.Subject.ToLower().Contains(f.ToLower()))
                              && groupNameExcludeFilter.Any(e => !x.Subject.ToLower().Contains(e.ToLower()))
                              select x;

            foreach (var groupName in groupRecord)
            {
                try
                {

                    if (!groupBeenChecked.Contains(groupName.GroupName.ToString()))
                    {
                        RemoveUserFromGroup(groupName.GroupName, activeDirOU, activeDirPath);
                        Create(activeDirOU + "," + activeDirPath, groupName.GroupName);
                        groupBeenChecked.Add(groupName.GroupName.ToString());
                    }
                    AddToGroup(groupName.PupADSname, groupName.GroupName, usersLocation, activeDirOU, activeDirPath);
                }
                catch (Exception e)
                {
                    Console.WriteLine("Group Name Log (" + e.Message.ToString() + ")");
                }

            }

        }


        static void Create(string ouPath, string name)
        {
            if (!DirectoryEntry.Exists("LDAP://CN=" + name + "," + ouPath))
            {
                try
                {
                    // bind to the container, e.g. LDAP://cn=Users,dc=...
                    DirectoryEntry entry = new DirectoryEntry("LDAP://" + ouPath);

                    // create group entry
                    DirectoryEntry group = entry.Children.Add("CN=" + name, "group");

                    // set properties
                    group.Properties["sAmAccountName"].Value = name;

                    // save group
                    group.CommitChanges();
                }
                catch (Exception e)
                {
                    Console.WriteLine("Create Group Log " + ouPath.ToString() + " - " + name.ToString() + "(" + e.Message.ToString() + ")");
                }
            }
            else { Console.WriteLine(ouPath + " already exists"); }
        }



        static void RemoveUserFromGroup(string groupDn, string activeDirOU, string activeDirPath)
        {
            try
            {
                List<string> usersInGroup = new List<string> { };
                DirectoryEntry dirEntry = new DirectoryEntry("LDAP://CN=" + groupDn + "," + activeDirOU + "," + activeDirPath);

                foreach (var dn in dirEntry.Properties["member"])
                {
                    usersInGroup.Add(dn.ToString());
                }

                foreach (var user in usersInGroup)
                {
                    dirEntry.Properties["member"].Remove(user);
                    dirEntry.CommitChanges();
                    dirEntry.Close();
                }

            }
            catch (System.DirectoryServices.DirectoryServicesCOMException e)
            {
                Console.WriteLine("Remove User Log" + " - group probably exists elsewhere on AD (" + e.Message.ToString() + ")");
            }
        }


        static void AddToGroup(string userDn, string groupDn, string usersLocation, string activeDirOU, string activeDirPath)
        {
            try
            {
                DirectoryEntry dirEntry = new DirectoryEntry("LDAP://CN=" + groupDn + "," + activeDirOU + "," + activeDirPath);
                string distinguished = GetUserDn(userDn, usersLocation, activeDirPath);
                dirEntry.Properties["member"].Add(distinguished);
                dirEntry.CommitChanges();
                dirEntry.Close();
            }
            catch (System.DirectoryServices.DirectoryServicesCOMException e)
            {
                Console.WriteLine("Add User Log For " + groupDn.ToString() + " - group probably exists elsewhere on AD (" + e.Message.ToString() + ")");
            }
        }


        static string GetUserDn(string identity, string usersLocation, string activeDirPath)
        {
            using (var rootEntry = new DirectoryEntry("LDAP://" + usersLocation + "," + activeDirPath, null, null, AuthenticationTypes.Secure))
            {
                using (var directorySearcher = new DirectorySearcher(rootEntry, String.Format("(sAMAccountName={0})", identity)))
                {
                    var searchResult = directorySearcher.FindOne();
                    if (searchResult != null)
                    {
                        using (var userEntry = searchResult.GetDirectoryEntry())
                        {
                            return (string)userEntry.Properties["distinguishedName"].Value;
                        }
                    }
                }
            }
            return null;
        }

    }
}

Click here to grab the standalone, ready made tool & appropriate files.

Using the tool

There are three files needed to execute the process:

• The main executable program (obviously)!
• A SQL database connection string config file (this is literally the details of your SQL server connection).
• A batch file containing the appropriate “parameters” (this includes your AD LDAP connection information and query filter information).

Clearly the tool will need to be run under a user account, that has full AD permissions.

Batch file explanation

The real key to using this process, is understanding the parameters / switches of the batch file.

The batch file is fundamentally broken into 5 arguments (parameters / switches) as explained below:

1. The first parameter is where you enter your LDAP connection: e.g. “DC=St-Stephens, DC=Local”.
2. The second parameter is where you enter the Organisational Unit (OU) of where you are going to create your groups: e.g. “OU=SBGroups”.
3. The third parameter is where your AD students are located: e*.g. “OU=Students, OU=St Stephens Users”.*
4. The forth parameter is where you can filter down, to those subjects containing a name (or names) that you wish to include in your selection; you can create multiple selections by separating each word with a “|” (pipe) symbol: e.g. “Art|Photo”.
5. The fifth parameter is where you can filter out those subjects containing a name (or names) that you wish to exclude from your selection; again, you can create multiple selections by separating each word with a “|” (pipe) symbol: e.g. “UCAS|History”.

Example

Below is an example batch file, that when run (either manually or on a task schedule) will select only those groups, where the subject name is like Art or Photo but doesn’t contain the word UCAS.

It would create new AD Groups within an existing OU called “SBGroups” on an Active Directory domain titled “St-Stephens.Local” - populating these groups with students found in an OU called “Students” under a parent OU “St Stephens Users”.

Trust me – it’s a lot easier than it reads!

C:\SCHEDULE\SBGroups\CreateADGroups.exe  "DC=St-Stephens, DC=Local" "OU=SBGroups" "OU=Students, OU=St Stephens Users" "Art|Photo" "UCAS"

Conclusion

Clearly, this is just one solution to a problem, and may not fit every situation.  But under the banner of “openness and sharing” you may just find something useful in this post.

It’s worth noting, that we have also experienced quite a nice side benefit from using this system, in that students and staff are much more likely to inform you of teaching group changes, when their access to network resources are involved!

The entire project is available on my GitHub account here; please fee free to download it, fork it, adjust it or completely ignore it!

You may also choose to compose a different query, to create different group types?

It also goes without saying, that you use any of this code, entirely at your own risk!