January 2005

Article
10/22/2009

SG090301

For a list and additional information on all Tales from the Script columns, click here.

All You Need is Log (Well, Log Parser)

If you’ve watched more than a handful of movies in your life, then you know that Hollywood has a finite set of plots that get reused over and over again. For example, one tried-and-true movie plot features the hero—rich, dashing, and handsome… Hey, you’re right; we Scripting Guys should sue the movie studios; the similarities are way too obvious to be a coincidence. Bentley, get our attorneys on the phone!

But back to our story. Our rich, dashing, and handsome hero is nonetheless lonely and unfulfilled. His enormous wealth means nothing to him; he flits around the world, traveling to exotic locales, battling villains, finding lost archeological treasures, and romancing supermodels. (Ok, so maybe there are some differences between the hero and the Scripting Guys.) But it’s no use. More depressed than ever he slinks home, only to find that the girl of his dreams, his one true love, had been there all the time. (Sometimes she’s the girl next door, sometimes she’s the maid, sometimes she’s his secretary.) He removes her glasses, she lets her hair down, and the final credits begin to roll. Sigh…

Now, to tell you the truth, we Scripting Guys have always scoffed at movies such as this. “How dumb can you be?” we’d guffaw. “I mean, you waste all that time and money and the answer was right there under your nose the whole time? Nobody could be that clueless.”

Lately, however, we’ve come to change our tune a little. Maybe the hero wasn’t so dumb after all; maybe it is possible to overlook something so obvious. For example, maybe the hero has been in possession of an absolutely killer command line and scripting tool for the past couple years (a tool like, say, Log Parser) and didn’t even know it.

Yes, we admit it: we’re just as dumb as the movie hero, though perhaps not quite as rich or handsome. A couple years ago someone placed a tool—Log Parser 2.0—in the Script Center. “What’s that?” we asked. “Some tool that analyzes SQL Server logs or something,” came the reply. Well, to tell you the truth, that didn’t sound very interesting, and it didn’t seem to have anything to do with scripting. So we did what people typically do when faced with the unknown: we ignored it, and assumed it would just go away.

Note. Shouldn’t we have at least looked at the tool to see what it really did? Probably. But if we kept a list of all the things we should have done or should be doing, well….

Avid movie-goers already know how this turns out: Log Parser is actually a really cool little utility, and it solves some pretty thorny scripting problems to boot. We still don’t know what it means to analyze SQL Server logs, but we do know that the latest version of Log Parser (version 2.2) can do things like:

Search through event logs and quickly locate and report back events of interest.
Search through the file system and quickly locate and report back files and folders of interest.
Search through Active Directory and quickly locate and report back objects of interest.
Search through all make and manner of log files. For example, Log Parser can search through logs that use the W3C Extended Log File format. Logs that use this format include: Personal Firewall logs; Microsoft Internet Security and Acceleration (ISA) Server logs; Windows Media Services logs; Exchange Tracking logs; and Simple Mail Transfer Protocol (SMTP) log files.
Search through Internet Information Services (IIS) logs, through Netmon capture files, through the registry—through all sorts of things, quickly and easily.

And that’s not even the half of it. Log Parser uses a true SQL query language, which means you can do aggregate queries. Want to know how many of each event ID type you have in your event logs (e.g., 37 Event 100s, 43 Event 101s, 14 Event 102s)? No problem; Log Parser can do that. And, of course, there’s much, much more. You can use a SQL command such as ORDER BY to sort the returned data any way you want, or TOP to return only the x-most records (for example, the 10 biggest files on your hard disk). You can run Log Parser 2.2 from the command prompt, or you can call it as a scriptable COM object (MSUtil.LogQuery). We’d like to see the girl next door do that.

Ok, fine. But let’s see her display returned data as a graph.

Ok, let’s just forget about the girl next door. (Man, isn’t there anything that girl can’t do?)

