In-Depth

Drive Better Active Directory Data with DRS

You can improve query performance in Active Directory by using disconnected data sets.

• By James Turner
• 04/18/2013

Improving Active Directory performance is on everyone's mind. You can do so relatively easily using disconnected recordsets. A disconnected recordset (DRS) is an ADO record that resides in memory. A DRS is completely disconnected from its original data source and you can create one "on demand" with relative ease. Collect data from sources such as Active Directory and gather useful information by issuing simple SQL-like queries.

The utilities referenced here are not complex, and they're not relational databases. They're databases consisting of common collections of data you generally have to evaluate on a daily or weekly basis. The number of fields within the databases is kept to a minimum. The total record count is usually less than 10,000, and file size and memory use are minimal. You'll find recordsets of this size and even smaller perform well when queried.

DRS utilities will save you time and provide high-performance data access, small disk-space footprints and low memory use. They typically require a limited amount of work. You can use a DRS in many ways. When interactive queries are best, I've constructed an .hta GUI that lets you find data sets to match specific queries.

When a GUI isn't necessary, create the DRS and evaluate the data in whichever manner is required to perform the task at hand. That might entail scrubbing log files that contain userids and computer names and replacing those values with substituted values.

Over the years, the DRS has proven to be an excellent resource for quick and efficient no-cost utilities, such as:

• Security log scrubbers
• Security Identifier (SID)/User-retention databases
• SID/User cross-reference lookups
• Print queue lookups
• Weekly admin account comparisons

To build a DRS, you start by creating an ADO Recordset (ADOR) object. Here are two examples using VBScript:

Set DRS = CreateObject("ADOR.Recordset")And:
Set DRS = CreateObject("ADODB.Recordset"

ADOR is a lightweight ADO client that exposes only the recordset interface. If you can create ADO instances, then you can create ADOR instances. ADOR is a subset of ADOdb, which is a subset of ADO. I tend to use ADODB.Recordset, but there is an alternate use

Constructing a DRS database is simple. Start by setting up fields that relate to your endeavor, then populate those fields using data from a source such as Active Directory.

Here's an example of how you might set up a security log scrubber utility database:

Const adFldIsNullable = 32
Set DRS = CreateObject("ADODB.Recordset")
DRS.Fields.Append "SecurityObject",201,256,adFldIsNullable
DRS.Fields.Append "Type",201,256,adFldIsNullable
DRS.Fields.Append "Substitute",201,256,adFldIsNullable
DRS.Ope

Note the first line declares a Constant. The adFldIsNullable constant indicates the field can accept null values. In the scrubber script, fields will always contain data. Including the constant isn't really necessary and you can omit this step, but including it won't affect the script. However, if you do create a database with fields that might not contain data, you'll need to declare and use this constant. Here are some of the script's other characteristics:

• The second line creates the DRS Object.
• The third through fifth lines define the fields of the database.
• The sixth line of the code sample declares the database open for business and ready to accept data.

Let's examine the syntax of the Fields.Append statement:

• The name of the field being created is SecurityObject.
• The 201 indicates a field data type of long string.
• The 256 specifies the field's DefinedSize is a variable-length size. (By definition, any field width greater than 255 for this type of field will be treated as variable length, meaning that the field can store more or fewer than 256 characters.)
• The field Attribute of adFldIsNullable means it accepts null values.

For information on field data types, check out the DataTypeEnum Web page. For information about field widths, check out the Append Method (ADO) Web page.

To add data to the database, simply follow a sequence of events similar to this example:

DRS.AddNew DRS("SecurityObject") = "172.172.\d\d?\d?\.\d\d?\d?"
DRS("SecurityType") = "IP"
DRS("Substitute") = "a1.b1.c1.d1"
DRS.Update

You don't have to issue the DRS.Update method after every record you populate. You can wait to issue this command until you've populated your complete recordset. The Update method simply commits the records to the database and makes records accessible.

In the previous snippet, I've added one record to the database. The scrubber process will use this particular record to look for anything in the text-based log file containing an IP address starting with 172.172, and replace it with a1.b1.c1.d1.

To populate the database with a collection of Active Directory computer and server names, you'd create an LDAP query and populate the database as shown here:

Const ADS_SCOPE_SUBTREE = 2
DNC = GetObject("LDAP://RootDSE").Get("defaultNamingContext")
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT cn FROM 'LDAP://" & DNC & "' WHERE objectcategory = 'computer'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
SubstCount = 1
Do Until objRecordset.EOF
DRS.AddNew
DRS("SecurityObject") = objrecordset.Fields("CN").Value
DRS("SecurityType") = "Host"
DRS("Substitute") = "Host" & SubstCount
SubstCount = SubstCount + 1
objRecordset.MoveNext
Loop

Each of the records created in this sequence would contain a computer name with a Type value of "Host" and a Substitute value of "Hostnnn" (where nnn represents a sequential number—that is, Host123). You can examine the code to see how a similar process is coded to populate the database with userids.

After you've finished populating the database and issuing an .Update, you can now sort, read and filter the recordset. To step through the database, first position the record pointer to the first record, then set up a looping process to cycle through the database like so:

DRS.MoveFirst
Do while Not DRS.EOF
myRegExp.Pattern = "\b" & Lcase(Trim(DRS.Fields.Item("SecurityObject"))) & "\b"
If myRegExp.Test(strNewText) Then
chng2 = Lcase(Trim(DRS.Fields.Item("Substitute")))
strNewText = myRegExp.Replace(strNewText,chng2)
End If
DRS.MoveNext
Loop

The looping process will continue until it reaches an End of File marker. It's also important to note the "MoveNext" method, which advances the record pointer. Without it, you'd find your program in an endless loop.

As you step through the database, you'll be able to access data field value within each record by using the Fields.Items property, as seen in the third and fifth lines of the previous code snippet:

• DRS.Fields.Item("SecurityObject") will contain an Active Directory value of a userid or computer name.
• DRS.Fields.Item("Substitute") will contain the value that will replace that userid or computer name if it's found.

What you don't see in the previous snippet is that it opened a file needing scrubbing and read it into a variable. Then it created a Regular Expression object for the purpose of locating strings within the text and replacing them with specific values.

The items being searched all reside within the database in the field called SecurityObject. The replacement value resides in the field called Substitute. In this example, the database contains all the computer names and userids that exist within a given domain. While still within the looping process, if the DRS finds a SecurityObject value within the text file, it replaces it with the associated Substitute value.

Once the DRS reaches the End of File marker, it reviews the file, writes the scrubbed text file to the file system, and passes on to vendor support once approved. You can use this code as is or to adapt as you wish. You might find you need to tweak the code so it skips common names or words that might reside within your Active Directory.

This just scratches the surface of what you can do with a DRS, but I hope this proves to be beneficial. There's plenty more to discuss on the subject of the DRS, such as creating a GUI that shows off more features of the DRS, including sorting, filtering and displaying records based on SQL-based queries.