Creating an ADO Query into Active Directory: Step-by-Step Exercises

In this exercise, we will explore the use of various queries against Active Directory. We will use both simple and compound query filters as we return data, beginning with the generic and moving on to the more specific.

1. Launch the  CreateMultipleUsers.ps1 script from the scripts folder for this chapter. This script will create 60 users with city locations from three different cities, and four different departments. We will use the different users and departments and cities in our Active Directory queries. By default, the script will create the users in the MyTestOU in the NwTraders.msft domain. If your Active Directory configuration is different, then edit the Active Directory Service Interfaces (ADSI) connection string shown here. If you are unsure of how to do this, refer back to Chapter 7, “Working with Active Directory.”

    $objADSI = [ADSI]"LDAP://ou=myTestOU,dc=nwtraders,dc=msft"

2. Open Notepad or another Windows PowerShell script editor.

3. On the first line, declare a variable called $strBase. This variable will be used to hold the base for our LDAP syntax query into Active Directory. The string will use angle brackets at the beginning and the end of the string. We will be connecting to the MyTestOU in the NwTraders.msft domain. The line of code that does this is shown here:

    $strBase = "<LDAP://ou=mytestOU,dc=nwtraders,dc=msft>"

4. On the next line, declare a variable called $strFilter. This variable will hold the string that will be used for the query filter. It will filter out every object that is not a User object. The line of code that does this is shown here:

    $strFilter = "(objectCategory=User)"

5. Create a variable called $strAttributes. This variable will hold the attribute we wish to retrieve from Active Directory. For this lab, we only want the name of the object. This line of code is shown here:

    $strAttributes = "name"

6. On the next line, we need to declare a variable called $strScope that will hold the search scope parameter. For this exercise, we will use the subtree parameter. This line of code is shown here:

    $strScope = "subtree"

7. On the next line, we put the four variables together to form our query string for the ADO query into Active Directory. Hold the completed string in a variable called $strQuery. Inside quotes, separate each of the four variables by a semicolon, which is used by the LDAP dialect to distinguish the four parts of the LDAP dialect query. The line of code to do this is shown here:

    $strQuery = "$strBase;$strFilter;$strAttributes;$strScope"

8. Create a variable called $objConnection. The $objConnection variable will be used to hold an ADODB.Connection COM object. To create the object, use the New-Object cmdlet. This line of code is shown here:

    $objConnection = New-Object -comObject "ADODB.Connection"

9. Create a variable called $objCommand that will be used to hold a new instance of the COM object “ADODB.Command”. The code to do this is shown here:

    $objCommand = New-Object -comObject "ADODB.Command"

10. Open the Connection object by calling the Open method. Supply the name of the provider to use while opening the connection. For this lab, we will use the AdsDSOObject provider. The line of code that does is shown here:

     $objConnection.Open("Provider=ADsDSOObject")

11. Now we need to associate the Connection object we just opened with the ActiveConnection property of the Command object. To do this, simply supply the Connection object contained in the $objConnection variable to the ActiveConnection property of the Command object. The code that does this is shown here:

     $objCommand.ActiveConnection = $objConnection

12. Now we need to supply the text for the Command object. To do this, we will use the query contained in the variable $strQuery and assign it to the CommandText property of the Command object held in the $objCommand variable. The code that does this is shown here:

     $objCommand.CommandText = $strQuery

13. It is time to execute the query. To do this, call the Execute method of the Command object. It will return a RecordSet object, so use the variable $objRecordSet to hold the RecordSet object that comes back from the query.

     $objRecordSet = $objCommand.Execute()

14. Use a do … until statement to walk through the recordset until you reach the end of file. While you are typing this, go ahead and open and close the curly brackets. This will take four lines of code, which are shown here:

     Do {  } Until ($objRecordSet.eof)

15. Inside the curly brackets, retrieve the Name attribute from the recordset by using the Item method from the Fields property. Pipeline the resulting object into a Select-Object cmdlet and retrieve only the value property. This line of code is shown here:

     $objRecordSet.Fields.item("name") |Select-Object Value

16. Call the MoveNext method to move to the next record in the RecordSet object contained in the $objRecordSet variable. This line of code is shown here:

     $objRecordSet.MoveNext()

17. After the until ($objRecordSet.eof) line of code, call the Close method from the RecordSet object to close the connection into Active Directory. This line of code is shown here:

     $objConnection.Close()

18. Save your script as yourname QueryUsersStepByStep.ps1. Run your script. You should see the name of 60 users come scrolling forth from the Windows PowerShell console. If this is not the case, compare your script with the  QueryUsersStepByStep.ps1 script. Note, in the  QueryUsersStepByStep.ps1 script, there are five $strFilter lines … only one that is not commented out. This is so you will have documentation on the next steps. When this code is working, it is time to move on to a few more steps.

19. Now we want to modify the filter so that it will only return users who are in the Charlotte location. To do this, copy the $strFilter line and paste it below the current line of code. Now, comment out the original $strFilter. We now want to use a compound query: objects in Active Directory that are of the category user, and a location attribute of Charlotte. From Chapter 7, you may recall the attribute for location is l. To make a compound query, enter the search parameter inside parentheses, inside the grouping parentheses, after the first search filter. This modified line of code is shown here:

     $strFilter = "(&(objectCategory=User)(l=charlotte))"

20. Save and run your script. Now, we want to add an additional search parameter. Copy your modified $strFilter line, and paste it beneath the line you just finished working on. Comment out the previous $strFilter line. Just after the location filter of Charlotte, add a filter for only users in Charlotte who are in the HR department. This revised line of code is shown here:

     $strFilter = "(&(objectCategory=User)(l=charlotte)(department=hr))"

21. Save and run your script. Now copy your previous $strFilter line of code, and paste it below the line you just modified. This change is easy. You want all users in Charlotte who are not in HR. To make a not query, place the exclamation mark (bang) operator inside the parentheses you wish the operator to affect. This modified line of code is shown here:

     $strFilter = "(&(objectCategory=User)(l=charlotte)(!department=hr))"

22. Save and run your script. Because this is going so well, let’s add one more parameter to our search filter. So, once again copy the $strFilter line of code you just modified, and paste it beneath the line you just finished working on. This time, we want users who are in Charlotte or Dallas and who are not in the HR department. To do this, add a l=dallas filter behind the l=charlotte filter. Put parentheses around the two locations, and then add the pipeline character (|) in front of the l=charlotte parameter. This revised line of code is shown here. Keep in mind that it is wrapped for readability, but should be on one logical line in the script.

     $strFilter = "(&(objectCategory=User)(|(l=charlotte)(l=dallas))(!department=hr))"

23. Save and run your script. In case you were getting a little confused by all the copying and pasting, here are all the $strFilter commands you have typed in this section of the step-by-step exercise:

     $strFilter = "(objectCategory=User)" #$strFilter = "(&(objectCategory=User)(l=charlotte))" #$strFilter = "(&(objectCategory=User)(l=charlotte)(department=hr))" #$strFilter = "(&(objectCategory=User)(l=charlotte)(!department=hr))" #$strFilter = "(&(objectCategory=User)(|(l=charlotte)(l=dallas))(!department=hr))"

24. This concludes this step-by-step exercise.