By the way, when we say that Log Parser finds things quickly, that isn’t just Microsoft hype. (Um, not that Microsoft is prone to hyping its products or anything.) For example, consider a test computer with these specs:

2.39 GHz processor
512 megabytes RAM
14-gigabyte hard disk with just 1.5 gigabytes of free space (and tens of thousands of files)

Suppose we ask Log Parser to find all the Microsoft PowerPoint files (.ppt files) stored on that hard disk. How long do you suppose it will take Log Parser to track down those files? An hour? Two hours? Six or seven days? Try 14 seconds. And that’s not due to some secret Microsoft cheat code built into PowerPoint files. Try searching for .mp3 files. Four seconds. (There were only a handful of .mp3 files on this machine.) Try searching for .jpg files. Eleven seconds. That’s pretty fast.

In other words, this isn’t just mindless hype: this really is an incredibly useful (and free!) tool that addresses a lot of scenarios the scripting technologies built into Windows don’t address very well. (Besides, we Scripting Guys tend to save the mindless hype for our own tools, like Scriptomatic 2.0.)

Best of all, Log Parser is extremely versatile. If you want to use it as a command-line tool, then use it as a command-line tool. If you’d rather script the use of Log Parser, that’s cool; all of Log Parser’s features are exposed through an Automation object. Because this is a scripting column (yeah, sometimes it’s hard for us to believe that ourselves), we’re going to focus on scripting Log Parser. But if you’d rather use it as a command-line tool, Log Parser has a very extensive help file that will show you how to do that. (Hey, we can’t do everything.)

We encourage you to download Log Parser 2.2 and give it a try (and we apologize to Log Parser’s creator, Gabriele Giuseppini, for overlooking the tool for so long). To help you get started once you’ve grabbed the download, we thought we’d spend some time showing you how to write some simple – yet highly useful – Log Parser scripts. And in order to keep the tasks as generic as possible, we’re going to focus on the file system, something every system administrator has to deal with.

Making Log Parser 2.2 Scriptable

Before you can begin writing Log Parser scripts, you need to register the Log Parser DLL. After you download and extract the Log Parser utility, open a command window in the same folder as Log Parser, and type the following from the command prompt:

regsvr32 LogParser.dll

That’s all you have to do; you’re now ready to write Log Parser scripts.

Sure, go ahead and call your Mom and give her the good news; we’ll wait.

Your First Log Parser Script

So what does a Log Parser script look like? Well, today must be your lucky day, because we just happened to have one to show you. Here’s a Log Parser script that returns the name and size of all the files found in the folder C:\Scripts (and, just for the heck of it, sorts the returned data alphabetically by file name):

Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = _
    CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.recurse = 0

Set objOutputFormat = _
    CreateObject("MSUtil.LogQuery.NativeOutputFormat")
objOutputFormat.rtp = -1

strQuery = "SELECT Name, Size FROM 'C:\Scripts\*.*' ORDER BY Name ASC"
objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

Don’t worry; we’ll explain how each line of code works. And while it might be a bit cryptic-looking right now, trust us: by the end of this column you’ll be able to crank out thousands of scripts just like this one. And if that doesn’t impress the girl next door, well, she doesn’t deserve you anyway.

For the most part, Log Parser scripts consist of five steps. The script must:

Create an instance of the Log Parser object
Create and configure the InputFormat object
Create and configure the OutputFormat object
Create a Log Parser query
Execute the query and return data

Let’s take a look at each of these steps in a little more detail.

Step 1: Creating an instance of the Log Parser object

Yes, we know: “Create an instance of …” always sounds really hard. In truth, though, it’s just a single line of code:

Set objLogParser = CreateObject("MSUtil.LogQuery")

That’s it; run that line of code, and you’ll have an instance of Log Parser available to carry out your every whim.

OK, maybe not that whim. But lots of other whims.

Step 2: Creating and Configuring the “InputFormat” object

As we noted earlier, Log Parser can grab data from all kinds of places: event logs, the registry, log files, Netmon capture files, etc. The InputFormat object simply specifies where you want to get the data from. To grab data from the file system, you use code like this:

Set objInputFormat = _
    CreateObject("MSUtil.LogQuery.FileSystemInputFormat")

This creates an InputFormat object, in this case one designed to work with the file system. All you have to do is create the object; once Log Parser has a copy of the object, it knows what to do with it. In this example Log Parser now knows how to access the file system.

By the way, when it comes to working with the file system, InputFormat has three optional properties you can configure:

Property	Description
recurse	Maximum subdirectory recursion level. 0 disables subdirectory recursion; -1 enables unlimited recursion.
preserveLastAccTime	Preserve the last access time of visited files. Enumerating files and directories causes their last access time to be updated. Setting this parameter to "ON" causes the file system input format to restore the last access time of the files being visited.
useLocalTime	Use local time for timestamp fields. When set to "ON", the values of the "CreationTime", "LastAccessTime", and "LastWriteTime" fields are expressed in local time. When set to "OFF", the values of these fields are expressed in Universal Time Coordinates (UTC) time.

By default, Log Parser recurses all subfolders beginning with the starting point; if you start your search in C:\, then Log Parser will search every folder on drive C. But what if you really do want to search only the root directory (C:\)? In that case, just set the recurse property to 0:

Set objInputFormat = _
    CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.recurse = 0

What if you wanted to only go two levels deep? Well, then:

Set objInputFormat = _
    CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.recurse = 2

To ensure that you get a recursive search of every subfolder, just set the recurse property to -1.

Step 3: Creating and Configuring the “OutputFormat” object

Ok, so if the InputFormat object represents the input format, then the OutputFormat object must—ah, you guessed it. Yes, the OutputFormat object represents the output format for the data returned by Log Parser. Does Log Parser give you lots of choices for outputting data? Of course:

Output Format	Description
Chart	Creates image files containing charts of the output record field values.
CSV	Writes output records as comma-separated values text.
Datagrid	Displays output records in a graphical user interface.
IIS	Writes output records in the Microsoft IIS Log File format.
NAT	Writes output records in a readable tabulated column format.
SQL	Uploads output records to a table in a SQL database.
SysLog	Sends messages to a Syslog server; creates text files containing Syslog messages; sends Syslog messages to users.
TPL	Writes output records formatted according to user-defined templates.
TSV	Writes output records as tab-separated or space-separated values text.
W3C	Writes output records in the W3C Extended Log File format.
XML	Writes output records as XML document nodes.

For now, we’re going to focus on the NAT (native) output, which displays your data in nice, tabular format in the command window. How do we set the OutputFormat object to NAT? Just like this:

Set objOutputFormat = _
    CreateObject("MSUtil.LogQuery.NativeOutputFormat")

Hey, we told you this was easy. Of course, Log Parser also offers a number of options for configuring the output. Here are some of the more interesting ones available for NAT:

Option	Description
rtp	When writing to STDOUT, the NAT output format displays output records in batches made up of the number of rows equal to the value specified for this parameter. Once a batch of rows has been displayed, the NAT output format prompts the user to press a key to display the next batch of rows. Specifying -1 for this parameter disables batching.
headers	This parameter enables or disables the column headers displayed before each batch of output rows.
ralign	When this parameter is set to "ON", the NAT output format aligns values to the right side of each column. When this parameter is set to "OFF", values are aligned to the left side of each column.

rtp

When writing to STDOUT, the NAT output format displays output records in batches made up of the number of rows equal to the value specified for this parameter. Once a batch of rows has been displayed, the NAT output format prompts the user to press a key to display the next batch of rows.

Specifying -1 for this parameter disables batching.

headers

This parameter enables or disables the column headers displayed before each batch of output rows.

ralign

When this parameter is set to "ON", the NAT output format aligns values to the right side of each column.

When this parameter is set to "OFF", values are aligned to the left side of each column.

To use one of these options, just specify the option and the desired value as a property of the OutputFormat object. For example, to right-align your output, use code like this:

Set objOutputFormat = _
    CreateObject("MSUtil.LogQuery.NativeOutputFormat")
objOutputFormat.ralign = "ON"

Step 4: Creating the Log Parser query

This is where the fun begins. Let’s start with a very simple query, one that lists all the files in the folder C:\Scripts (as well as their sizes), and then sorts them in alphabetical order:

strQuery = "SELECT Name, Size FROM 'C:\Scripts\*.*' ORDER BY Name ASC"

If you’re even slightly familiar with SQL then the preceding query shouldn’t faze you in the least. As in most SQL queries, we start by asking for certain properties; in this case, Name and Size. (If we wanted all the properties, we’d use Select * FROM instead.) We then need to tell Log Parser where to look for this information. In a typical SQL query, you’d pass the name of a database table. Because we’re working with the file system, we pass the name of the folder where we want to start our search. Thus ‘C:\Scripts\*.*’.

By the way, if you want to get back a list of all the files in C:\Scripts, make sure you include the *.* wildcard; if you don’t, you’ll get back an error message saying that Log Parser “Cannot find any file or folder matching ‘C:\Scripts’”. What if you’re only interested in, say, the .vbs files? Then use a query that looks like this:

strQuery = "SELECT Name, Size FROM 'C:\Scripts\*.vbs' " & _
    "ORDER BY Name ASC"

If you wanted to search drive C for one file in particular, just add a WHERE clause:

strQuery = "SELECT Name, Size FROM 'C:\*.*' WHERE " & _
    "NAME = 'inventory.vbs' ORDER BY Name ASC"

Ok, some of you look a bit puzzled: how did we know that Name and Size were properties we could include in our query? That’s easy: we cheated. We looked in the Log Parser help file and checked the fields available to the file system object. Those fields are shown in the following table.

Field	Description
Path	Full path of the file or directory.
Name	Name of the file or directory.
Size	Size of the file, in bytes.
Attributes	Attributes of the file or directory. File attributes include: • Archive • Compressed • Directory • Encrypted • Hidden • Offline • ReadOnly • System • Temporary
CreationTime	Date and time at which the file or directory has been created (local or UTC time, depending on the value of the useLocalTime parameter). The CreationTime will look similar to this: 6/10/2004 1:29:50 PM.
LastAccessTime	Date and time at which the file or directory has been last accessed (local or UTC time, depending on the value of the useLocalTime parameter). The LastAccessTime will look similar to this: 6/10/2004 1:29:50 PM.
LastWriteTime	Date and time at which the file or directory has been last modified (local or UTC time, depending on the value of the useLocalTime parameter). The LastWriteTime will look similar to this: 6/10/2004 1:29:50 PM.
FileVersion	Version of the file. This is typically found only with executable files and .dll files.
ProductVersion	Version of the product the file is distributed with.
InternalName	Internal name of the file.
ProductName	Name of the product the file is distributed with.
CompanyName	Name of the vendor company that produced the file.
LegalCopyright	Copyright notices that apply to the file.
LegalTrademarks	Trademarks and registered trademarks that apply to the file.
PrivateBuild	Private version information of the file.
SpecialBuild	Special file build notes.
Comments	Comments associated with the file.
FileDescription	Description of the file.
OriginalFilename	Original name of the file.

Step 5: Executing the Query and Returning Data

At this point all you have to do is execute the query and watch as Log Parser starts returning the data. That takes just one line of code:

objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

As you can see, you call the ExecuteBatch method and pass it three parameters: the variable holding your SQL query (strQuery); the object reference to your InputFormat object (objInputFormat); and the object reference to your output format (objOutputFormat). And what’s going to happen then? Well, you should get back output similar to this:

Note. The elapsed time for this script: one second.

All in all, pretty good. Still, you might notice that the first two entries in our list are dot (.) and dot dot (..). What’s the deal there? Well, if you remember the good old days of MS-DOS, the single dot is shorthand notation for the current directory, and the double dot is shorthand for the parent directory. That’s nice, but neither one of these are actually files, so we might not want them cluttering up our output. (Incidentally, this isn’t an artifact of these two shorthand methods; if this folder had any subfolders, the names of those subfolders would show up in our output as well.)

So can we get rid of folders and subfolders and show only file names in our output? You bet. As it turns out, folders have an attribute (Directory) that the file system uses to distinguish between files and folders. All we have to do is change our query to weed out anything that has the Directory attribute. In other words, we use a query like this:

strQuery = "SELECT Name, Size FROM 'C:\Scripts\*.*' WHERE NOT " & _
    "Attributes LIKE '%D%' ORDER BY Name"

A little awkward (hey, it’s SQL), but the WHERE NOT Attributes simply means, “Where the Attribute is not enabled.” In other words, if the directory attribute is enabled, then this baby is a folder, and we don’t want it.

By the way, we use the LIKE operator to test whether or not the attribute in question begins with the letter D. If it does, then this is a directory. To check for hidden files, we use LIKE ‘%H%’; to check for encrypted files, we use LIKE ‘%E%’; and so on.

Note. This isn’t necessarily the universal way to check for file and folder attributes, but it is the way Log Parser checks attributes. Likewise, the LIKE operator is used a bit differently than the way LIKE is used in typical SQL queries. In standard SQL, LIKE ‘%E%’ returns any attribute that has the letter e somewhere in the attribute name. In Log Parser, however, this returns only encrypted files; that is, only attributes that begin with the letter e.

Here’s what our revised script looks like:

Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.Recurse = 0

Set objOutputFormat = CreateObject("MSUtil.LogQuery.NativeOutputFormat")
objOutputFormat.rtp = -1

strQuery = "SELECT Name, Size FROM 'C:\Scripts\*.*' WHERE NOT " & _
    "Attributes LIKE '%D%' ORDER BY Name"
objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

And here’s the new output:

Much better, but still not perfect. For example, take a look at the way the files are sorted:

Copy of kix_wmi.kix
New Text Document.txt
New_user.sxls
Process.ppt
Script1.vbs
bookmarkdoc.doc
computers.txt

What the…? If this is sorted in alphabetical order, then what are bookmarkdoc.doc and computers.txt doing coming after Script1.vbs?

Well, what’s happening is that Log Parser is sorting on the ASCII values of the characters found in the file name. In the ASCII system, all the uppercase letters come before all the lowercase letters. Because of that, a file named ZZZZZZZZ.doc is going to come before a file named aaaaaaaa.doc. Sad, but true.

So do we have to go through and rename all our files just to get things sorted alphabetically in Log Parser? Yep, too bad. Have fun.

No, hey, we’re just giving you a bad time; this is easy enough to correct. As it turns out, Log Parser has a vast array of functions that allow you to manipulate the data as you retrieve it. For example, our collection of files would all be sorted alphabetically provided we used nothing but lowercase letters in the file names. So why not just ask Log Parser to treat all letters as lowercase? Take a look at this query:

strQuery = "SELECT TO_LOWERCASE (Name) AS NewName, Size FROM " & _
    "'C:\Scripts\*.*' WHERE NOT Attributes LIKE '%D%' ORDER BY NewName"

What we’re doing here is using the TO_LOWERCASE function to convert all the file names to lowercase. (Note that this is just for Log Parser’s use; the actual file names won’t change.) Again, the syntax might seem a bit odd (unless you’ve done some SQL coding), but basically all we’re saying is this: instead of selecting the Name property, use the TO_LOWERCASE function to convert the Name property to all lowercase letters. And because this essentially creates a new property (it’s no longer Name, it’s now converted-to-lowercase-Name), we use the AS operator and assign this property a new name. (We came up with the very clever NewName, although you can use any name you want). The net result is that Log Parser will grab all the file names, convert them to lowercase, and then do the sorting.

