Hey, Scripting Guy! How Can I Search Active Directory and Produce a Report?

Hey, Scripting Guy! How Can I Search Active Directory and Produce a Report?

  • Comments 1
  • Likes

Hey, Scripting Guy! Question

Hey, Scripting Guy! We are in the situation where we are closing a number of remote offices, and I need to quickly be able to generate a list of the computers that are contained in those remote offices. Basically I need to be able to generate a report that tells me the location and the name of the computer assets for each of a series of these locations. Can you help me? I do not have time to learn a lot of convoluted syntax; I need something quick and easy that I can do now.

- TC

SpacerHey, Scripting Guy! Answer

Hi TC,

Moving offices around can always be a big challenge. I remember when I was in consulting once a while back, having to help three different companies planning moves during the same time frame. I quickly became the "moving guy" at the company. There is a heck of a lot of planning that takes place, especially if you are not permitted to take an outage during the move. Since I know you don't have an awful lot of extra time let's go ahead and jump into the script.

This week we are talking about searching Active Directory. The Active Directory Script Center Hub has links to a number of resources related to working with Active Directory. You will also find in this section of the Script Center Script Repository a good collection of scripts that illustrate searching Active Directory. There are several scripts in the Community-Submitted Scripts Center that also illustrate searching Active Directory. The “Hey, Scripting Guy!” Active Directory Archive is also an excellent source of information for searching Active Directory. Not to be outdone, the Scripting Guide has an ADSI Scripting Primer.

TC, we decided to write a script called SearchComputersByLocation.ps1. This script uses an array of city names and looks through Active Directory for computers that are assigned to that location. It then writes the located computers to a text file. See also a VBScript version similar to this script.

SearchComputersByLocation.ps1

$Locations = "Charlotte","Atlanta"
$OutFile = "C:\fso\computerReport.txt"
Foreach($location in $Locations)
{
 $Filter = "(&(ObjectCategory=computer)(Location=$location))"
 $Searcher = New-Object System.DirectoryServices.DirectorySearcher($Filter)
 $Searcher.Findall() | 
 Foreach-Object `
   -Begin { "Results of $Filter query: " >> $OutFile } `
   -Process { 
                     $_.properties.item("DistinguishedName" )  >> $OutFile
                     $_.properties.item("Location")   >> $OutFile
                     "`r" >>$OutFile
                    } `
   -End { [string]$Searcher.FindAll().Count + " $Filter results were found`n"  >> $OutFile }
} #end foreach

Before I get too carried away I always like to look in Active Directory Users and Computers to see if what I am looking for even exists. As we can see in the following image, there are a number of computers in the Charlotte organizational unit (OU). Therefore, we should be able to find computers assigned to Charlotte. There are, however, other objects in there as well. This keeps us from simply querying for all the objects in the Charlotte OU.

Image of the computers in the Charlotte OU

 

The first thing we do is to create an array of city names that represent the locations whose computers we wish to gather. This is a straightforward value assignment to the $Locations variable and is seen here:

$Locations = "Charlotte","Atlanta"

We also need to specify the location and the name for the report that will be created. In this script, we assume there is a folder named fso that exists off of the C:\ drive. We do not use the Test-Path cmdlet to ensure that it exists. You would need to modify the script to provide a path that is valid on your system. The computerReport.txt file does not need to exist because it will be created the first time the script is run. On subsequent runnings, the computerReport.txt file will be appended to.

$OutFile = "C:\fso\computerReport.txt"

To walk through the array of location names, we use the ForEach statement. Inside the ForEach loop, we will use the variable $location to refer to the current item in our array. When using the ForEach statement, we use parentheses to hold the enumerator and the array name, and the action to be performed goes into a set of curly brackets. This is shown here.

