AD and WMI Reporting

When you need to extract Active Directory (AD) or Windows Management Instrumentation (WMI) data, several tools are at your disposal. The Csvde command-line utility (csvde.exe) and the Scriptomatic tool (scriptomatic.exe) are both extremely useful in this respect. To get the most out of this data, you can use Microsoft Excel to format the output into clean and easy-to-read reports. Here are some scripts to show you how.

AD Reporting
Csvde, which resides in %systemroot%system32 after you install Windows Server 2003 or Windows 2000 Server, lets you import and export AD data to a comma-separated value (CSV) file according to attribute and object filters. For example, to extract all user-object data to a .csv file called C:report.csv, open a command window and type the following command:

csvde -f c:report.csv -v -d   "dc=mycorp,dc=com"  -r "(objectClass=User)" -p SubTree

This command directs Csvde to extract the information to the specified file (-f), use verbose mode onscreen while running (-v), start from mycorp.com's root (-d), look for the specified objects (i.e., User objects) only (-r), and scan the whole tree (-p). If you want to extract the user objects' ADsPath property only, add the following parameter:

-l ADsPath

For more information about Csvde's syntax, use the tool's -? parameter, type the csvde command with no parameters, or go to http://www.microsoft.com/technet/prodtechnol/windowsnetserver/proddocs/entserver/csvde.asp.

After you've used Csvde to extract AD information, the best method for making that data into legible reports (with formatted columns, headings, and so forth) is to use Excel. You can import the .csv file into Excel, then save the report as an Excel (.xls) file. I've created a VBScript script, which Listing 1, page 2, shows, that automates this process without visibly opening Excel. The script creates an Excel Application object, then uses the Workbooks::Open method to open the .csv file that I used Csvde to create (i.e., C:report.csv). The script then passes two parameters to the ActiveWorkbook::SaveAs method. The first parameter specifies the new file to be created (i.e., C:report.xls), and the second parameter defines the file format (i.e., xlNormal). Note that running this script on a Win2K Server that doesn't run Excel will result in an error.

System Reporting
If you're considering extracting system data for reporting purposes, investigate Scriptomatic, which automates the creation of complex WMI reporting scripts. (For information about WMI scripts and other reporting topics, see "Related Reading," page 2.) You can download this free tool from http://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/wmimatic.asp (at the time of this writing, the tool is also scheduled to be included in the Windows 2003 resource kit). Scriptomatic works on Windows 2003, Windows XP, and Win2K systems and on Windows NT 4.0 Service Pack 6 (SP6) and Windows 98 systems on which you've installed Microsoft Internet Explorer (IE) 5.0, WMI, and Windows Script Host (WSH) 5.6. You can use the tool to generate basic WMI scripts, which you can then modify to be more versatile.

After you download Scriptomatic, run scriptomatic.hta to open the tool. The primary screen contains a drop-down list of WMI classes for you to select from. When you select a class, the tool automatically returns a script that will report every item of that class on the local system. (The scripts always point to the local PC, but you can modify the scripts to run on other PCs in your organization.) For example, when you select Win32_OperatingSystem from the list, the tool instantly creates a script that reports every item within the Win32_OperatingSystem class. If you then click Run, Scriptomatic uses cscript.exe to execute the script, opening a command window and placing the results into that command window. You can modify the created script to export the results into Excel.

Listing 2 shows the Scriptomatic-generated Win32_OperatingSystem class script, which I've modified in several ways. First, for the sake of brevity, I've shortened the script so that it extracts only the CSName, Caption, and Version properties. (On an XP system, CSName is the client's short name, Caption is some text along the lines of Microsoft Windows XP Professional, and Version is the OS build version number—for example, 5.1.2600.) I've also added code that creates an Excel Application object, adds a workbook to that object, and names the first worksheet OS Data. The code then places column headings (i.e., PC Name, Caption, and Version) in the first three cells in Row 1 (i.e., A1, B1, and C1). To make the headings stand out, the code sets those cells' background color to dark blue and sets the text to a bold white font. (To discover how I determined the code to use to automate these Excel tasks, see the sidebar "Formatting the Reports," page 4.)

The next three lines of code are straight from the Scriptomatic script and extract data from the Win32_OperatingSystem class into a collection called colItems. Then, to populate the spreadsheet, the code sets a row index marker (which starts at Row 2, after my heading row) and enters a loop through the collection. This loop adds data into the three columns, using the row index to target the correct cell each time. At the end of the loop, the code increments the row index marker. (Using a For Each...Next loop through colItems when only one item exists in the collection might seem silly, but this script is simply a generic example of how to import your data into Excel. The advantage of the Scriptomatic scripts is that they print every item for every returned result. Depending on the WMI class you select, those items can become quite numerous. You can modify the sample script in Listing 2 as necessary to start each new item on a new row.)

After all the data is in the spreadsheet, the code selects all the cells in the worksheet and autofits the width of the columns and the rows to make the report easier to read. Last, the code makes the Excel spreadsheet visible. I could have written the script to make the spreadsheet visible earlier so that I could watch the script populate the spreadsheet, but doing so introduces the risk of the user accidentally clicking the spreadsheet while the script is working—an action that would interrupt the script and cause it to abort with an error. If you never intend to show a system's user the data you're retrieving from that system, you can simply leave out the code that makes Excel visible and let the entire process run in the background.

If you want to save the Excel spreadsheet, then close Excel, simply add several lines to the end of the script. For example, to save the spreadsheet to the local system under the filename report.xls, use the following code:

appExcel.ActiveWorkbook.SaveAs _  "C:report.xls"appExcel.Workbooks.CloseappExcel.Quit

To open a saved file in another script and have this second script perform additional changes, save the file, and close it, use the following code:

appExcel.Workbooks.Open _  "C:report.xls"' Perform your changesappExcel.ActiveWorkbook.SaveappExcel.Workbooks.CloseappExcel.QuitRemote PCs

How can you modify the provided script to return information about one or more remote PCs? The answer lies in the script's strComputer line, which specifies the target computer. By default, Scriptomatic sets this line of code (at callout A in Listing 2) to ".", indicating the current PC. To check a remote PC instead, change "." to the full DNS name of the remote PC—"anotherpc.emea.mycorp.com", for example. What about checking multiple PCs? If you want to check only a dozen PCs, you can add them to the script as an array and cycle through each one in turn. However, if you want to check more PCs—say, all the PCs in a specific OU—you need a way to retrieve all those long DNS names from AD. That requirement takes us back to Csvde, which you can use to export the names of all the PCs in a certain group, a certain OU, or the entire network.

Suppose that you want to find all the PCs listed in AD, then pull out the OS Caption and Version information from each one. You can do so by using Csvde and a modification to the Scriptomatic-generated WMI Win32_OperatingSystem script. The script that Listing 3, page 5, shows uses Csvde to extract the list of client PCs to a .csv file, opens that file in Excel (placing the information about each PC in a new row), and reads the file line by line. The script then uses WMI to try to connect to each PC and read the OS data. The script writes the results to a second Excel spreadsheet, indicating for each PC whether the script could connect to the PC and providing the OS data for those PCs to which it could connect.

The script begins by declaring the necessary constants and variables. You'll need to change the specified PATH, CSV, XLS, and AD_ROOT variable values to ones that are appropriate for your environment.

The script then uses the WSH Shell::Run method to run Csvde, ensuring that the Run window remains hidden and that the script's execution pauses until Csvde has finished running. The script uses the Csvde string that I described earlier but sets the filter (-r) to search for computers and the property to be returned (-l) to the PCs' long DNS names.

After the script populates the .csv file, it creates an Excel Application object, opens the .csv file, and saves the file as an .xls file, closing the original .csv file in the process. The script then uses the standard FileSystemObject::DeleteFile call to delete the .csv file and the csv.log file.

The .xls file has one sheet containing the PC data. This data includes an extra first column containing each returned item's distinguished name (DN) and an extra header row identifying each returned item. We don't need this information in our report, so the script deletes the first column and first row.

Now the .xls file contains the data we need in one worksheet named Machines (or whatever you called your .csv file—Excel opens the .csv file into one worksheet and gives that worksheet whatever name you gave the .csv file). Because only one worksheet exists, the script can rename the sheet (with a more descriptive name such as OS Details for All PCs, for example) simply by selecting all worksheets and renaming the active sheet. I also need to create a heading row, so the script inserts a new first row, shifting all the other cells down. The script then inserts column headings into the empty row and formats those cells, as I described for Listing 2.

Now we're ready to begin adding data, starting at Row 2. The script uses a While loop to determine whether the first cell in that row (i.e., cell A2) is empty. If not—in other words, if the cell contains a PC name—the script continues, setting the strComputer object to the name in the cell and attempting to use the Scriptomatic WMI code to retrieve data from that system. Before entering the loop, the script confirms that the objWMIService object isn't equal to Nothing. If the object is equal to Nothing, the script increments the rowcount and skips to the next row. (Inside the While loop, the script sets the objWMIService object to Nothing after each pass. Otherwise, if WMI failed to connect to a remote PC, the object would fail to loop to the next machine in line.)

When looping through colItems, the script sets the first cell of the current row to be strComputer and the second and third cells to be the Caption and the Version, respectively. If colItems returns more than one result, the script moves all the following rows—which contain the names of the PCs we've yet to connect to—down to make room for this data. At the same time, the script adds the first cell, containing the strComputer value, to these new blank rows. So for example, if colItems contains three results, the script will fill in Row 2 with the first set of data from colItems, create a blank Row 3, insert strComputer and the next set of data from colItems into Row 3, create a blank Row 4, insert strComputer and the last set of data from colItems into Row 4, and create a blank Row 5. When the loop determines that colItems is empty and that we therefore don't need that final blank row, the script deletes that row. The next row, which contains the next PC, moves up, and the script continues.

Last, the script autofits the columns and rows, saves the .xls file, and opens it for viewing. You can just as easily have the script quit Excel without displaying the file to the user and instead send an email message notifying the user that the file is available. You can even have the script email the file, then remove it from the PC that the script ran on.

Reports in a Flash
To see a large amount of data for each PC, modify the script that Listing 3 shows so that it uses the Win32_Service class and the Description property, then watch what happens. (I strongly suggest that you limit the number of clients you connect to for this test, because these changes add one line per service per PC, or as many as 100 more lines per client.) Rather than using Csvde, comment out the lines at callout A in Listing 3 and reference an existing .csv file that contains a dozen or so PC names. This test will give you more than 1000 lines in your spreadsheet. Pretty powerful stuff, isn't it? Play around with Csvde, Scriptomatic, and Excel—reporting about AD and your WMI clients might actually become fun.