Sorting Data by Using a Disconnected Recordset

  • Article

Microsoft® Windows® 2000 Scripting Guide

As noted previously, one of the major limitations of the WMI Query Language (WQL) is the fact that you cannot specify a sort order of any kind; all you can do is request a collection of data and then display the data in the order in which WMI returns it. For example, service instances are always returned in alphabetical order. You cannot modify your WQL query to instead sort the data by service state or by the user account under which the service runs.

If you want to display data in an alternative fashion (and if you do not want to use the tabular data control), you will have to write code that can sort and then display the data. Traditionally, script writers have done this by placing the data in an array and then using sorting algorithms (such as the "bubble sort", an algorithm that iterates through a list of elements, swapping adjacent pairs that are out of order) to sort the data.

Unfortunately, these sorting algorithms can become quite complicated, particularly if the data set contains a number of fields (service name, service status, service account, and so forth). A much easier way to sort information is to place this information in a disconnected recordset and then use a single line of code to sort the information.

A disconnected recordset is essentially a database that exists only in memory; it is not tied to a physical database. You create the recordset, add records to it, and then manipulate the data, just like any other recordset. The only difference is that the moment the script terminates, the recordset, which is stored only in memory, disappears as well.

To use a disconnected recordset to sort data, you must first create the recordset, adding any fields needed to store the data. After you have created the fields, you then use the AddNew method to add new records to the recordset, using the same process used to add new records to a physical database. After the recordset has been populated, a single line of code can then sort the data on the specified field. For example, this line of code sorts the recordset by the field ServiceAccountName:

DataList.Sort = "ServiceAccountName"

Scripting Steps

Listing 17.14 contains a script that sorts data by using a disconnected recordset. To carry out this task, the script must perform the following steps:

  1. Create constants named adVarChar (value = 200) and MaxCharacters (value = 255). These constants are used when creating fields for the disconnected recordset; they set the data type to variant and the maximum number of characters to 255.

  2. Create an instance of the ADO Recordset object.

  3. Use the Fields.Append method to add two fields to the recordset: ServiceName and ServiceState.

  4. Open the recordset.

  5. Use a GetObject call to connect to the WMI namespace root\cimv2, and set the impersonation level to "impersonate."

  6. Use the ExecQuery method to query the Win32_Service class.

    This query returns a collection consisting of all the services installed on the computer.

  7. For each service in the collection, use the AddNew method to add a new record to the recordset. For each new record:

    • Set the value of the ServiceName field to the name of the service.

    • Set the value of the ServiceState field to the state (running, stopped, paused, resuming) of the service.

  8. Use the Update method to update the recordset with the new record.

  9. After all the records have been added, use the Sort method to sort the recordset by ServiceState.

  10. Use the MoveFirst method to move to the first record in the recordset.

  11. For each record in the recordset, echo the service name and the service state. The output will be sorted by service state.

Listing 17.14 Sorting Data by Using a Disconnected Recordset

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

Const adVarChar = 200
Const MaxCharacters = 255
Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "ServiceName", adVarChar, MaxCharacters
DataList.Fields.Append "ServiceState", adVarChar, MaxCharacters
DataList.Open
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strComputer& "\root\cimv2")
Set colServices = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_Service")
For Each Service in colServices
 DataList.AddNew
 DataList("ServiceName") = Service.Name
 DataList("ServiceState") = Service.State
 DataList.Update
Next
DataList.Sort = "ServiceState"
DataList.MoveFirst
Do Until DataList.EOF
 Wscript.Echo DataList.Fields.Item("ServiceName") _
 & vbTab & DataList.Fields.Item("ServiceState")
DataList.MoveNext
Loop