So what do I mean when I say Dynamic AD Groups? I look at it in a number of stages.

Based from groups selected by the business, return each groups members where specific attributes are checked and match Department, Title and Office values. Again these values are noted by the business.

Based on results collected so far, verify each members compliance.

“Does this user have Department “X” and Office “Y”?

If these values are satisfied, then the user can stay a member of the group.

However, if the values are not satisfied, the user is removed from the group.

So to achieve this we need to create a database instance or a CSV file. To get a proof of concept, and to determine what the SQL instance would even look like I opted to work with a CSV file.

At the time of writing this, I would like to use sql instead of the CSV. I imagine that this would be a stored procedure that I could call upon to return the values like I do with the CSV in this example. Stay tuned for a Part 2 when I eventually implement that.

So first we need to create a CSV file with the data we are interested in.

group,department,title,office,Finance,Payroll Officer,Brisbane,Information Technology,Systems Administrator,Sydney

Here we give four headings that we are interested in, group, department, title and office.

In this example we know that to be a member of you MUST have;

  • Department: Finance
  • Title: Payroll Officer
  • Office: Brisbane

attributes to be a member or “in compliance”.

The same goes for You must have Information Technology, System Administrator and Sydney as an attribute.

Download the following powershell script. Note: As a safety measure I have put the -Whatif parameter on each of the three remove or add users from groups. When you have tested completely feel free to remove this.

Gitea Repo

So lets see this in action…

So here is my CSV file:

To quickly note for this example the following test users where configured like below,

  • Alex does not have the office, job title or department value populated, however is a member of the group. This would make Alex’s groups non compliant.
  • Ben has Perth as his office location, System Administrator as job title and Information Technology as Department. However he has the Again this would make Ben’s groups non compliant.
  • John has a location of Brisbane, job title of Payroll Officer and a Department of Finance. But is missing the correct the group This means that John’s groups are also not compliant. But rather then being removed from the group he needs to be added!
  • Finally Sarah has a location in Sydney, job title of Systems Administrator and a department of Information Technology. Again she is missing the required group, This makes Sarah’s groups non compliant.

Here’s the results: