TechNet Magazine > Home > Issues > 2008 > June >  Output data to the command window in tabular fo...
Hey, Scripting Guy! Not-So-Hard Work Has Its Rewards, Too
The Microsoft Scripting Guys

Download the code for this article: HeyScriptingGuy2008_06.exe (150KB)

For hundreds of years, people have believed that hard work is it own reward; that true satisfaction comes from putting in an honest day's labor; that true happiness comes from the destination, not the journey; that—well, you get the idea. Work hard and without complaint (think Cinderella), and, someday, you will be rewarded.
Note: is that really true, that if you work hard you'll not only be happier but, someday, you'll be rewarded for your efforts? Why are you asking us a question like that?
Of course, Cinderella was lucky; after all, if you live with a wicked stepmother and a couple of wicked stepsisters, you have all sorts of opportunities to work hard. But what if you're not so fortunate? What if you don't live with a wicked stepmother and a couple of wicked stepsisters? How are you supposed to put in long, grueling, thankless days of work? How will you ever find true happiness?
Well, if you're bound and determined to work your fingers to the bone, the Scripting Guys suggest you try writing a script that outputs data in nice, neat, tabular format, like the output shown in Figure 1.
Figure 1 Tabular output (Click the image for a larger view)
Like we said, Cinderella was very lucky: she got to clean the house from top to bottom and, when finished, got to sit in the hearth amidst the ashes and soot. But even Cinderella would have balked at having to write a script that could display data in tabular format. Sitting in the ashes and soot is one thing. Writing a script that outputs data in tabular format is quite another altogether.
Note: well, unless you have to sit in the hearth to write your scripts; then they're pretty much the same thing.
Why would Cinderella balk at this task? Because it's just too darn hard. The only way to display data in tabular format in VBScript is to do something along these lines:
  • Start by determining the maximum size for each column. For example, you may want the display name of a service to fill up 52 character spaces.
  • From there, calculate the number of characters in a piece of data. For example, the display name Adobe LM Service has 16 characters.
  • If the display name exceeds the 52-character limit, determine the number of characters that need to be chopped off the end of the string to make it fit in the allotted space. If the display name doesn't exceed 52 character spaces, determine how many blank spaces must be added to the end of the string to make the length exactly 52 characters.
  • Repeat for the next set of data. And the next. And so on.
If you're familiar with the story of Cinderella, then you know it's possible that your fairy godmother will pop in unannounced, turn a mouse into a system administrator (no comments on how easy that might be), and then the mouse will write this script for you. But you probably shouldn't count on that happening. You're probably on your own here.
On the bright side, though, you'll be the most satisfied person in the world. That's because you'll also be the hardest-working person in the world. The hardest working by far.
Of course, some people would be willing to trade a little satisfaction for the opportunity to not have to work quite so hard. If you're one of those people, you're probably thinking, "Thank goodness the Scripting Guys are here; they'll tell me how to change a mouse into a system administrator, then tell me how to get that mouse to format my output as a table." Well, we're afraid we have bad news for you: the Scripting Guys have no idea how to turn a mouse into a system administrator. (Although we can turn a system administrator into a mouse, if that's of use to you.) We don't know how you can get someone to write scripts for you, let alone scripts that will output data in tabular format.
But that's OK. As long as you're running Windows® XP or Windows Server® 2003 (which many of you are), you don't need a mouse to write your scripts for you. (No, sorry, we're afraid this doesn't work on Windows Vista®.) Instead, you can do this yourself, and with minimal effort, thanks to the Microsoft.CmdLib object. Take a look at the sample script in Figure 2.
Dim arrResultsArray()
i = 0

Set objCmdLib = _
  CreateObject("Microsoft.CmdLib")
Set objCmdLib.ScriptingHost = _
  WScript.Application

arrHeader = Array("Display Name", _
  "State", "Start Mode")
arrMaxLength = Array(52, 12, 12)
strFormat = "Table"
blnPrintHeader = True
arrBlnHide = Array(False, False, False)

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" _
  & strComputer & "\root\cimv2")

Set colServices = objWMIService.ExecQuery _
  ("Select * FROM Win32_Service")

For Each objService In colServices
  ReDim Preserve arrResultsArray(i)
  arrResultsArray(i) = _
  Array(objService.DisplayName, _
    objService.State,objService.StartMode)
  i = i + 1
Next
objCmdLib.ShowResults arrHeader, _
  arrResultsArray, arrMaxLength, _
  strFormat, blnPrintHeader, arrBlnHide

In case you're wondering, Microsoft.CmdLib is a COM object that ships with Windows XP and Windows Server 2003. This object has a number of different features; for more information, type cmdlib.wsc /? at the command prompt and read the comments in the script file that pops up on screen. For today, we're interested in only one feature of Microsoft.CmdLib: the ability to display data in tabular format.
This leads to an obvious question: how do you display data in tabular format? Let's see if we can figure that out. As you can see, our sample script kicks off by defining a dynamic array named arrResultsArray:
Dim arrResultsArray()
In a typical script, data is echoed back to the screen as quickly as it is retrieved. But why would you expect the Scripting Guys to do anything in typical fashion? In this script, we aren't going to echo back the data as it is retrieved. Instead, we're going to store all the returned data in an array, then let Microsoft.CmdLib format and display the data for us.
In other words, that's why we start out by creating a dynamic array (that is, an array that can be resized during the course of the script). After defining the array, we set the value of a counter variable named i to 0; we'll use this variable to keep track of the current size of the array.
In case you're wondering, we set the value of i to 0 because the first item in an array is always given the index number 0. Consequently, when we add the first item to the array, we're adding item 0 rather than item 1.
Next we need to initialize our instance of the Microsoft.CmdLib object; that's what these two lines of code are for:
Set objCmdLib = _
CreateObject("Microsoft.CmdLib")
Set objCmdLib.ScriptingHost = WScript.Application
And that brings us to this little block of code:
arrHeader = Array("Display Name", "State", "Start Mode")
arrMaxLength = Array(52, 12, 12)
strFormat = "Table"
blnPrintHeader = True
arrBlnHide = Array(False, False, False)
What we're doing here is setting up a few parameters for configuring our output. In the first line, we're assigning values to an array named arrHeader; as you might expect, these are the headings for each column in our output table. For this sample script, we're going to retrieve information about the services running on a computer, then we're going to display the values of the DisplayName, State, and Start Mode properties for each service.
Not too surprisingly, we assign our array the column names Display Name, State, and Start Mode (though we could have just as easily assigned our columns the names A, B, and C; Larry, Moe, and Curly; or anything else we wanted. The column names don't have to be the same as the property names).
Note: there are many variations of the Cinderella story, including the names of her stepsisters. In the Disney version of Cinderella, the stepsisters are named Drizzella and Anastasia, which just happen to be the first and middle names of our Scripting Editor!
In the second line of the code, we're assigning values to another array, named arrMaxLength. This array holds the size of each column in the table. In our output, we want to allocate 52 character spaces to column 1 (the service display name); we then want to allocate 12 spaces each to the service state and start mode. (Microsoft.CmdLib will automatically insert a blank space between columns.) If we want our column sizes to be 52, 12, and 12 character spaces (which we do), we use code that looks like this:
arrMaxLength = Array(52, 12, 12)
The third line specifies the output format for our data. We want the data displayed as a table; hence we set the value of strFormat (the variable that holds the output type) to, well, Table. Suppose instead we wanted to display the data as a comma-separated values list. In that case, we'd set the format to CSV, like so:
strFormat = "CSV"
In turn, we'd get output similar to what we have here:
"Display Name","State","Start Mode"
"Adobe LM Service","Stopped","Manual"
"Adobe Active File Monitor V4","Stopped","Manual"
"Alerter","Stopped","Manual"
"Application Layer Gateway Service","Running","Manual"
"Apple Mobile Device","Running","Auto"
"Application Management","Stopped","Manual"
Notice how Microsoft.CmdLib not only put commas between each item, but also enclosed the individual values in double quotes. That's a nicer feature than you might first think. After all, to do this manually, you'd have to use code like the following:
Wscript.Echo Chr(34) & objService.DisplayName & Chr(34) & "," & Chr(34) & 
   objService.State & Chr(34) & "," & Chr(34) & objService.StartMode & Chr(34)
Yuck.
What's that? You don't like tables, but you don't like the CSV format either? Well, in that case try setting the format to List, which gives you output similar to this:
Display Name: Adobe LM Service
State:        Stopped
Start Mode:   Manual
Display Name: Adobe Active File Monitor V4
State:        Stopped
Start Mode:   Manual
But we digress. (As we are prone to do on occasion.) After defining the output format, we set the value of a variable named blnPrintHeader to True:
blnPrintHeader = True
We're going to use blnPrintHeader to tell Microsoft.CmdLib to print the column headings. What if we don't want to print the column headings? That's fine; in that case, set blnPrintHeader to False:
blnPrintHeader = False
Last, we have this line of code:
arrBlnHide = Array(False, False, False)
When it comes time to display your data, Microsoft.CmdLib gives you the option of showing or hiding a column. To hide a column (that is, to suppress data for a particular property), set the value for that property to True; to show a column, set the value to False.
In our output, we're going to show the values for the DisplayName, State, and StartMode properties, in that order; hence we use the values False, False, False in our array. What we if wanted to show the DisplayName, hide the State, and show the StartMode? In that case, we'd use this line of code:
arrBlnHide = Array(False, True, False)
Remember, use False to show a column and True to hide a column.
At this point we're ready to output some data—or we would be if we actually had some data. (Have the Scripting Guys ever written a script that failed to output data, then spent an inordinate amount of time debugging that script, only to discover that they hadn't bothered to actually retrieve any data in the first place? Uh, no, of course not; whatever makes you say that?)
With that in mind, our next step is to bind to the Windows Management Instrumentation (WMI) service on the local computer, then use the ExecQuery method to retrieve information about all the services installed on that computer:
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colServices = objWMIService.ExecQuery ("Select * FROM Win32_Service")
We should probably mention that you aren't limited to working with WMI data here. As you might expect, Microsoft.CmdLib can work with any kind of data. WMI is just a handy way to retrieve a bunch of data.
Once we have our collection of service-related data, we set up a For Each loop to cycle through all the services in the collection. However, instead of echoing back the property values for each service, we execute this code:
ReDim Preserve arrResultsArray(i)
arrResultsArray(i) = Array(objService.DisplayName, objService.State,objService.StartMode)
i = i + 1
What are we doing in this block of code? Well, in line 1 we're resizing our array, using the command ReDim Preserve to not only resize the array but also to preserve any existing data in that array. (Without the Preserve keyword, the array would be resized, but any data currently in that array would be erased.)
What size are we making the array? The first time through the loop, we're making an array of size 0; in other words, we're creating an array containing one element. And how do we know for certain we're making an array of size 0? Because we're using the counter variable i, and that variable is equal to 0.
So if we're using the variable i to represent the size of the array, doesn't that mean we'll always be setting the size to 0? Well, it would, except that each time we go through the loop we increment the value of i by 1; as you can see, that's what we do in line 3 of our code block.
That leaves us with just one line of code to worry about:
arrResultsArray(i) = Array(objService.DisplayName, objService.State,objService.StartMode)
Here we're simply taking the property values of interest—DisplayName, State, and StartMode—and adding them to the array arrResultsArray. Notice that we don't add the property values individually; instead, we add them as an array of values. Granted, that's a little unusual: it means that each item in the array arrResultsArray will be another array; that's just the way Microsoft.CmdLib works.
Of course, that might cause you to start worrying again. "An array of arrays? How in the world am I supposed to access all the individual values in an array full of arrays?" Don't panic. It's easy: let Microsoft.CmdLib take care of it for you.
Note: sorry, but accessing all the individual values in an array of arrays is about the only thing Microsoft.CmdLib can do for you. However, cleaning your wicked stepmother's chambers, doing dishes, and making clothes for your evil stepsisters are being considered for the next release of the object.
In fact, we can output our data—in neat tabular format—simply by calling the ShowResults method and passing the method all the arrays and variables we configured earlier in the script:
objCmdLib.ShowResults arrHeader, arrResultsArray, arrMaxLength, strFormat, blnPrintHeader, arrBlnHide
What's all that going to look like? It's going to look like the nicely formatted output we hoped for when we looked at Figure 1.
Not bad, eh? You get nice-looking output, and you hardly had to do any work at all. (And your next script will be even easier, because all you'll have to do is make a few minor changes to this first script.)
Now, admittedly, you won't get the same sense of satisfaction that Cinderella got from slaving all day and all night just so she could display script output in a table. And, to be honest, you probably won't get to marry a prince, either; that's not really part of the deal. But that seems like a small price to pay in order to get such nice output from a VBScript script, especially when you consider what princes tend to be like these days.
And who knows: you might get yourself a prince anyway. After all, even royalty needs to get information about all the services installed on their computers, right?

Dr. Scripto's Scripting Perplexer
The monthly challenge that tests not only your puzzle-solving skills, but also your scripting skills.

June 2008: PowerShell Pathway
In each of these puzzles, connect the letters horizontally, vertically, and diagonally to make up the name of a Windows PowerShell cmdlet. Each letter will be used only once. Here's an example:
The cmdlet created in this puzzle is New-Alias. Now it's your turn. Here are three more puzzles:








Show Answer

The Microsoft Scripting Guys work for—well, are employed by—Microsoft. When not playing/coaching/watching baseball (and various other activities), they run the TechNet Script Center. Check it out at www.scriptingguys.com.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.
Page view tracker