So I try to teach my colleagues a bit more about Powershell from time to time. I have advised them a bit about how they can filter values to meet their requirements. Our architect wanted an export of all user accounts. When I simply exported all AD users in the domain he said that the amount of users is way too large. He asked me how this happend and where all those users are coming from? I wanted to give him an export of all enabled users in ActiveDirectory per OU. Actually I just wanted to show him the amount of users per OU. So I made a little script that does that job. However this is an excellent opportunity for someone to start working with Powershell. So I’ve written a little 101 about how they should start.
Step 1
Import the module ActiveDirectory.
IPMO ActiveDirectory
Step 2
You want to know in what OU a user is situated. Maybe the CMDlet Get-ADUser returns this value. By running the command Get-ADUser SAMACCOUNTNAME -Properties * you’ll get all the available properties. To display the methods and functions you can also use this command, in correspondence with the CMDlet Get-Member (Alias GM). You’ll have to run:
Get-ADUser SAMACCOUNTNAME -Properties * | GM
This prints all available methods,functions,properties and so forth. If you see a property, that is name something like, parent or OU you’ll be lucky. Unfortunately in Powershell 1,2 and 3 there isn’t such a property. So you have to find other ways to get the parent OU.
Step 3
Many CMDlets can be used against the registry, file systems, certificate stores or the Active Directory. In this case you’ll still have to know the containing OU. So probably the best way is to use the CanonicalName which contains this information. However that would kill all the fun. In this example DistinguishedName will have to do it. This obviously displays the containing OU. To save the DistinguishedName in a string run:
$Result = (Get-ADUser SAMACCOUNTNAME).DistinguishedName
Step 4
The DistinguishedName contains the CN of a user. You want to filter that out of the string. But how do you do that? Again use GM to see if you can convert the value to it’s parent or the get what is possible with the value. To do so run the command:
$Result | GM
You can replace the CN as text. This can be really really tricky. To do so run the command in step 3 without the .DistinguishedName property behind it. Next run the command:
$Result.DistinguishedName.Replace(("CN="+$Result.Name+","),'')
There is no right or wrong if the result is the same and it meets your goals. That is an important lesson to learn.
Step 5
To select an item use the CMDlet Get-Item (Alias GI). This CMDlet will let you select any file, folder or object in the registry,file system, certificate store or AD. Actually you can use it against any mounted PSdrive. We still want to know the containing OU. So in this case we will look up the DistinguishedName in the AD. Again use GM to find out what is possible with the output. To do so run:
GI "AD:\$Result" | GM
Step 6
As you can see the property PSParentPath exists. Maybe this displays the required output? Run the command:
(GI AD:\$Result).PSParentPath
Step 7
That output is not how you want it to be. The type is displayed. To be as clean as possible you will have to replace that part of the line. You can use the Replace method like this:
(GI AD:\$Result).PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')
Step 8
To come back to the objective, we’ll have to export all enabled users. There are two ways to accomplish this.
$List = Get-ADUser -Filter * | ? {$_.Enabled -eq $True}
Or
$List = Get-ADUser -Filter {Enabled -eq $True}
Which one is better? The correct answer would be none. The result is what counts. The best thing is running the command that you can remember or that works best for you. In terms of performance the second command would be better. Try this:
(Measure-Command -Expression {$list = Get-ADUser -Filter * | ?{$_.Enabled -eq $True}}).TotalSeconds
(Measure-Command -Expression {$list = Get-ADUser -Filter {Enabled -eq $true}}).TotalSeconds
Step 9
Next you can form a “script” to collect all DistinguishedNames and replace the unwanted text. To do so run:
Foreach ($item in $List){$Result = (GI AD:\$item).PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')
AC -Value $Result -Path ".\Output.csv"}
Step 10
We have a list called Output.csv. The only thing we have to do now is to count the number of occurrences. This is not necessary the best way to do it. You can save this output in an array if you like. Below there are examples of many different ways to solve this problem. Below is an example of the last bit of a script.
$List2 = GC ".\Output.csv"
$Uniquelist = $list2 | Sort -Unique
Foreach ($UniqueItem in $Uniquelist){
[int]$Count = 0
Foreach ($Item in $list2){If ($Item -eq $UniqueItem){$Count++}}
$Outinfo = "$UniqueItem" + ";" + "$Count"
AC -Value $outinfo -Path ".\Counted OUs.csv"
}
There are lots of ways to get to your destination. I have generated a number of scripts to do this task.
Script 1
IPMO ActiveDirectory
$list = Get-ADUser -Filter * | ? {$_.Enabled -eq $True}
Foreach ($item in $List){$Result = (GI AD:\$item).psparentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')
AC -Value $Result -Path ".\Output.csv"}
$List2 = GC ".\Output.csv"
$Uniquelist = $list2 | Sort -Unique
Foreach ($UniqueItem in $Uniquelist){
[int]$Count = 0
Foreach ($Item in $list2){If ($Item -eq $UniqueItem){$Count++}}
$Outinfo = "$UniqueItem" + ";" + "$Count"
AC -Value $outinfo -Path ".\Counted OUs.csv"
}
This script generated two output files. A part of statics values is replaced.
Script 2
IPMO ActiveDirectory
$list = Get-ADUser -Filter * | ? {$_.Enabled -eq $True}
Foreach ($item in $list){$Result = $item.distinguishedName.Replace(("CN="+$item.Name+","),'')
AC -Value $Result -Path ".\Output.csv"}
$List2 = GC ".\Output.csv"
$Uniquelist = $list2 | Sort -Unique
Foreach ($UniqueItem in $Uniquelist){
[int]$Count = 0
Foreach ($Item in $list2){If ($Item -eq $UniqueItem){$Count++}}
$Outinfo = "$UniqueItem" + ";" + "$Count"
AC -Value $outinfo -Path ".\Counted OUs.csv"
}
This script contains a replace of dynamic content. You have to be very careful using such a replace. We have CN’s with a value like Berg, Steven van den. That comma is precisely the problem. To display a comma in the DistinguishedName a break is inserted. So the actual value is CN=Berg\, Steven van den,. Based on the specified condition this does not apply. Meaning that you will have inconsistent results. In script 3 this is corrected.
Script 3
IPMO ActiveDirectory
$list = Get-ADUser -Filter {Enabled -eq $True}
$List2 = @()
Foreach ($item in $list){$Result = $item.distinguishedName.Replace('\','').Replace(("CN="+$item.Name+","),'')
$List2 += $Result}
$Uniquelist = $list2 | Sort -Unique
Foreach ($UniqueItem in $Uniquelist){
[int]$Count = 0
Foreach ($Item in $list2){If ($Item -eq $UniqueItem){$Count++}}
$Outinfo = "$UniqueItem" + ";" + "$Count"
AC -Value $outinfo -Path ".\Counted OUs.csv"
}
Here the conditions are met. I have also included an empty array at the line 3. Next all values are added to that array. So only the correct file is exported. Be aware that is you do a double ” after the replace of the special character you’ll need a double backslash. In this case something like .Replace(“\\”,””). The backslash means a break stating a special character.
Even though this script delivers the correct output, I would still advise you to refrain from replacing dynamic content unless you are really sure that every condition is met and that you not filter out too much.
Script 4
IPMO ActiveDirectory
$list = (Get-ADUser -Filter {Enabled -eq $True}).DistinguishedName | %{(GI "AD:\$_").PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')}
$Uniquelist = $list | Sort -Unique
Foreach ($UniqueItem in $Uniquelist){
[int]$Count = 0
Foreach ($Item in $list2){If ($Item -eq $UniqueItem){$Count++}}
$Outinfo = "$UniqueItem" + ";" + "$Count"
AC -Value $outinfo -Path ".\Counted OUs.csv"
}
Script 1 uses the CMDlet Get-Item (Alias GI) to collect the parent OU. In script 1 this is done in separate commands. You can combine this in one line let the example above. I have done this by using the CMDlet Foreach-Object (Alias %).
Script 5
IPMO ActiveDirectory
$list = (Get-ADUser -Filter {Enabled -eq $True}).DistinguishedName | %{(GI "AD:\$_").PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')}
Foreach ($UniqueItem in ($List | Sort -Unique)){
[int]$Count = 0
Foreach ($Item in $list){If ($Item -eq $UniqueItem){$Count++}}
$Outinfo = "$UniqueItem" + ";" + "$Count"
AC -Value $outinfo -Path ".\Counted OUs.csv"
}
Unlike script 4 in shorted the script even further by not generation a separate unique item list.
Script 6
IPMO ActiveDirectory
$list = (Get-ADUser -Filter {Enabled -eq $True}).DistinguishedName | %{(GI "AD:\$_").PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')}
Foreach ($UniqueItem in ($List | Sort -Unique)){
[int]$Count = 0
Foreach ($Item in $list){If ($Item -eq $UniqueItem){$Count++}}
AC -Value ("$UniqueItem" + ";" + "$Count") -Path ".\Counted OUs.csv"
}
The output string is combined in one line.
Script 7
IPMO ActiveDirectory
Get-ADUser -Filter {Enabled -eq $True} | % {($_).distinguishedName.Replace('\','').Replace(("CN="+$_.Name+","),'')} | Group | Select Name,Count | epcsv ".\Counted OUs.csv" -Delimiter ";" -NoTypeInformation
This example is even prettier. Nearly a oneliner. To count we use the Group-Object (Alias Group) te count the number of occurrences. Next we Select the fields we want. Followed by exporting the data by Export-CSV (Alias epcsv). This can be done with that other command aswel.
Script 8
IPMO ActiveDirectory
(Get-ADUser -Filter {Enabled -eq $True}).DistinguishedName | %{(GI "AD:\$_").PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')} | Group | Select Name,Count | epcsv ".\Counted OUs.csv" -Delimiter ";" -NoTypeInformation
In this case you have few ways to select the data. Like these:
IPMO ActiveDirectory
Get-ADUser -Filter {Enabled -eq $True} | %{(GI AD:\$_).PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')} | Group | Select Name,Count | epcsv ".\Counted OUs.csv" -Delimiter ";" -NoTypeInformation
Or
IPMO ActiveDirectory
Get-ADUser -Filter {Enabled -eq $True} | %{(GI AD:\$_).PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')} | Group | % { AC -Value ("$($_.Name)" + ";" + "$($_.Count)") -Path ".\Counted OUs.csv"}
Or
IPMO ActiveDirectory
Get-ADUser -Filter {Enabled -eq $True} | %{(GI AD:\$_).PSParentPath.Replace("Microsoft.ActiveDirectory.Management\ActiveDirectory:://RootDSE/",'')} | Group | % { AC -Value ($($_.Name) + ";" + ($($_).Group.Count)) -Path ".\Counted OUs.csv"}
Except one, these scripts all generate the same output. Know that there are many more ways to get the same output. Again the best method is the one you can remember and you feel comfortable using. If you like oneliners you’ll prefer script 8. If you like to have an overview of what is happening you’ll prefer script 1. Based on your skills in scripting/Powershell you’ll choose anything in between.
You could also have selected CanonicalName. This would have been a lot easier as it doesn’t contain any breaking (\). Another advantage would be that you can sort alphabetically. Since DistinguishedName is formed from the deepest to the highest level is it only possible to sort based on the last OU. Normally you would want to have this export correspond with the hierarchy of Active Directory. This is only possible with CanonicalName. CanonicalName is built up from highest in the hierarchy to the lowest container.
Reference table
| Alias |
CMDlet |
| IPMO |
Import-Module |
| GM |
Get-Member |
| GI |
Get-Item |
| ? |
Where-Object |
| AC |
Add-Content |
| GC |
Get-Content |
| Sort |
Sort-Object |
| % |
Foreach-Object |
| Group |
Group-Object |
| Select |
Select-Object |
| epcsv |
Export-CSV |
I hope you’ve had fun!
