Group Populator for MIM – Update

The Group Populator as originally detailed here – Group Populator for FIM – was a University developed method for providing automatic group population in MIM (Microsoft Idenity Manager) which subsequently provisioned out to our chosen directory service. This method used SQL views based on attributes in the MIM metaverse to create consistent group names and group to member mappings.

This method has served us well over the years but we have recently outgrown it with an ever-increasing number of objects to sync. The MA (Management Agent) as it stands takes around 48 hours to complete a full sync, and the majority of this time is spent by MIM iterating over groups and members using various MIM stored procedures for which we can find no fix.

As we felt comfortable within the Microsoft ecosystem we explored the idea of using PowerShell in combination with our existing SQL views to write directly to our directory service (in this case Active Directory). In turn, MIM would import this data in to the metaverse and push out to other directory services. We ended up with the “Group Populator for MIM – with PowerShell”. This new method reduces a full sync time down from 48 hours to around 50 minutes, enabling us to run it nightly. The extra logging and built in email alerts allow for must easier monitoring when things go wrong.

There are several things we require for the new Group Populator to work which we will summarise here but detail further below:

SQL tables
- Snapshot table – to be truncated and inserted in to from the Master view after a full or delta sync.
SQL views
- Master view – contains a member to group mapping for all groups and users.
- Delta add view – contains a member to group mapping of users that are not in the current snapshot of member to group mappings, but should be in a group based on the Master SQL view.
- Delta remove view – contains a member to group mapping of users that are in the current snapshot of member to group mappings, but should not be in a group based on the Master SQL views.
PowerShell and PowerShell script (below)
MIM with existing group flow
Active Directory
SMTP server

SQL Tables

Snapshot table

Firstly we need to create a snapshot table that our Delta views will be able to reference, and in to which we can insert a fresh snapshot from our Master view once the script has finished.

USE [Database];
CREATE TABLE [dbo].[gp_master_ps_snapshot](
[object] [nvarchar](459) NULL,
[sAMAccountName] [nvarchar](448) NOT NULL
) ON [PRIMARY]

SQL Views

Master view

It may be that you create multiple SQL views to use with the Group Populator, however the end goal is to have one “Master” view containing unions of all your other views. In our own Group Populator we have multiple views based on attributes from the MIM metaverse table fimsynchronizationservice.dbo.mms_metaverse.

In the example below we have a new view based on jobTitle.

USE [Database];
CREATE VIEW [dbo].[gp_jedi_masters]
AS
SELECT 'all-jedi-masters' AS [group], mv.sAMAccountName
FROM fimsynchronizationservice.dbo.mms_metaverse mv WITH (nolock)
WHERE jobTitle = 'Jedi'

The view gives the following results:

We can then union in any other views to create our Master view which will give us all groups and all members of those groups.

USE [Database];
CREATE VIEW [dbo].[gp_master_ps]
AS
SELECT [group], sAMAccountName
FROM [Database].dbo.gp_jedi_masters
UNION
...

Delta add view

Now we can create our Delta add view. This will be used by the GroupPopulator script to pick up any additions when delta syncs are run. The view is grabbing all results from the Master view and comparing to the snapshot. If there are additional members in Master, the script will add them to the corresponding AD group.

USE [Database];
CREATE VIEW [dbo].[gp_delta_ps_add] as
SELECT * from [Database].[dbo].[gp_master_ps]
EXCEPT
SELECT * from [Database].[dbo].[gp_master_ps_snapshot]

Delta remove view

Next we have our Delta remove view. This will be used by the GroupPopulator script to pick up any removals when delta syncs are run. The view is grabbing all results from the snapshot table and comparing to the Master view. If there are additional members in the snapshot table, the script will remove them from the corresponding AD group.

USE [Database];
CREATE VIEW [dbo].[gp_delta_ps_remove] as
SELECT * from [Database].[dbo].[gp_master_ps_snapshot]
EXCEPT
SELECT * from [Database].[dbo].[gp_master_ps]

PowerShell

Group Populator script

Example output

You can find the actual script further down in the article. Here is how it works:

Functions

There are 7 functions inside the script in total.

Create-LockFile – creates a lock file whilst the script runs to prevent multiple instances.
Delete-LockFile – deletes the lock file once the script is complete.
Write-Log – called whenever required to log out to the log file. Different logging levels are available.
Create-NewGroups – grabs existing groups in AD from defined CN variable and gets all groups that should exist from the gp_master_ps view. Any that do not exist are created in AD.
Run-FullSync – grabs all groups and members from gp_master_ps and then compares to the corresponding group in AD. If they are in gp_master_ps and not in AD then add to AD group. If they are in the AD group but not in gp_master_ps then remove from AD group.
Run-DeltaSync – performs the same function as Run-FullSync except it uses the Delta add and Delta remove views to only process changes since the last snapshot.
Take-Snapshot – truncates table gp_master_ps_snapshot and inserts a fresh set of data from gp_master_ps.

