AD/LDAP Shadow Group PowerShell Script

Setup User Directories in an Active Directory/LDAP Enterprise setting

This is rooted in setting up Atlassian JIRA and Confluence (Crowd based), but its useable information for many other tools using Active Directory or LDAP for User administration.

Pulling users from Active directory (AD) in an Enterprise setting is usually far from trivial, and especially when users and groups are added far away from each other and cooperation is expected.

Below is a simplified representation of an AD setup. The current setup I’m working with, have more than 20.000 OU nodes across multiple AD partitions – but thankfully, we won’t need to dive into that complexity for this explanation. :-)

To pull out all Consultants at the Aarhus node, one can use a Base DN as this:

OU=Consultants,OU=Users,OU=Accounts,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org

If we also need to include the External users associated with Aarhus, we can’t specify two base DN’s at the same time, instead we have to specify the closest common denominator, so in this case we have to specify the following Base DN:

OU=Users,OU=Accounts,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org

…this will result in the users specified in Aarhus nodes OU’s named Consultants, Externals, Services and Users. So suddenly we’re pulling potentially hundreds of users, just to include the say 10 Consultants and 5 Externals.

In my case, I first had to pull out users in Aarhus and Atlanta’s locationA, so the closest common denominator were OU=BranchA,DC=domain,DC=org, resulted in some 11.000 users and hundreds of groups.

Barely bearable, as we were now pulling in 20 times more users than needed!

But it got worse when we started cooperation with a small group of people in Xian. Suddenly the closest common dominator for the Base DN were DC=domain,DC=org, resulting in about 250.000 users and at least 15.000 groups being pulled in.

Not useable!

So what were my options when setting up the AD/LDAP importer, in order to only import the users needed?

The following five entries looked like they deserved special attention: Base DN, Additional User DN, Additional Group DN, Group Object Filter (in Group Schema Settings) and User Object Filter (in User Schema Settings)

Base DN, we already established had to be the closest common denominator to the locations we intent to import from, so it had to be set at: OU=BranchA,DC=domain,DC=org

Additional User DN, is an OU string to append to the Base DN, used to specify the starting point to where users should be imported from. Since the users I needed to import didn’t share a common location, this setting wasn’t useful at all, and it was left blank.

Additional Group DN, is an OU string appended to the Base DN, used to specify the starting point to where groups should be imported from. In my case this was very useful, as all groups with this set of users were located in only one place: OU=Development,OU=Groups,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org

Group Object Filter, this is a filter that’s applies to all groups. AD entries not matching the filter won’t be imported. The most basic setting (and default) for this is (objectCategory=Group), but during test importing, where I had thousands of groups imported, I often got import errors. It turned out had to do with the AD replication across AD partitions, which would sometimes leave duplicated group entries, and to avoid naming conflicts AD automatically assign “cnf” values to some of these groups… which the importer would choke on. To play it safe, I decided to exclude all groups with “cnf” values, resulting in this filter: (&(objectCategory=Group)(!(cn=*cnf:*)))

User Object Filter, so this was the final option to somehow filter the users… but we´re not so lucky that it can just be used to operate directly on the OU's available in the AD. The filter can be used to filter on users belonging to groups, or belonging to groups and its sub-groups. 

So in order to write this filter, we first need to create an AD Security Group, in which all the users we intend to import are members. I placed the AD group in the already imported Development OU at the Aarhus node. I chose to name this group AAR-Global-Devl-Users, resulting in this filter:

(&(samAccountType=805306368)(memberOf:=CN= AAR-Global-Devl-Users, OU=Development,OU=Groups,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org))

Note: If you intent to include sub-groups in your group, use memberOf:1.2.840.113556.1.4.1941:=CN= instead of memberOf:=CN= .

So finally, with this in place I now only import what was necessary, namely a few hundred users and a few groups, instead of some 250.000 users and at least 15.000 groups.

