Inventorying Windows XP Service Packs - Part 2

By The Microsoft Scripting Guys

Doctor Scripto's Script Shop talks about practical system administration scripting problems, often from the experiences of our readers, and develops scripts to solve them. Most of our examples in the Script Repository perform one simple task; in this column, by contrast, we'll put those pieces together into more complex scripts. They won't be comprehensive or bullet-proof, but they will show how to build scripts from reusable code modules, handle errors and return codes, get input and output from different sources, run against multiple machines, and do other things you might want to do in your production scripts.

We hope you will find these columns and scripts useful – please let us know what you think of them, what solutions you've come up with for these problems, and what you'd like to see covered in the future.

This column is part two of a three-part series. You may find it helpful to begin with the first column: Inventorying Windows XP Service Packs – Part 1

On This Page

Inventorying Windows XP Service Packs – Part 2
Enumerating computers in an Active Directory container with ADSI
Much ADO About Searching Active Directory
Procedures and variable scope
The Full Monty
For more information

Inventorying Windows XP Service Packs – Part 2

When an important deadline looms, you can usually find the Scripting Guys in our war room — the Olympic-size jacuzzi with the built-in champagne cooler in our luxurious penthouse suite — looking out over the Cascade range and meditating on the meaning of it all. Hey, we're big-picture, creative thinkers: that's why we're rich and famous (and why we're always first in line for the free bagels Friday mornings).

Because one of our strongest suits is delegating, we usually leave it to Dr. Scripto, our harried and overworked adviser, to sweat the details. As you can imagine, when he heard about the April 12 deadline that marked the end of preventing the installation of Windows XP Service Pack 2, he launched into overdrive. With typical melodrama, he calls it "SP2-Day." As he works, he does his Winston Churchill imitation – "We shall script them on the beaches, we shall script them in the streets" – and chomps on his virtual cigar.

As usual, he's going a bit overboard. First of all, Service Pack 2 is hardly an invasion: it brings a lot of useful new features, such as Windows Firewall, that defend against the real invasions of worms and viruses and keep machines patched. Then, too, the April 12 deadline applies only if you are using Automatic Update or Windows Update Services to keep your Windows XP clients patched. If you didn't want to roll out Service Pack 2 until after April 12, you could always turn off updating until you were ready, although you'd lose the benefits of automatic patching.

But in any case, we want to know the score on our clients. And so our task is to write a script that will give us an inventory of all the Windows XP clients, so we'll know which ones have not yet been upgraded to Service Pack 2, and then do something with that information. In the previous column, we showed how to do this with WMI on machines not joined to an Active Directory domain. This week we'll show a couple of approaches for an Active Directory environment.

Enumerating computers in an Active Directory container with ADSI

If you've used Active Directory Service Interfaces (ADSI) at all, you'll probably be able to figure out how our first script works. In case you haven't, here's what it does. First, it binds to a specific Active Directory container using the LDAP: provider, and filters the collection of objects it gets back for computer objects only. Then, for each computer in the filtered collection, it checks the OperatingSystemVersion attribute to see if Windows XP is running. Rather than the ADSI attribute that contains the operating system name, we're using one that returns the version number and build for Windows XP: 5.1 (2600). If the computer is running Windows XP but not Service Pack 2, the script displays the version and the service pack, which it gets from the OperatingSystemServicePack attribute.

We are not going to trick this script out with fancy output, dual carbs, twin exhausts and flame decals. We'll save that for our final version, which takes a different approach. As much as we love our old friend ADSI, it's really not the best technology to do this job, for reasons that shall shortly be revealed.

On Error Resume Next
strContainer = "CN=computers,DC=fabikam,DC=com"
Set colComputers = GetObject("LDAP://" & strContainer)
colComputers.Filter = Array("Computer")
For Each objComputer in colComputers
  If objComputer.operatingSystemVersion = "5.1 (2600)" And _
   Not (objComputer.operatingSystemServicePack = "Service Pack 2") Then
    strComputer = objComputer.CN
    Wscript.Echo
    Wscript.Echo strComputer
    Wscript.Echo String(Len(strComputer), "-")
    Wscript.Echo "  " & objComputer.OperatingSystemVersion
    Wscript.Echo "  " & objComputer.OperatingSystemServicePack
  End If
Next

As long as the computers container of fabrikam.com doesn't have any sub-containers, this script checks all the computers. But what if the container is an organizational unit (OU) with other sub-OUs contained within it? This script can't search the sub-containers for computers, so we don't get a full list. You could write a function that recurses down the directory structure, but the code gets a little baroque and the return on your time investment falls. Recursion is a fish of another color, or should I say a horse to fry another day (this is your brain after a few hours on VBScript). Maybe we'll talk about it some other time.

Much ADO About Searching Active Directory

For the task at hand, we don't need to use ADSI because there's a better solution. ActiveX Data Objects (ADO) is a scripting technology that works with databases, and Active Directory, in its heart of hearts, is just another database. ADO code may look a little intimidating at first, but when you get to know it, it's really a cuddly teddy bear of a COM automation library. Walking a directory hierarchy is relatively easy with ADO: by setting one property we can tell the ADO query to drill down through the layers of subtrees of the container. And most important – if you have a big directory – ADO is blindingly fast for searches compared to ADSI: in one informal test we found that searching with ADO was about 100 times quicker.

Our main script for this column will use ADO to retrieve the information on all the computers in an Active Directory domain, or other container. ADO is designed to work with databases so it accepts SQL queries (as well as queries in LDAP form). In those queries, however, we still use the same ADsPath and other ADSI attributes as we did in the previous script, because ADO works in conjunction with ADSI.

Before we develop the full script, we'll show a short version that uses similar ADO code but simply outputs to the display rather than to an Excel spreadsheet. This will let us concentrate on how ADO works.

When we use ADO, we usually begin by creating two objects: ADODB.Connection and ADODB.Command. We tell the Connection object what sort of database we want to connect to. For this script, we tell ADO to connect to the Active Directory Provider. We could also pass the Connection object alternative credentials, but we're not going to do that in this script in the interest of keeping it simple.

Then we tell the Command object what sort of query we want to run against the database. The constant ADS_SCOPE_SUBTREE, which we assign to the Searchscope property of the Command object, tells ADO that we want to also search any containers contained by the top-level container and on down through the hierarchy. Alternatively, we could use other constants to search only the specified container or to search that container and its immediate children.

The Page Size property of the Command object specifies how many records we want the query to retrieve from the directory in a single ADO operation. It's important to specify a page size, because if you don't ADO simply returns 1,000 records (the default) and then stops processing the query. As long as you set the page size, the records are returned in batches of that number until all the matching records have been retrieved. A rule of thumb is to set Page Size to a value between 100 and 1,000, depending on the size of the directory.

Our query in this first ADO script retrieves only those computers running Windows XP on which Service Pack 2 has not been installed. We filter the objects in the container with a WHERE clause further modified by AND and AND NOT statements.

"SELECT CN, operatingSystemVersion, operatingSystemServicePack " _
   & "FROM 'LDAP://DC=fabrikam,DC=com' " _
   & "WHERE objectCategory='computer' " _
   & "AND operatingSystemVersion = '5.1 (2600)' " _
   & "AND NOT operatingSystemServicePack = 'Service Pack 2'"

You've probably seen SQL queries before, either in working with databases or in the WQL queries used with WMI. We won't go into them in detail, but there are a few things to keep in mind in querying Active Directory with ADO. You may have noticed that after the SELECT statement we list out the attributes we want to retrieve from Active Directory. In WQL queries, we often use the "*" to retrieve all properties from a class. But for SQL queries in ADO, the "*" returns only the ADsPath attribute, so if you want other properties returned you have to specify which ones.

In the last part of this script, we execute the command that runs this query. The command returns a recordset of all the objects in the container and its child containers meeting the query's criteria. We sort the objects alphabetically by common name (CN) using the Sort method of the recordset and then move to the first object in the recordset. Using a Do Until loop, we then iterate through the recordset, using the MoveNext method at the end of each record and checking whether the EOF (end of file) property of the recordset is True before the next iteration. ADO doesn't automatically do these things for us: rather, we have to position the cursor where we want it within the recordset and move it through the recordset with the functionality that ADO provides.

For a more complete explanation of scripting ADO, see the Scripting Guys webcast "Poking Your Nose Into Active Directory" (link at the end of this column).

'List all computers in an AD domain running XP but not SP2 using ADO.

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2
strContainer = "DC=fabrikam,DC=com"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = ("ADsDSOObject")
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Page Size") = 1000
objCommand.CommandText = _
  "SELECT CN, operatingSystemVersion, operatingSystemServicePack " _
   & "FROM 'LDAP://" & strContainer & "' " _
   & "WHERE objectCategory='computer' " _
   & "AND operatingSystemVersion = '5.1 (2600)' " _
   & "AND NOT operatingSystemServicePack = 'Service Pack 2'"
Set objRecordSet = objCommand.Execute
objRecordSet.Sort = "CN"
objRecordSet.MoveFirst 
Do Until objRecordSet.EOF
  Wscript.Echo
  Wscript.Echo objRecordSet.Fields("CN").Value
  Wscript.Echo String(Len(objRecordSet.Fields("CN").Value), "-")
  Wscript.Echo "  " & objRecordSet.Fields("operatingSystemVersion").Value
  Wscript.Echo "  " & objRecordSet.Fields("operatingSystemServicePack").Value
  objRecordSet.MoveNext 
Loop

Procedures and variable scope

In our final script, we use code very similar to that in the previous script, but we output the results to an Excel spreadsheet. As in the previous column, we break some of the script's functionality out into separate procedures. This time we use sub-routines to write to the spreadsheet and handle errors.

We also could have separated out into a function the logic of sorting the computer names into different variables depending on the operating system version and service pack, but we chose not to because… well just because it seemed more trouble than it was worth. We don't want to shock anyone, but at the margins decisions on how to break a script down into components are more an art than a science, at least when made by the Scripting Guys.

There's another capability of VBScript (and most programming languages) used in this and the previous column that relates to subroutines and functions. If you haven't run into the ideas of global variables and variable scope before, a little explanation may be in order.

The concept of scope comes up when you use procedures in a script. Each variable has a scope within the script. Those declared or initialized in the main routine of the script are global variables with global scope. This means that sub-routines and functions can read and change the values of those variables as well. In our scripts, we use the optional convention of beginning the names of global variables with "g_".

If you use a variable only in a sub-routine or function, however, that variable's scope is local, that is, limited to that procedure. The main body of the script and other procedures don't know that the variable exists and can't access it.

One way to share variables between the components of a script is to pass them as parameters from the main body of the script to procedures, and from one procedure to another. With global variables, though, you can simply declare, initialize, or use them in the script body before calling any procedures. Then those global variables are automatically accessible to procedures.

VBScript makes the situation a bit more confusing because it does not require that you declare variables before using them (unless you specify Option Explicit at the beginning of the script). You can use the Dim keyword to declare variables, but it's optional. If you just start using a variable without first declaring or initializing it (assigning it a value), VBScript assumes that the value is either a 0 or an empty string ("") depending on the context.

Here are two little examples that show how you can confuse yourself if you don’t understand the difference between global and local variables.

'main body of script

Sub1
Sub2
WScript.Echo x + y

'procedures

Sub Sub1
  x = 2
End Sub

Sub Sub2
  y = 5
End Subs

Output:

0

Contrary to appearances (at least to those who have yet to plumb the subtleties of scoping), this script displays a value of 0 for the sum of x and y. That's because we haven't assigned any value to these variables in the body of the script before calling the sub-routines, so when we Echo them VBScript assumes the values of both to be 0. The script body doesn't know that Sub1 and Sub2 have assigned other values to x and y because the scope of the variables within each procedure is local, even though the local variables have the same names as those in the main body.

In the following script, by contrast, we explicitly assign values to x and y in the main body of the script before we use them in the subroutines. This gives the variables global scope.

'main body of script

x = 0
y = 0

Sub1
Sub2
WScript.Echo x + y

'procedures

Sub Sub1
  x = 2
End Sub

Sub Sub2
  y = 5
End Sub

Output:

7

Now the sub-routines have access to the global variables x and y in the main body of the script and can change their values. The value of x + y this time is 7.

The Full Monty

At last, here's the full-fledged script in all its splendor: it retrieves names of all computers in an Active Directory domain by operating system and service pack and outputs them to an Excel spreadsheet.

To run this script on your local network, the network must use Active Directory, and Excel must be present on the local computer. You must change the value assigned to the variable strContainer to the name of the container on your network that you want to inventory. And you must either have a C:\Scripts directory on the local computer or change the path in strOutputFile to one that exists on this computer. (You can also change the filename of the spreadsheet if you are so inclined.) Even though this script only gathers information, keep in mind that it's generally wise to test scripts first on a test network before running them on a production network.

On a big directory, this script can take a while to run. As a rough rule of thumb − dependent on network speed, directory configuration, processing power of the workstation on which it's run, and a host of other factors − figure on around 30 seconds per 1,000 machines.

In this script we're going to query for all the computers in the container and its sub-containers, rather than just those running Windows XP without Service Pack 2 as we did in the previous script.

We could have continued to use ADO to sort by name and service pack in this script, but just to try another technique, we've chosen to sort the computer names by assigning them to different variables depending on what operating system and service pack are installed, and counting the number of machines in each category. For machines running Windows XP, we'll use a different variable for each service pack, but we'll dump all machines not running Windows XP into a single variable, as we are not concerned about them in this scenario. Finally, we'll alphabetically sort the names of machines for each category in the spreadsheet.

To turn this script into a periodic progress report on service pack updates on the network, you could dynamically add the date to the filename of the spreadsheet with code something like the following, which massages the output of the VBScript Date function:

strDate = Replace(Date, "/", "-")
strOutputFile "c:\scripts\xpsp-" & strDate & ".xls"

Then you could schedule it to run daily (or at some regular interval) with At.exe or Task Scheduler. (You could also schedule an AT command with a WMI script using the Win32_ScheduledJob class.)

Incidentally, this script can take a while to run on a big network. As a rough rule of thumb − dependent on network speed, directory configuration, processing power of the workstation on which it's run, and a host of other factors − figure on at least 30 seconds per 1,000 machines or so. And of course, you must run the script under Administrator credentials that possess any other privileges required for access to your network's Active Directory.

We've divided the script into a main routine and two subroutines.

• The main routine initializes constants and global variables, then connects to ADO and queries the directory for all computer objects in the container. As it loops through the objects, it sorts their names into different global variables according to operating system and service pack – g_strSP2, g_strSP1, g_strSP0 and g_strNotXP, each a string of names delimited by commas – and counts each category. Then it calls a subroutine to write the results to a spreadsheet.

  Also in the main routine, the script checks for errors after attempting to open a connection to the Active Directory Provider with ADO, and again after attempting to execute the query. If an error occurs, it calls error-handling code that is broken out into a separate sub-routine.

• The WriteSpreadsheet subroutine takes as a parameter a string containing the path and name of the spreadsheet file to be created. It connects to the Excel object model by calling the VBScript CreateObject method and passing it the programmatic identifier (ProgID) Excel.Application. This call creates a new Excel object that gives the script access to all the capabilities of Excel.

  With Excel, you first add a workbook object to the Excel object, write and format data within it, then save the workbook as a file, in this case using the filename passed as a parameter.

  Most of the code in this subroutine writes headings to and formats specific cells. It adds up the global counter variables (g_int*) to display numbers of machines for each category. And to display the list of machines, it converts the global string variables (g_str*) to arrays by breaking them into array elements at the commas with the VBScript Split function. Then it loops through the arrays and writes each name into a column, incrementing the row number for each entry. It sorts the names for each column by creating two Excel Range objects, the first the range of data and the second a sort key. It then calls the Excel Sort method on the first range object and passes it the second range, the sort key, as a parameter.

  Finally, the subroutine saves the spreadsheet by the filename assigned to g_strContainer, forcing an overwrite of an existing file if necessary.

• The HandleError subroutine takes as parameters the VBScript Err object containing information on the error and a custom error message depending on the context. The sub displays the custom message along with the error number, source, and description from the Err object.

Here's the code:

'List all computers in an AD domain running XP by SP using ADO.
'Output to Excel spreadsheet.

On Error Resume Next

'Initialize constants and variables.
Const xlAscending = 1
Const xlYes = 1
Const ADS_SCOPE_SUBTREE = 2
g_strContainer = "dc=na,dc=fabrikam,dc=com"
strQuery = "SELECT CN, operatingSystemVersion, operatingSystemServicePack " _
 & "FROM 'LDAP://" & g_strContainer & "' WHERE objectCategory='computer'"
strOutputFile = "c:\scripts\xpsp.xls"

g_strSP2 = ""
g_strSP1 = ""
g_strSP0 = ""
g_strNotXP = ""
g_intSP2 = 0
g_intSP1 = 0
g_intSP0 = 0
g_intNotXP = 0

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = ("ADsDSOObject")
objConnection.Open "Active Directory Provider"
If Err <> 0 Then
  HandleError Err, "Unable to connect to AD Provider with ADO."
End If
objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
If Err <> 0 Then
  HandleError Err, "Unable to execute ADO query."
End If

WScript.Echo "Gathering data from Active Directory ..."

objRecordSet.MoveFirst 
Do Until objRecordSet.EOF
  If objRecordSet.Fields("operatingSystemVersion").Value = "5.1 (2600)" Then
    If objRecordSet.Fields("operatingSystemServicePack").Value = _
     "Service Pack 2" Then
      g_strSP2 = g_strSP2 & objRecordSet.Fields("CN").Value & ","
      g_intSP2 = g_intSP2 + 1
    ElseIf objRecordSet.Fields("operatingSystemServicePack").Value = _
     "Service Pack 1" Then
      g_strSP1 = g_strSP1 & objRecordSet.Fields("CN").Value & ","
      g_intSP1 = g_intSP1 + 1
    Else
      g_strSP0 = g_strSP0 & objRecordSet.Fields("CN").Value & ","
      g_intSP0 = g_intSP0 + 1
    End If
  Else
    g_strNotXP = g_strNotXP & objRecordSet.Fields("CN").Value & ","
    g_intNotXP = g_intNotXP + 1
  End If
  objRecordSet.MoveNext 
Loop 

If Err <> 0 Then
  HandleError Err, "Unable to gather data."
End If

WScript.Echo "Writing data to spreadsheet ..."

WriteSpreadsheet strOutputFile

WScript.Echo "Data written to " & strOutputFile

'******************************************************************************

'Write data to spreadsheet.
Sub WriteSpreadsheet(strFileName)

'On Error Resume Next

'Create spreadsheet and open workbook.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

'Write data to spreadsheet.
objExcel.Cells(1,1).Value = "Inventory of Windows XP Service Packs"
objExcel.Cells(1,1).Font.Bold = True
objExcel.Cells(1,1).Font.Size = 13
objExcel.Cells(2,1).Value = "Time: " & Now
objExcel.Cells(2,1).Font.Bold = True
objExcel.Cells(2,5).Value = "Container: " & g_strContainer
objExcel.Cells(2,5).Font.Bold = True
objExcel.Cells(3,1).Value = "Total Computers: "
objExcel.Cells(3,1).Font.Bold = True
objExcel.Cells(3,3).Value = g_intSP2 + g_intSP1 + g_intSP0 + g_intNotXP
objExcel.Cells(3,3).Font.Bold = True

objExcel.Cells(4,1).Value = "Computers Running Windows XP"
objExcel.Cells(4,1).Font.Bold = True
objExcel.Cells(4,1).Font.Size = 12
objExcel.Cells(5,1).Value = "Number"
objExcel.Cells(5,1).Font.Bold = True
objExcel.Cells(6,1).Value = g_intSP2 + g_intSP1 + g_intSP0

objExcel.Cells(8,1).Value = "Service Pack 2"
objExcel.Cells(8,1).Font.Bold = True
objExcel.Cells(8,1).Font.Size = 11
objExcel.Cells(9,1).Value = "Number"
objExcel.Cells(9,1).Font.Bold = True
objExcel.Cells(10,1).Value = g_intSP2
objExcel.Cells(9,2).Value = "Names"
objExcel.Cells(9,2).Font.Bold = True

arrSP2 = Split(g_strSP2, ",")
x = 10
For Each strSP2 In arrSP2
  objExcel.Cells(x,2).Value = strSP2
  x = x + 1
Next
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()
Set objSortRange = objExcel.Range("B9:B" & x)
Set objSortKey = objExcel.Range("B10")
objSortRange.Sort objSortKey,xlAscending,,,,,,xlYes

objExcel.Cells(8,3).Value = "Service Pack 1"
objExcel.Cells(8,3).Font.Bold = True
objExcel.Cells(8,3).Font.Size = 11
objExcel.Cells(9,3).Value = "Number"
objExcel.Cells(9,3).Font.Bold = True
objExcel.Cells(10,3).Value = g_intSP1
objExcel.Cells(9,4).Value = "Names"
objExcel.Cells(9,4).Font.Bold = True

arrSP1 = Split(g_strSP1, ",")
x = 10
For Each strSP1 In arrSP1
  objExcel.Cells(x,4).Value = strSP1
  x = x + 1
Next
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()
Set objSortRange = objExcel.Range("D9:D" & x)
Set objSortKey = objExcel.Range("D10")
objSortRange.Sort objSortKey,xlAscending,,,,,,xlYes

objExcel.Cells(8,5).Value = "No Service Pack"
objExcel.Cells(8,5).Font.Bold = True
objExcel.Cells(8,5).Font.Size = 11
objExcel.Cells(9,5).Value = "Number"
objExcel.Cells(9,5).Font.Bold = True
objExcel.Cells(10,5).Value = g_intSP0
objExcel.Cells(9,6).Value = "Names"
objExcel.Cells(9,6).Font.Bold = True

arrSP0 = Split(g_strSP0, ",")
x = 10
For Each strSP0 In arrSP0
  objExcel.Cells(x,6).Value = strSP0
  x = x + 1
Next
Set objRange = objExcel.Range("F1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()
Set objSortRange = objExcel.Range("F9:F" & x)
Set objSortKey = objExcel.Range("F10")
objSortRange.Sort objSortKey,xlAscending,,,,,,xlYes

objExcel.Cells(4,7).Value = "Not Running Windows XP"
objExcel.Cells(4,7).Font.Bold = True
objExcel.Cells(4,7).Font.Size = 12
objExcel.Cells(5,7).Value = "Number"
objExcel.Cells(5,7).Font.Bold = True
objExcel.Cells(6,7).Value = g_intNotXP
objExcel.Cells(5,8).Value = "Names"
objExcel.Cells(5,8).Font.Bold = True

arrNotXP = Split(g_strNotXP, ",")
x = 6
For Each strNotXP In arrNotXP
  objExcel.Cells(x,8).Value = strNotXP
  x = x + 1
Next
Set objRange = objExcel.Range("H1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()
Set objSortRange = objExcel.Range("H5:H" & x)
Set objSortKey = objExcel.Range("H6")
objSortRange.Sort objSortKey,xlAscending,,,,,,xlYes

'Move active cell back to A1.
Set objRange = objExcel.Range("A1")
objRange.Activate

'Force save and close spreadsheet.
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.ActiveWorkbook
objWorkbook.SaveAs strFileName
objWorkbook.Close
objExcel.Quit

End Sub

'******************************************************************************

'Handle errors.
Sub HandleError(Err, strMsg)

On Error Resume Next

WScript.Echo "  " & strMsg
WScript.Echo "    Error Number: " & Err.Number
WScript.Echo "    Source: " & Err.Source
WScript.Echo "    Description: " & Err.Description
WScript.Quit

End Sub

As a reward for our efforts, we now have a nice report in an Excel spreadsheet with alphabetical lists of all the Windows XP clients in the container and its child containers, sorted by service pack. If you really wanted to impress your boss, you could make the formatting a lot prettier, highlight the work that needs to be done, and add charts, animations and music. We've only begun to explore the vast capabilities of Excel here. To dig into them further, check out the two columns by Scripting Guy Greg Stemp on MSDN (see links at the end of the column).

We also have in memory (while the script is still running) a list of all the Windows XP clients on which Service Pack 2 has not yet been installed: all we have to do is concatenate together the two global variables g_strSP1 and g_strSP0. In our next column, the last of this Service Pack 2 series (but not of Doctor Scripto's Script Shop), we'll talk about how we can use this information to install Service Pack 2 on those machines. The code we'll explore can be integrated with either this script (for Active Directory environments) or the script from the first column of this series (for non-Active Directory networks).

For more information

ADSI

• TechNet Webcast: Hey, What About ADSI?

• TechNet Script Center Script Repository: Active Directory

ADO

• TechNet Webcast: Database Design and SQL for System Administrators Who Script

• TechNet Webcast: Database Scripting for System Administrators

• TechNet Script Center Script Repository: Scripting Techniques - ADSI

• TechNet Script Center Script Repository: Scripting Techniques - Working with Databases

• Microsoft ActiveX Data Objects (ADO) SDK

Excel

• MSDN Scripting Clinic: Create User Accounts from Information in an Excel Spreadsheet

• MSDN Scripting Clinic: Scripting Excel: The Saga Continues

• TechNet Script Center Script Repository: Microsoft Office – Excel

• Microsoft Office Developer Center - Excel

Windows XP Service Pack 2

• Tales from the Script - I Married Bigfoot. Oh: and Service Pack 2 Made My Computers Disappear

• Windows XP Service Pack 2 Application Compatibility—Supplemental Scripts

• Application Compatibility Guide for Windows XP SP 2

• Windows XP Service Pack 2 - Resources for IT Professionals