Running the script

Before running the script it’s important that the variables are amended in the #SET VARIABLES section with appropriate lock file location, log file path, CN, DB and SMTP details.

By default, running the script with no parameters performs a Delta sync unless it is 01:00AM at which point it does a full sync. This logic can be found in the #MAIN section towards the end of the script where the timings can be altered (we have once instance of it set to run every 10 minutes which covers both full and delta syncs)

The script itself accepts two parameters – TestRun and FullSync.

TestRun – runs the script but does not make any changes but writes to the log what it would have done.
FullSync – performs an adhoc full sync.

Examples:

GroupPopulator.ps1 -FullSync

GroupPopulator.ps1 -TestRun

IMPORTANT – if you are moving from the existing group populator to this and you have not deleted the old MA from MIM, you will need to ensure flow precedence is set correctly so that AD becomes the master of data or they will fight very badly with each other.

#Requires -Modules ActiveDirectory

[CmdletBinding(SupportsShouldProcess = $true)]
Param(
[switch]$TestRun,
[switch]$FullSync
)

#SET VARIABLES

$ErrorActionPreference = "Stop"
if ($TestRun) { $Test = "Test" } 
$lock_file_path = "E:\GP3\Scripts\"
$lock_file_name = "GroupPopulator$($Test).lock"
$lock_file = $lock_file_path + $lock_file_name
$LogFilePath = "E:\Logs\GroupPopulator$($Test)-$(Get-Date -format 'yyyyMMdd').log"
$CN = "OU=Groups,OU=MIMManaged,DC=test,DC=example,DC=com"
$DB = "Database"
$mailsmtpserver = "smtp.example.com"
$mailsender = "$env:computername Server <$env:[email protected]>"
$mailrecipient = @('Person1 <[email protected]>','Person2 <[email protected]>')


#FUNCTIONS
Function Create-LockFile {
<#
.Description
The Create-LockFile function creates a lock file so that it should only have one instance running at any one time
#>
while (Test-Path $lock_file) {
Write-Log "$(Get-Date -format 'HH:mm:ss') - Lock file already present, waiting..."
Start-Sleep -Seconds 10 
}
New-Item -Path $lock_file_path -Name $lock_file_name -ItemType File | Out-null
if (Test-Path $lock_file) {
Write-Log "$(Get-Date -format 'HH:mm:ss') - Lock file created"
} else {
Write-Log "$(Get-Date -format 'HH:mm:ss') - Problem creating lock file" -DebugLevel "Error"
}
}

Function Delete-LockFile {
<#
.Description
The Delete-LockFile function deletes the lock files
#>
if (Test-Path $lock_file) {
Remove-Item $lock_file
if (-not(Test-Path $lock_file)) 
{
Write-Log "$(Get-Date -format 'HH:mm:ss') - Lock file Deleted"
} else {
Write-Log "$(Get-Date -format 'HH:mm:ss') - Problem deleting lock file" -DebugLevel "Error"
}
}
}

Function Write-Log {
<#
.Description
The Write-Log function writes all requests to a log file but also displays a message on the screen for the user
#>
[CmdletBinding()]
param(
[string]$Message,
[String]$Path,
[ValidateSet("Debug","Error","Normal","Verbose")]
[String]$DebugLevel = "Normal"
)
Out-File -InputObject "$(Get-Date -format 'HH:mm:ss') - $Message" -FilePath $Path -Append
If ($DebugLevel -eq "Debug") {
Write-Debug "$(Get-Date -format 'HH:mm:ss') - $Message"
} elseIf ($DebugLevel -eq "Error") {
Send-MailMessage -From "$mailsender" -To $mailrecipient -Subject "Group Populator - ERROR" -Body "$(Get-Date -format 'HH:mm:ss') - $Message" -SmtpServer "$mailsmtpserver"
Delete-LockFile
Write-Error "$(Get-Date -format 'HH:mm:ss') - $Message"
} elseIf ($DebugLevel -eq "Normal") {
Write-Host "$(Get-Date -format 'HH:mm:ss') - $Message"
} elseIf ($DebugLevel -eq "Verbose") {
Write-Verbose "$(Get-Date -format 'HH:mm:ss') - $Message"
}
}
$PSDefaultParameterValues['Write-Log:Path'] = $LogFilePath