Synchronizing time suddenly dropped to 30 seconds, instead of hours!

The penalty to use this filter is that the users you import need to be members of a group, and this group needs to be maintained. Manually maintained that is, as Active Directory have no dynamic update methods available. L

But PowerShell to the rescue:

LDAP/Active Directory Shadow Group Script with Alternate Credentials

The PowerShell script published below, updates members in an AD Security group with users found on one or more AD OU’s.

The scripts have only four configuration options, described here:

1. The credentials for the user making the changes in the AD:

$sUser = "domain\user"
$sPass = "Pass!Word"

For security reasons, the user I’m using have read access to the whole AD, but only write access to the groups in the Development OU under the Aarhus node.

2. The IP or host name and port number to use:

$LDAP = "LDAP://HOSTNAME.OR.IP.:389"

3. Full DN of the group to maintain:

$GroupDN = " CN= AAR-Global-Devl-Users, OU=Development,OU=Groups,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org "

4. List of the OU’s to scan for users. Each OU have an option to either only scan the entry level  or recursive scan deeper levels(“onelevel” or “subtree”):

$OUs = @{  # Allowed values: "oneLevel" or "subtree" “OU=Users,OU=Accounts,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org " = "subtree":

“OU=Users,OU=Accounts,OU=LocationA,OU=Atlanta,OU=US,OU=AMR,OU=BranchA,DC=domain,DC=org " = "oneLevel":

“OU=Services,OU=Users,OU=Accounts,OU=Department,OU=LocationB,OU=Xian,OU=China,OU=ASIA,OU=BranchB,DC=domain,DC=org " = "oneLevel" :

}

The script can be saved under any name. Since its reason for life is to update one group, I suggest to give it a saying name that include the group name and clearly states its purpose, such as 

Update_Shadowgroup_AAR-Global-Devl-Users.ps1

Logfile

The script will create a log file at the same location of the script, with the same name as the script, but instead of the ps1 extension, it will use log. 

The log contains time stamped entries of when an OU was scanned, what users were added to, and removed from, the shadow group. It also contains statistics about the current number of members, and how many were added and removed during last run.

AD Group info.

At the end of the synchronization, the AD info/Notes field in the group is updated with information about the computer that ran the scrips, as well as a complete path to the script. Group member statistics is also included. 

The group's description field is also updated, to warn against manually editing the group.

Schedule the Script

I recommend using the windows scheduler to start the script at regular intervals. This can be relative problematic when using a PowerShell script, due to a pretty cryptic permission setup. 

To circumvent this, I’m using a batch script file, named Update_Shadowgroup_AAR-Global-Devl-Users.cmd, which contains the following line:

              @powershell -ExecutionPolicy ByPass -File Update_Shadowgroup_AAR-Global-Devl-Users.ps1

And now, finally, onto the script:

#=====================================================================
# Active Directory Shadow Group Script.
#
# Written by Flemming Steffensen, October 2015.
#
# Inspired by work by Richard Mueller, jrv, Bill Stewart, 
#                     Mauricio Ramalho and Eric Weintraub
#=====================================================================
# Configuration parameter section
#=====================================================================

# Credentials
$sUser = "domain\user"
$sPass = "Pass!Word"

# AD domainname/IP and port
$LDAP = "LDAP://HOSTNAME.OR.IP.:389"

# Shadow Group
$GroupDN = "CN=AAR-Global-Devl-Users,OU=Development,OU=Groups,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org"

# Hash Array of OUs.
$OUs = @{  # Allowed values: "oneLevel" or "subtree" 
 “OU=Users,OU=Accounts,OU=Aarhus,OU=Denmark,OU=EUR,OU=BranchA,DC=domain,DC=org" = "subtree"
 “OU=Users,OU=Accounts,OU=LocationA,OU=Atlanta,OU=US,OU=AMR,OU=BranchA,DC=domain,DC=org" = "oneLevel"
 “OU=Services,OU=Users,OU=Accounts,OU=Department,OU=LocationB,OU=Xian,OU=China,OU=ASIA,OU=BranchB,DC=domain,DC=org" = "oneLevel" 
}