ForEach($location in $Locations)
{

It is time to define our search filter. We talked about search filters on Tuesday of this week, and you can refer to that article here if you would like a refresher. The thing that is a bit unique this time is that our search filter contains the $location variable. This allows us to create the search filter programmatically. We could then populate the array of locations anyway we wish. We could even read a text file that contained a list of location names by doing something like this:

$Locations = Get-Content –Path C:\FSO\LocationNames.txt

After we have the query working by using a variable, we gain a tremendous amount of flexibility. The essential query is a compound query. We look for the value of the ObjectCategory attribute that is equal to computer, and we look for the value of the Location attribute that is equal to the one specified in the $location variable.

You may recall from previous work with Active Directory that the user object contains an attribute named l (lowercase L). The l attribute stands for location and is used to specify the city with which a user is associated. We now try the query seen here:

$Filter = "(&(ObjectCategory=computer)(l=$location))"

The results that come back from the above query are seen in the following image. They are, in a word, unimpressive.

Image of the results form the query

 

This is particularly confusing when we can plainly see there are a number of computer objects in the Charlotte OU. When confronted with these kinds of unexpected results, I always call upon my old friend (I am not talking about Luis from Lisbon, who helped me out earlier this week): ADSI edit. You can think about ADSI edit as sort of a registry editor for the entire domain.

If you have permissions to change values of the object, keep in mind that it is read/write. So be careful.

As seen in the following image, the l (lowercase L) attribute is not set. However, by looking down the list, we can see that the location attribute has a value of Charlotte.

Image of the lowercase L attribute not being set

 

Armed with the new information, we modify the way we thought our search filter would work. The modified search filter incorporates the Location attribute and is seen here.

$Filter = "(&(ObjectCategory=computer)(Location=$location))"

Now when we test the filter, we are rewarded with a listing of the computer in both Atlanta and in Charlotte. This is seen here:

Image of the computer listed in both Atlanta and Charlotte

 

To perform the query of Active Directory, we use the DirectorySearcher object. We create this by using the New-Object cmdlet and specifying the full name of the System.DirectoryServices.DirectorySearcher .NET Framework class. We give it the filter we worked out earlier as the constructor. We then store the returned object in the $Searcher variable:

$Searcher = New-Object System.DirectoryServices.DirectorySearcher($Filter)

After we have created the DirectorySearcher object we use the FindAll method to retrieve all the matches. We send the results across the pipeline:

$Searcher.FindAll() |

It is time to begin working on our reporting. We use the ForEach-Object cmdlet to allow us to process the items that come across the pipeline. The back tick (`) is used for line continuation. This is seen here:

Foreach-Object `

We would like to print out a heading for the objects that come across. The –Begin parameter allows us to do this once for all the objects. We use the double arrow (>>) to append the output to a text file whose path we specified earlier. This is seen here:

-Begin { "Results of $Filter query: " >> $OutFile } `

Now we will begin processing the objects. To do this, we use the –Process parameter. Actions defined here are done once for each object that comes across the pipeline. We obtain the DistinguishedName and the Location of each computer object that matches our search filter. We redirect the output to our text file. The double arrow means we will append to the text file and therefore we will avoid overwriting the file. This is all shown here:

-Process { 
                     $_.properties.item("DistinguishedName" )  >> $OutFile
                     $_.properties.item("Location")   >> $OutFile
                     "`r" >>$OutFile
                    } `

Now we would like to add a conclusion to our report. This line is printed out once for all the objects that come across the pipeline. Like the other output, it is appended to the text file specified in the $OutFile:

-End { [string]$Searcher.FindAll().Count + " $Filter results were found`n"  >> $OutFile }
} #end foreach

The report contained in the $OutFile is seen here:

Image of the report contained in the $OutFile

 

Well, TC, that is all there is to querying Active Directory and producing a report. This also draws to a conclusion our Searching Active Directory Week articles. Join us tomorrow for Quick-Hits Friday. Until then, take care.

 

Ed Wilson and Craig Liebendorfer, Scripting Guys

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment