Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Searching for Empty Attribute Values

Microsoft® Windows® 2000 Scripting Guide

Determining whether an attribute does not have a value is a common searching task. For example, company policy might dictate that each user account must contain an office telephone number or an e-mail address. Thus, searching for all user accounts that are missing these values will help you determine which accounts are not in compliance with company policy.

Scripting Steps

Two scripting approaches for finding attributes that do not contain a value are as follows:

  • Using the not present operator of a search filter (!attribute_name=*) to test for the absence of an attribute

  • Using the VBScript IsNull function to test for the absence of an attribute

Using the Not Present operator of a search filter

Listing 7.28 contains a script that uses a filter to find all user accounts in the forest that do not contain a value for an attribute. To carry out this task, the script performs the following steps:

  1. Create an ADO Connection object to access the Active Directory database by using the ADSI OLE DB provider.

  2. Create an ADO Command object, and assign the ADO connection to it.

  3. Assign the query string to the CommandText property of the ADO Command object. The string uses LDAP search dialect.

    Line 8 specifies the search base using the GC moniker to query the global catalog server in the Active Directory root domain, fabrikam.com, because the mail attribute in the search filter and the distinguishedName attribute are replicated to the global catalog.

    Line 9 specifies the search filter for the query. The filter, which uses the objectCategory property, limits the query to user account types, including contact accounts. The mail filter limits the query to all user account types whose mail attribute does not contain a value.

    Line 10 specifies the attribute of the objects to return, the distinguishedName attribute, and the scope of the search.

  4. Run the query by assigning the Execute method to the Command object and storing the return value in the RecordSet object, objRecordSet.

  5. Use an If Then Else statement to determine whether the recordset is empty by checking the EOF (end of file) property of the RecordSet object. If EOF is true, display a message stating that all user accounts contain a value for the mail attribute; otherwise, display each record that does not contain a value for the mail attribute.

  6. To display the records, use a While Wend statement to loop through all of the records in the RecordSet object. For each record, display the distinguishedName values stored in the Fields collection of the RecordSet object.

  7. Move to the next record in the recordset by using the MoveNext method of the RecordSet object. When all records are processed, end the loop.

  8. Close the Connection object.

Listing 7.28 Using the Not Present Operator to Display All User Accounts with an Empty Attribute

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = _
 "<GC://dc=fabrikam,dc=com>;" & _
 "(&(objectCategory=person)(!mail=*));" & _
 "distinguishedName;subtree"

Set objRecordSet = objCommand.Execute

If objRecordset.EOF Then
 Wscript.Echo _
 "All user accounts contain a value for the mail attribute."
Else
 Wscript.Echo "User account(s) without a mail value:"
 While Not objRecordset.EOF
 Wscript.Echo objRecordset.Fields("distinguishedName")
 objRecordset.MoveNext
 Wend
End If

objConnection.Close

Using the IsNull function to find attributes without values

You can also determine whether an attribute is empty by using the IsNull VBScript function to find the user account types that do not contain values in the mail attribute.

Listing 7.29 contains a script that uses the VBScript IsNull function to determine which user accounts in the forest do not contain a value for an attribute. To carry out this task, the script performs the following steps:

  1. Create an ADO Connection object to access the Active Directory database by using the ADSI OLE DB provider.

  2. Create an ADO Command object, and assign the ADO connection to it.

  3. Assign the query string to the CommandText property of the ADO Command object. The string uses LDAP search dialect.

    Line 9 specifies the search filter for the query. The filter using the objectCategory property limits the query to user account types, including contact accounts. However, a search filter to limit the search to user account types without a mail attribute is not specified.

    Line 10 specifies the attributes of the objects to return, the distinguishedName and mail attributes, and the scope of the search. The mail attribute is returned by the search so that the script can later test whether any value is contained in the attribute.

  4. Run the query by assigning the Execute method to the Command object and storing the return value in the RecordSet object, objRecordSet.

  5. Initialize the variable blnNoEmptyMailAttribute to True. This variable will remain true unless a single record is found that does not contain the mail attribute.

  6. Use a While Wend statement to loop through all of the records in the RecordSet object. For each record, test whether the mail attribute is empty by using the VBScript IsNull function.

  7. If a mail attribute is empty, set the blnNoEmptyMailAttribute to False and then display the distinguishedName value of the user account.

  8. Move to the next record in the recordset by using the MoveNext method of the RecordSet object. When all records are processed, end the loop.

  9. When all records are tested, use an If Then statement to test whether blnNoEmptyMailAttribute is true. If it is true, display a message stating that all user accounts contain a value for the mail attribute.

  10. Close the Connection object.

Listing 7.29 Using IsNull to Display All User Accounts with an Empty Attribute

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = _
 "<GC://dc=fabrikam,dc=com>;" & _
 "(objectCategory=person);" & _
 "distinguishedName,mail;subtree"

Set objRecordSet = objCommand.Execute
blnNoEmptyMailAttribute = True
While Not objRecordset.EOF
 If IsNull(objRecordset.Fields("mail")) Then
 blnNoEmptyMailAttribute = False
 Wscript.Echo objRecordset.Fields("distinguishedName")
 End If
 objRecordset.MoveNext
 If blnNoEmptyMailAttribute Then
 Wscript.Echo _
 "All user accounts contain a value for the mail attribute."
 End If
Wend

objConnection.Close

This approach does not require that the script use an ! (NOT operator) in the search filter. The NOT operator requires additional processing by the domain controller servicing the search request. However, because the filter does not limit the user accounts to those that do not contain a mail attribute, all user account types are returned by the query. The client must then test all of the records to determine whether the mail attribute is empty. This approach increases network traffic because the server returns all user account records, and it increases the processing requirements placed on the client computer running the script because the client computer must read each record to determine whether IsNull is true.

The scripts in Listing 7.28 and Listing 7.29 demonstrate that performance optimization is an important consideration when writing scripts to search Active Directory. For more information about optimizing scripts that search Active Directory, see "ADSI Scripting Primer" in this book.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.