#=====================================================================

#Define the log file and function
$ScriptPathName = $MyInvocation.MyCommand.Definition
$sLogPathName = ($ScriptPathName -replace ".ps1", ".log") # Same path and name as script, end with log.
function Log($text)
{
 Add-Content $sLogPathName ("$(Get-Date –f o) $text")
}

# Bind to the specified group - with alternate credentials!
$oGroup = New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "$LDAP/$GroupDN", $sUser, $sPass # Omit ", $sUser, $sPass" to use the script runners credentials.

# Hash table of all users in any of the OU's.
$OUList = @{ }

# Statistics counters
$CountAdded = 0
$CountRemoved = 0
$CountCurrent = 0

Log("Inf: Scan started.")

# Use the DirectorySearcher class.
$oSearcher = New-Object System.DirectoryServices.DirectorySearcher
$oSearcher.PageSize = 200
$oSearcher.PropertiesToLoad.Add("distinguishedName") > $Null
foreach ($OU in $OUs.GetEnumerator())
{
 Log("Inf: Scanned: $($OU.Value) : $($OU.Name)")
 # Filter on all users in the OU.
 $oSearcher.SearchRoot = New-Object System.DirectoryServices.DirectoryEntry "$LDAP/$($OU.Name)", $sUser, $sPass # Omit ", $sUser, $sPass" to use the script runners credentials.
 $oSearcher.SearchScope = $($OU.Value) #  "oneLevel" or "subtree" 
 $oSearcher.Filter = "(sAMAccountType=805306368)"
 $Users = $oSearcher.FindAll()
 ForEach ($User In $Users)
 {
  $UserDN = $($User.Properties.Item("distinguishedName"))
  $UserDN = $UserDN.Replace("/", "\/")
  
  # Add this user to the hash table.
  If ($OUList.ContainsKey($UserDN) -eq $False)
  {
   $OUList.Add($userDN, $True)
  }
  # Make sure each user in the OU is a member of the group.
  If ($oGroup.IsMember($User.Path))
  {
   # User is already a member of the group.
  }
  Else
  {
   # Add user to the group.
   Log ("Add: $userDN")
   $oGroup.Invoke("add", "$LDAP/$userDN")
   $CountAdded++
  }
 }
}
$oGroup.CommitChanges()
$oGroup.RefreshCache()

# Enumerate all direct members of the group.
ForEach ($Member In $oGroup.Member)
{
 $Member = $Member.Replace("/", "\/")
 If ($OUList.ContainsKey($Member) -eq $False)
 {
  # Remove any group members that are not in one of the OU's.
  Log ("Del: $Member")
  $oGroup.Invoke("remove", "$LDAP/$Member")
  $CountRemoved++
 }
 else
 {
  # Member should stay a member.  
  $CountCurrent++
 }
}

#Some statistics
$GroupName = ($GroupDN -split ',*..=')[1]  # First substring starting with = and ending with , .
$Stats = "Members: $CountCurrent  (+ $CountAdded/- $CountRemoved)"
Log ("Inf: ShadowGroup '$GroupName' $Stats")

#Update Groups info/Notes and Description.
$NewInfo = "$env:computername : $ScriptPathName finished scheduled update at $(Get-Date –f o). $Stats"
$oGroup.InvokeSet("info", $NewInfo)
$oGroup.InvokeSet("description", "Shadow Group - Do not modify manually. ")
$oGroup.CommitChanges()
$oGroup.Close()


Exit

Use SI number notation in spreadsheets

I've long been annoyed that spreadsheets (Excel and Google Docs) lack the option to format a number using SI number notation.

To get around this limitation, I've made the following three formulas.

SI-Notation numbers:

To write SI notation numbers in the range from 1E14 (T) to 1E-10 (p), the following formula will both display the rounded number and the SI-notation, as well as adding the unit as well.

Input number is in cell A3.

=ROUND(A3/CHOOSE(IF(A3>1;ROUNDDOWN(LOG(A3;10)/3;0);ROUNDUP(LOG(A3;10)/3;0))+5;10^-12;10^-9;10^-6;10^-3;1;10^3;10^6;10^9;10^12);0)&
CHOOSE(IF(A3>1;ROUNDDOWN(LOG(A3;10)/3;0);ROUNDUP(LOG(A3;10)/3;0))+5;"p";"n";"u";"m";"";"k";"M";"G";"T")&"m" 


 The rounded number 
 Number of decimals 
 The SI-notation 
 Unit 

Negative numbers will result in #NUM.
Numbers outside of the range will result in #VALUE.
If A3 contains 1568, the above formula will result in the value 2km.


For computer-memory and storage, two other formulas are missing, namely conversion from bytes to kilobytes, megabytes ect. for both base-2 and base-10.


Base-10 display uses kB, MB, TB etc. as post-fix:

This notation is primarily used by storage manufactures (although the letters used to be used for base-2 values).


Input number is in cell A4.


=ROUND(A4/CHOOSE(ROUNDDOWN(LOG(A4;10)/3;0)+1;1;10^3;10^6;10^9;10^12;10^15);1)&
CHOOSE(ROUNDDOWN(LOG(A4;10)/3;0)+1;"B";"kB";"MB";"GB";"TB";"PB") 


 The rounded number 
 Number of decimals 
 The SI-notation 



Negative numbers will result in #NUM.
Numbers outside of the range will result in #VALUE.
If A4 contains 140000, the above formula will result in the value 140.0kB.

Base-2 display uses kiB, MiB, TiB etc. as postfix:


This notation is the one primarily used by any computer system.



Input number is in cell A5.

=ROUND(A5/CHOOSE(ROUNDDOWN(LOG(A5;2)/10;0)+1;1;2^10;2^20;2^30;2^40;2^50);1)&
CHOOSE(ROUNDDOWN(LOG(A5;2)/10;0)+1;"B";"kiB";"MiB";"GiB";"TiB";"PiB")

 The rounded number 
 Number of decimals 
 The SI-notation 



Negative numbers will result in #NUM.
Numbers outside of the range will result in #VALUE.
If A5 contains 140000, the above formula will result in the value 136.7kiB.

With these formulas you should be pretty safe for any value you need represented. If not the structure of the formulas should easily allow you to modify them for your own usage.

WEEKNUM() for Google Docs & Spreadsheets

I've been using Google Docs & Spreadsheets for some time now. Mostly the spreadsheets, though. During that time I've made some relative complex spreadsheets, and I'm really pleased with them.
However there still are a few normal spreadsheet commands missing, and yesterday I was in need for exactly one of them: WEEKNUM() (as detailed in ISO 8601:2000)

What I needed was to get the number of the week in a year (1 to 53) for any specific date.
After some time hacking at the keyboard, I got the following formula to work.
The date to check is located in cell C5.

=INT((C5-DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3)+WEEKDAY(DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3))+5)/7)

The above imitates the WEEKNUM() command, implemented in most spreadsheets, except on one count:

1. It's ONLY ISO 8601:2000, so there's no option to set the type.

Very usefull for me... perhaps usefull for you, too?




Updated on Feb.23, 2012:

Also thanks to Snezy and Anonymous for the two additional attempts at this. I've tested both of the forumlas you posted in the comments, but had some problems making them work. Snezy's seems to not be able to produce one digit week numbers, and Anonymous would display "week" in the output, but based on his input, I did make this formula, which does work both in Google Spreadsheet and Excel, but since it relies on the fairly undocumented TEXT() function, it may not work on all other Spreadsheets:

=LEFT(TEXT(C5; "w d"); 2)

Pretty neat!