Turn On, Tune In, and Disconnect, Part 2

Downloads
98717.zip

Executive Summary: In "Turn On, Tune In, and Disconnect, Part 1," Jim Turner discussed how to use a VBScript script to create a database that holds SID history information for group and user objects in a domain. This VBScript script also demonstrated how to use disconnected recordsets, which are Microsoft ActiveX Data Objects (ADO) recordsets that reside only in memory. In "Turn On, Tune In, and Disconnect, Part 2," Jim discusses an HTML Application (HTA) that you can use to locate and display records in the SID database. You'll learn how this HTA finds and opens SID databases, and how it filters database records. You'll also learn how the HTA overcomes a common HTA annoyance.

Disconnected recordsets are ActiveX Data Objects (ADO) recordsets that reside only in memory while they're active. Because they're in memory, accessing data within them can be quick and efficient. UpdateSIDdb.vbs demonstrates how to create and use disconnected recordsets. This script creates and updates a low-maintenance database that holds SID history information for group and user objects in a domain. Maintaining a SID database can come in handy when you need to retrieve a user's or group's ID but you have only a SID.

In "Turn On, Tune In, and Disconnect, Part 1," I discussed how to use UpdateSIDdb.vbs and how this script works. I also briefly introduced an HTML Application (HTA) named SIDFinderLite.hta. SIDFinderLite.hta is a utility you can run to locate and display records in any SID database that UpdateSIDdb.vbs creates. You can download SIDFinderLite.hta and UpdateSIDdb.vbs by clicking the Download the Code Here button. In this article, I’ll discuss how the HTA finds and opens SID databases, and how it filters database records based on the filter string you specify. I'll also let you know what you can and can't use in your filter strings.

How the HTA Finds and Opens SID Databases
Figure 1 shows what the SIDFinderLite.hta window looks like when it first opens. Notice that I’m using Microsoft’s Visual Web Developer 2008 Express Edition to create this HTA. This software is free and surprisingly very rich with features for building HTAs. So, if you're using Notepad to create and edit HTAs, you might consider downloading and trying this software.

To display the HTA window in Figure 1, SIDFinderLite.hta uses the Window_OnLoad subroutine in Listing 1. This subroutine begins by assigning the appropriate computer name to the strComputer variable, as callout B in Listing 1 shows. In the line at callout B, you'll need to replace DomainControllerName with the name of your domain controller (DC). The DC should be the same computer that you ran UpdateSIDdb.vbs on to create the database. (See Part 1.)