Here’s our new and improved script:

Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.Recurse = 0

Set objOutputFormat = CreateObject("MSUtil.LogQuery.NativeOutputFormat")
objOutputFormat.rtp = -1

strQuery = "SELECT TO_LOWERCASE (Name) AS NewName, Size FROM " & _
    "'C:\Scripts\*.*' WHERE NOT Attributes LIKE '%D%' ORDER BY NewName"
objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

And here’s our output:

You’re right: it doesn’t get any better than this. Being able to sort files alphabetically regardless of case ought to be enough romance and adventure for anyone.

Retrieving a Collection of Files

The ExecuteBatch method executes a script and then displays/saves the output in a command window, an HTML file, a SQL database, whatever. That’s great, provided all you want to do is look at/save the output. But what if you have a different chore in mind, what if, say, you want to find the 10 oldest files on your computer so that you can archive them? What if you want to find all the files larger than 5 megabytes so you can compress them? In cases like that, you don’t really want the returned data saved to a text file or displayed as a graph. Instead, you’d like to have that data held in memory, so you can actually do something with it.

We won’t bother to ask “Can you do this with Log Parser?” By now you already know the answer. So let’s take a look at a Log Parser script that instead of directly displaying information grabs that data and holds it in a recordset in memory:

Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = _
    CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.recurse = 0

strQuery = "SELECT TO_LOWERCASE (Name) AS NewName, Size FROM " & _
    "'C:\Scripts\*.*' WHERE NOT Attributes LIKE '%D%' ORDER BY NewName"

Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

Do While Not objRecordSet.AtEnd
   Set objRecord = objRecordSet.GetRecord
   Wscript.Echo "Name: " & objRecord.GetValue("NewName")
   Wscript.Echo "File size: " & objRecord.GetValue("Size")
   objRecordSet.MoveNext
Loop

See, that’s not so bad. In the first half of the script, there are really only two differences between this script and the script that displayed data directly to the command window. First, you might notice that we didn’t create an OutputFormat object. That’s because we aren’t using an OutputFormat object; instead of outputting the data, we’re going to hold it in memory.

Second, we don’t use the ExecuteBatch method. Instead, we call the Execute method, passing two parameters: the query we’re using (strQuery) and the InputFormat object (objInputFormat). That’s what this line of code does:

Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

After this line of code executes, we’ll have a list of all the files found in C:\Scripts; the difference is that those files won’t be echoed to the command window. Instead, this data will be held in memory in a recordset named objRecordSet. If we want to access that data, all we have to do is walk through the recordset. That’s what we do in the second half of the script:

Do While Not objRecordSet.AtEnd
   Set objRecord = objRecordSet.GetRecord
   Wscript.Echo "Name: " & objRecord.GetValue("NewName")
   Wscript.Echo "File size: " & objRecord.GetValue("Size")
   objRecordSet.MoveNext
   Loop

Here we create a Do While loop that continues to loop until we reach the end of the recordset (objRecordSet.AtEnd). Inside the loop, we use the GetRecord method to grab the first record in the recordset. We use the GetValue method to grab and echo the value of the NewName field, then do the same thing with the Size field. And that’s it. The MoveNext method moves us the next record, and we repeat the process. When we’re all done, we end up with output like this:

Cool, huh? Now, admittedly, simply echoing back values isn’t the most exciting use of the recordset capability, but it does show you how you can grab data and then do with it as you wish.

As you can see, Log Parser is pretty handy when it comes to retrieving information about files and folders. But remember, Log Parser is far more than just a fancy dir command; it can grab similar information from event logs, Active Directory, various make and manner of log files, and so on. We haven’t even skimmed the surface of what Log Parser can do. Over the next month or so, we’ll see if we can get a bunch of sample Log Parser scripts (and queries) posted to the Script Center. In the meantime, the Log Parser download includes a number of examples that should help you get going on your own.

Now, if you’ll excuse us, we’re going to go see who actually does live next door. Hey, you never know, right?