Function Create-NewGroups {
<#
.Description
The Create-NewGroups function compares required groups to what's in AD and then creates any missing AD groups
#>
Write-Log "--------------------------------------------------GROUP CREATION START--------------------------------------------------"
try {
#Get existing groups from MIM and AD
$DepartmentGroupList = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT distinct object FROM [$($DB)].[dbo].[gp_master_ps] order by object asc" -QueryTimeout 0
$ADGroupList = (Get-ADGroup -Filter * -searchbase "$CN" -Properties *).Name
If ($ADGroupList.Count -eq 0 -or $DepartmentGroupList.Count -eq 0) {
Write-Log "Failed to retrieve groups from SQL or AD" -DebugLevel "Error"
break
}

#Get departments in MIM without an AD group and create
ForEach ($group in ($DepartmentGroupList | Sort-Object)) {
If ($group.object -notin $ADGroupList) {
try {
Get-ADGroup -Identity $group.object | Out-Null
}
catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
Write-Log "$($group.object) flagged for creation"
try {
if (!$TestRun) {
New-ADGroup -Name $group.object -SamAccountName $group.object -GroupCategory Security -GroupScope Global -Path "$CN" 
Write-Log "Created group $($group.object) in AD"
} else {
Write-Log "Would of created group $($group.object) in AD"
}

}
catch {
Write-Log "Failed to create $($group.object)`r`n$(Get-Date -format 'HH:mm:ss') - $($_)" -DebugLevel "Error"
break
}
}
catch{
Write-Log "Failed to query AD to create $($group.object)`r`n$(Get-Date -format 'HH:mm:ss') - $($_)" -DebugLevel "Error"
break
}
}
}
Write-Log "Group creation successfully finished"
}
catch {
Write-Log "Failed with group creation`r`n$(Get-Date -format 'HH:mm:ss') - $($_)" -DebugLevel "Error"
}
Write-Log "---------------------------------------------------GROUP CREATION END---------------------------------------------------"
}

Function Run-FullSync {
<#
.Description
The Run-FullSync function compares the full dataset against what's in AD
#>
Write-Log "----------------------------------------------------FULL SYNC START-----------------------------------------------------"
try {
#Populate groups with new members
$DepartmentGroupList = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT distinct object FROM [$($DB)].[dbo].[gp_master_ps] order by object asc"
If ($DepartmentGroupList.Count -eq 0) {
Write-Log "Failed to retrieve groups from SQL" -DebugLevel "Error"
break
}
ForEach ($group in $DepartmentGroupList) {
$DepartmentGroupMemberList = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT object, samaccountname FROM [$($DB)].[dbo].[gp_master_ps] where [object] = '$($group.object)' order by object asc, samaccountname asc" -QueryTimeout 0
If ($DepartmentGroupMemberList.Count -eq 0) {
Write-Log "Failed to retrieve members from SQL" -DebugLevel "Error"
break
}
Write-Log "Fetching members of $($group.object)"
$ADGroupMembers = Get-ADGroup $group.object -Properties Member | Select-Object -Expand Member | Get-ADUser | select samaccountname
$DepartmentGroupMemberList = @{samaccountname = $($DepartmentGroupMemberList.samaccountname)}
If ($ADGroupMembers.samaccountname.Count -gt 0) {
$ADGroupMembers = @{samaccountname = $($ADGroupMembers.samaccountname)}
} else {
$ADGroupMembers = @{}
}
ForEach ($member in $DepartmentGroupMemberList.samaccountname) {
If ($ADGroupMembers.Count -eq 0) {
try {
Get-ADUser -Identity $member | Out-Null 
if (!$TestRun) {
Add-ADGroupMember -Identity $group.object -Members $member 
Write-Log "Added $($member) to $($group.object)"
} else {
Write-Log "Would have added $($member) to $($group.object)"
}
} catch {
Write-Log "$($member) does not exist in AD for $($group.object)"
}
}
Else {
If (! $ADGroupMembers.samaccountname.Contains($member)) {
try {
Get-ADUser -Identity $member | Out-Null 
if (!$TestRun) {
Add-ADGroupMember -Identity $group.object -Members $member 
Write-Log "Added $($member) to $($group.object)"
} else {
Write-Log "Would have added $($member) to $($group.object)"
}
} catch {
Write-Log "$($member) does not exist in AD for $($group.object)"
}
}
}
}
ForEach ($member in $ADGroupMembers.samaccountname) {
If (! $DepartmentGroupMemberList.samaccountname.Contains($member)) {
if (!$TestRun) {
Remove-ADGroupMember -Identity $group.object -Members $member -Confirm:$false
Write-Log "Removed $($member) from $($group.object)"
} else {
Write-Log "Would have removed $($member) from $($group.object)"
}
}
}
}
Write-Log "Full Sync successfully finished"
}
catch {
Write-Log "Failed running full sync`r`n$(Get-Date -format 'HH:mm:ss') - $($_)" -DebugLevel "Error"
break
}
Write-Log "-----------------------------------------------------FULL SYNC END-------------------------------------------------------"
}

