Do Over: SID History One-Liner

Wouldn’t you know it.  The very same day I posted the SID history script I found a shorter way to do it.  I'm calling a "do over".

I love the email tip-of-the-day.  On that particular day the tip was how to use the ExpandProperty switch on the Select-Object cmdlet.  This was new for me, and exactly what I needed.  Let me explain.

When you display a list of object properties at the PowerShell console sometimes one of those properties may be what we call “multi-valued”.  That means the property contains multiple entries in an array or object collection.  A good example of this is Get-PSProvider.  The Drives column usually returns multiple values in a single row, like the drives for the Registry {HKLM, HKCU}.  In order to expand those multiple values into single rows we pipe the output to Select-Object -ExpandProperty like this:

Get-PSProvider | Select-Object -ExpandProperty Drives

This is very handy for Active Directory, because several user attributes are multi-value properties (ie. postalAddress, sIDHistory, description, userCertificate, etc.).  By expanding these properties in our query we can get one row for each SID history value that is present, making the result set more friendly for exporting and reporting.  This eliminates the need to loop through the values and populate a custom object as I did in the first version of the script.

That brings us to the revised SID history script.  Because we’re going to format the output into two separate files we’ll do the AD query only once and capture it into a variable.  This is obviously faster than doing the query twice and piping it out twice.  The one-liner takes the query, expands the SID history property, selects and names the columns, and then exports it to CSV.  I have put carriage returns after each pipe simply to make it more readable.

I love PowerShell!


Ashley McGlone, Microsoft PFE
May, 2011

This script queries Active Directory for SID history in order to build a SID
mapping file for use with the ADMT to do security translation, especially in
situations where the ADMT database has been lost.  In addition to the mapping
file it also generates a full SID history report for viewing in Excel.

This script must be run from a machine that has the Active Directory module
for PowerShell installed (ie. Windows 7 with RSAT or Windows Server 2008 R2).
You must also have either a Windows Server 2008 R2 domain controller, or an
older domain controller with the Active Directory Management Gateway Service
(AD Web Service) installed.  For more information on ADWS see:
Import-Module ActiveDirectory            
#Query SID history, current SID, and related fields from AD            
$ADQuery = Get-ADObject -LDAPFilter "(sIDHistory=*)" -Property objectClass, `
    samAccountName, DisplayName, objectSid, sIDHistory, distinguishedname            
#Create a full SID History report file for reference in Excel            
$ADQuery |            
Select-Object * -ExpandProperty sIDHistory |            
Select-Object objectClass, @{name="OldSID";expression={$_.Value}}, `
    @{name="NewSID";expression={$_.objectSID}}, samAccountName, DisplayName, `
    DistinguishedName |            
Export-CSV SIDReport.csv -NoTypeInformation            
#Create a SID Mapping text file for use with ADMT            
Get-ADObject -LDAPFilter "(sIDHistory=*)" -Property objectSID, sIDHistory |            
Select-Object * -ExpandProperty sIDHistory |            
Select-Object @{name="OldSID";expression={$_.Value}}, `
    @{name="NewSID";expression={$_.objectSID}} |            
Export-CSV SIDMap0.csv -NoTypeInformation            
#Peel out the quotes from the mapping file, because ADMT does not like those.            
Get-Content .\SIDMap0.csv | ForEach-Object {$_.Replace("`"","")} |            
    Set-Content .\SIDMap.csv            
Remove-Item .\SIDMap0.csv            
"Output complete:"            
"SIDReport.csv  - full SID History report for reference in Excel"            
"SIDMap.csv     - file for use with ADMT to do security translation"            
#   ><>