As I mentioned in Part 1, you have the option of running UpdateSIDdb.vbs on individual member servers to obtain the local and domain SIDs that reside on those servers. If you choose this route, the computer name specified in UpdateSIDdb.vbs and SIDFinderLite.hta needs to be the local server's name. In SIDFinderLite.hta, the code in callout A in Listing 1 acquires this name. So, if you ran UpdateSIDdb.vbs on individual member servers, you'll need to remove the comment character (') that precedes the two lines in callout A and comment out the line in callout B.

After assigning the appropriate computer name to the strComputer variable, the Window_Onload subroutine calls the OpenXML subroutine by way of the HTML setInterval method, as callout C shows. The OpenXML subroutine, which Listing 2 shows, opens the SID database. This subroutine begins by using the clearInterval method to cancel the timer created by setInterval. (For an explanation of why I used the setInterval and clearInterval methods and how they work, see the sidebar "Using setInterval to Overcome an HTA Shortcoming.") 

Next, OpenXML sets up the FileString variable, which represents the full path and filename of the database to be opened. The subroutine then checks to see if that database exists. If the database exists, OpenXML uses ADO to open the database with the code in callout A. If the database doesn't exist, the script writes an alert message in the User text box, as Figure 2 shows. If you receive the message that the database doesn't exist and you ran UpdateSIDdb.vbs on a DC, make sure that:

• The correct DC name is in the code at callout B in Listing 1. The database must reside in the C:\Temp folder.
• The code in callout B in Listing 1 is uncommented.
• The code in callout A in Listing 1 is commented out.

 If you receive the message that the database doesn't exist and you ran UpdateSIDdb.vbs on a member server, make sure that:

• The database resides in the C:\Temp folder.
• The code in callout A in Listing 1 is uncommented.
• The code in callout B in Listing 1 is commented out.

There's one element in the HTA that you might be interested in if you have more than one SID database located on the local machine: the Switch Source button. For example, if you ran UpdateSIDdb.vbs on more than one member server, you could copy the databases from the other member servers to the local machine you're running SIDFinderLite.hta on. Then, to open a different database, you simply enter the name of the member server that contains the database you're interested in searching and click the Switch Source button. Without going into great detail, when you click this button, it launches the Switch subroutine, which pretty much performs the same procedures as the Window_Onload subroutine—that is, the Switch subroutine checks for the existence of the database and calls the OpenXML subroutine. However, the Switch subroutine demonstrates one important ADO method: the Close method. It’s important that you close a database when you’re done with it to free memory and avoid possible problems when attempting to open another database using the same object name.

Whether your accessing the local SID database or a database you copied from another server, the OpenXML subroutine displays the server's name in the Source text box so that you always know which database you’re accessing. This is done with the code in callout B in Listing 2. Finally, OpenXML calls the FilterDRS subroutine.

How Database Records Are Filtered
The FilterDRS subroutine, which Listing 3 shows, filters database records. At callout A in Listing 3, FilterDRS sets a filter to limit the records returned based on what you specify in the Find/Filter On text box. The filter is specified with the code

FilterString = SearchStr.value

SearchStr.value will be whatever value is contained in the SearchStr input text box. SearchStr is the underlining object name for the HTA's Find/Filter On text box.

As Figure 1 shows, the filter string is initially set to

SID LIKE '*-500*'

to provide a filter during the initial database loading process. This filter retrieves SIDs that include -500, which are Administrator accounts. I hard-coded this value in the SearchStr element when I created my HTA form. Here’s what that line of HTML code looks like within the HTA's <BODY> tag:

<input type="text" name="SearchStr" size="61" value="SID LIKE '*-500*'">

Alternatively, I could've hard-coded this filter setting in the FilterDRS subroutine with a statement such as

FilterString = "SID LIKE '*-500*'"

After the filtered database is initially loaded, you can specify any valid filter by entering it in the Find/Filter On text box and clicking the Filter Records button. Because you can accidentally clear the Find/Filter On text box, not have any results from a filtered search, or inadvertently enter an invalid filter, the FilterDRS subroutine tests for empty filter strings and null filter strings.

Empty filter strings. Empty strings can occur if you accidentally clear the Find/Filter On text box, then click the Filter Records button. The FilterDRS subroutine checks for and handles empty filter strings in the code at callout B in Listing 3. When FilterString isn't empty, FilterDRS applies the specified filter by setting the ADO Recordset object's Filter property to the FilterString variable's value. To ensure that ADO is at the top of the filtered recordset, the MoveFirst method is used to move the record pointer to the first record. For the record, MSDN's Filter Property documentation states that the record pointer is automatically set to the first record of the filtered recordset, so you can remove the DRS.MoveFirst statement if you want. However, including that statement doesn’t hurt anything; with it, I’m certain that the record pointer is where I want it to be.

When FilterString is empty, FilterDRS first displays a message box that lets you know empty filter strings are ignored. FilterDRS then resets the SearchStr value to the last known good filter, which repopulates the Find/Filter On text box with that filter.

Null filter strings. Null filter strings can occur if the filtered recordset doesn't contain any records or if you enter an invalid filter. The FilterDRS subroutine checks for and handles null filters strings in the code at callout C. The first statement in that code

If DRS.EOF OR DRS.BOF OR Err.Number <> 0 Then

can be confusing, so let's take a close look at it.

To determine whether a filtered recordset contains at least one record, FilterDRS uses the Recordset object's BOF and EOF properties. BOF returns True (-1) when the current record position is before the first record. Similarly, EOF returns True (-1) if the current record position is after the last record. If either BOF or EOF is True, it means that the record pointer is at either the beginning or end of the file and there aren't any records in the filtered recordset. In other words, for there to be a record, both BOF and EOF must be False (0). Taking this into consideration, you can translate the statement just given to

If (DRS.BOF = True) OR (DRS.EOF = True) OR (Err.Number <> 0)

The last part of this translated statement—(Err.Number <> 0)—determines whether an error has occurred. If you enter an invalid filter, an error is thrown. Errors can be represented by any one of several numbers, except 0. So, the statement uses the <> (not equal to) operator to check whether the Err object's Number property value is any number but 0.

If BOF is True (i.e., no records in recordset), EOF is True (i.e., no records in recordset), or the error number is any number but 0 (i.e., an invalid filter), the FilterDRS subroutine first displays a message box that states nothing was found for that filter string. FilterDRS then resets the value in the Find/Filter On text box to the problem-causing string. That way, you can check the filter string to see if you made a simple mistake, such as typing one number wrong, and correct that mistake without having to retype the entire filter string.

If the filtered recordset contains records, the FilterDRS subroutine displays them. To do so, FilterDRS first clears the User text area box with the code

TxtArea.Value = ""

then initializes the RecordString variable to an empty string. RecordString is used to temporarily hold all the fields of every record in the filtered recordset. The code at callout D uses that variable in a Do…Loop statement to build the display of records. This code cycles through all the records until it reaches the end of the recordset. For each record, FilterDRS uses the Recordset object's Fields.Item property to retrieve the values of the User, SID, and SIDType fields. As I explained in Part 1, the SID database has these three fields for each user or group object in a domain. The User field stores the object's name, which is usually a user's or group's ID. The SID field holds the string of unique alphanumeric characters used to identify that object. The SIDType field contains a numeric value that represents the type of object. These fields' values are added to RecordString, along with some tabs so that the fields display evenly on screen. The TabCount function adds those tabs. I won't go into those details, but you can look at the TabCount function in SIDFinderLite.hta to see how it works. Finally, the Recordset object's MoveNext method is used to move the record pointer to the next record in the filtered recordset. If the MoveNext method wasn't called, you'd wind up in an endless loop.

After Do…Loop reaches the end of the recordset, the RecordString variable's contents are displayed in the User text box with the line

TxtArea.Value = RecordString

The HTA also displays the number of records in the filtered recordset, so the last task the FilterDRS subroutine performs is to determine that number using the Recordset object's RecordCount property with the code

NumberOfRecords.value = DRS.RecordCount & " Records"

This code also populates the NumberOfRecords text box with that number. The NumberOfRecords text box isn't labeled in the HTA window, but it resides just to the right of the Filter Records button. I didn't include a label because the word records appears within the display.

How to Create Filters
Since you’ll more than likely have to use filters to locate specific sets of records in the SID database, it's important to know what options you have at your disposal when creating a filter string and equally important to know the limitations. The filter string needs to consist of a database field's name, an operator, and a value. For example, in the filter string

SID LIKE '*-500*'

SID is the database field's name, LIKE is the operator, and '*-500*' is the value. This filter string limits the records returned to those that contain -500 anywhere in the SID field.

The field name that you specify must exist in the database. So, in this case, you can specify SID, User, or SIDType. Note that if a database includes a field name that contains spaces, you must enclose the field name in square brackets when constructing the filter string. For example, had I chosen to use SID Type instead of SIDType in my database, the filter string to look for records that had a SID Type value greater than 1 would be

\[SID Type\] > 1

The operators that you can use are straightforward, but you're limited to the following:

• < (less than)
• > (greater than)
• <= (less than or equal to)
• >= (greater than or equal to)
• = (equal to)
• <> (not equal to)
• LIKE

As you can see, you can't use the pound sign (#) to represent “not equal to” and you can't use the NOT operator or the ! (logical NOT) operator.

If you use the LIKE operator in a filter string, you can use values that contain wildcards, but the asterisk (*) and percent sign (%) are the only wildcards allowed—and they provide the same functionality. You can place a wildcard at the end of a value. You can also place wildcards at the beginning and the end of a value. However, you can't place a wildcard only at the beginning of a value. For example, the following filter strings are valid:

User LIKE 'Green*'User LIKE '*Green*'

However, this filter string isn't valid:

User LIKE '*Green'

The values that you use in filter strings can be strings, integers, or dates. As you probably noticed, if your value is a string, you must enclose it in single quotes. Should you need to filter on a string that contains a single quote, such as in the name O’Hare, you need to escape (i.e., flag) the single quote with another single quote, as in

User LIKE '*O''Hare*'

For integer or surprisingly enough even for strings consisting of only numbers, you can simply specify the value without single quotes, as in

SIDType=1

There are no date-type fields in the SID database, but you can include date values. You need to precede and end a date value with a pound sign (#). For example, a filter string that references a date might look like

DateField=#12/19/83#

The filter strings that I've shown you so far are simple. You can create more complex filter strings, which are known as compound clauses, by joining two or more simple clauses with the AND operator or the OR operator. For example, the filter string

(User LIKE '*Brown*' AND SIDType=1) OR (User LIKE '*Green*' AND SIDType=1)

returns any record that has a SIDType field value of 1 (which represents a user object, as shown in the SIDTypes drop-down list in the HTA window) and contains Brown or Green in the User field. In other words, it returns any user with Brown or Green in his or her username.

Got Questions or Comments? Let Me KnowThat covers the major parts of SIDFinderLite.hta and how to use them. I invite you to leave your questions and comments in the online User Comments section below. Let me know if you’d like to know more about other tasks you can perform with disconnected recordsets, such as sorting, finding, or deleting records. I’d be happy to provide more information about such topics.