Function Run-DeltaSync {
<#
.Description
The Run-DeltaSync function only looks for delta changes and makes them within AD
#>
Write-Log "----------------------------------------------------DELTA SYNC START----------------------------------------------------"
try {
$DepartmentGroupListDeltaAdd = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT distinct object FROM [$($DB)].[dbo].[gp_delta_ps_add] order by object asc" -QueryTimeout 0

#Populate groups with new members
ForEach ($group in $DepartmentGroupListDeltaAdd) {
$DepartmentGroupMemberListDelta = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT object, samaccountname FROM [$($DB)].[dbo].[gp_delta_ps_add] where [object] = '$($group.object)' order by object asc, samaccountname asc" -QueryTimeout 0
ForEach ($member in $DepartmentGroupMemberListDelta) {
try {
Get-ADUser -Identity $member.samaccountname | Out-Null 
if (!$TestRun) {
Add-ADGroupMember -Identity $group.object -Members $member.samaccountname 
Write-Log "Added $($member.samaccountname) to $($group.object)"
} else {
Write-Log "Would have added $($member.samaccountname) to $($group.object)"
}
} catch {
Write-Log "$($member) does not exist in AD for $($group.object)"
}
}
}

$DepartmentGroupListDeltaRemove = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT distinct object FROM [$($DB)].[dbo].[gp_delta_ps_remove] order by object asc" -QueryTimeout 0

#Remove invalid users from group
ForEach ($group in $DepartmentGroupListDeltaRemove) {
$DepartmentGroupMemberListDelta = Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "SELECT object, samaccountname FROM [$($DB)].[dbo].[gp_delta_ps_remove] where [object] = '$($group.object)' order by object asc, samaccountname asc" -QueryTimeout 0
ForEach ($member in $DepartmentGroupMemberListDelta) {
if (!$TestRun) {
Remove-ADGroupMember -Identity $group.object -Members $member.samaccountname -Confirm:$false
Write-Log "Removed $($member.samaccountname) from $($group.object)"
} else {
Write-Log "Would have removed $($member.samaccountname) from $($group.object)"
}
}
}
Write-Log "Delta Sync successfully finished"
}
catch {
Write-Log "Failed running delta sync`r`n$(Get-Date -format 'HH:mm:ss') - $($_)" -DebugLevel "Error"
break
}
Write-Log "-----------------------------------------------------DELTA SYNC END-----------------------------------------------------"
}

Function Take-Snapshot {
<#
.Description
The Take-Snapshot function recreates a snapshot of the dataset in the database so that delta syncs can be run
#>
Write-Log "-----------------------------------------------------SNAPSHOT START-----------------------------------------------------"
try {
Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "TRUNCATE table [$($DB)].[dbo].[gp_master_ps_snapshot]" -QueryTimeout 0
Invoke-Sqlcmd -ServerInstance localhost -Database $DB -Query "INSERT into [$($DB)].[dbo].[gp_master_ps_snapshot] (object, sAMAccountName) SELECT object, sAMAccountName from [$($DB)].[dbo].[gp_master_ps]" -QueryTimeout 0
Write-Log "Snapshot successfully taken"
}
catch {
Write-Log "Failed to take snapshot`r`n$(Get-Date -format 'HH:mm:ss') - $($_)" -DebugLevel "Error"
break
}
Write-Log "------------------------------------------------------SNAPSHOT END------------------------------------------------------"
}

#MAIN
#Logging header
Write-Log "=========================================================START=========================================================="
if ($TestRun) { Write-Log "Test Run enabled, no actions will be taken" }
Create-LockFile
If ((get-date).Hour -eq "01" -or $FullSync) { #(get-date).DayOfWeek -eq "Sunday" -and
Write-Log "Full Sync chosen"
Write-Log "Comparing and creating any new groups...."
Create-NewGroups
Write-Log "Running a Full Sync...."
Run-FullSync
Write-Log "Performing a snapshot...."
Take-Snapshot
}
Else {
Write-Log "Delta Sync chosen...."
Write-Log "Comparing and creating any new groups...."
Create-NewGroups
Write-Log "Running a Delta Sync...."
Run-DeltaSync
Write-Log "Performing a snapshot...."
Take-Snapshot
}
Delete-LockFile
#Logging footer
Write-Log "==========================================================END==========================================================="
Write-Log ""

Thanks for reading and we hope that it’s been useful.