Geeky Bob

It seems that I have always had a difficult time explaining to people what I do at Microsoft. It's not that I'm unsure about what I do - the details of my job have always been crystal-clear to me and I love what I am doing. It's just that I can't find a way to explain things in a way that doesn't result in blank stares from anyone who isn't a geek. (This problem isn't limited to me, though; my non-technical wife simply responds "I have no idea what he does" when someone asks her what I do for a living.)

Here's a perfect example: when I was a Program Manager on the Internet Information Services (IIS) team, people would often ask me what I did for Microsoft, and I would reply with something like, "I help design and implement the web publishing protocols for Microsoft's web server."

Other Person: [Blank Stare]

I would attempt to remedy the situation by adding, "You know, I design Microsoft's implementation of Internet technologies like the File Transfer Protocol, WebDAV, and the FrontPage Server Extensions."

Other Person: [Blank Stare]

In a sometimes-futile effort to salvage the conversation from complete disaster, I would interject, "You like to use the Internet, right? Well, your computer is on one side of the Internet, and my team helps build the other side of the Internet. That's kind of what I do."

That comment would usually be met with a slight spark of recognition, which was sometimes followed by a half-muttered, "That's nice."

At one time or other during my tenure as a Program Manager on the IIS team I was responsible for a smattering of disparate technologies; things like FTP, WebDAV, FPSE, FastCGI, PHP, URL Rewrite, IIS Express, Log Parser, etc. Most of those technologies garnered little to no interest for the average person, and many of my coworkers found them pretty boring as well. Just the same, I personally found every one of those technologies completely fascinating. (Why else would I spend eight years trying to get just one new command added to FTP?)

A couple of years ago I left the IIS program management team and I joined the writing team which is responsible for documenting Microsoft's ASP.NET framework; and if you have to ask what that means, then you are probably not interested in the answer.

Still, people would ask me what I do for Microsoft, and I would try to explain my job with statements like, "I document the Application Programming Interfaces (or APIs) for Microsoft's ASP.NET."

Other Person: [Blank Stare]

I would try to nudge the conversation along by saying things like, "I help people write web code."

Other Person: [Blank Stare]

Skipping ahead in the conversation, I would usually make a last-ditch attempt by stating, "Let's say you wanted to create a website; if so, you might read something that I wrote in order to help you get started."

Sometimes this remark would illicit a hint of acknowledgment, but usually I just got another blank stare.

This leads me to a few days ago. My wife and I were at dinner, and a waiter asked me what I did for a living. In the back of my mind I started to say something like, "Well, these days I'm documenting a set of APIs that Java programmers will use with Microsoft Azure technologies [blah blah blah]..."

But what actually came out of my mouth was, "I could explain it to you, but I'm pretty sure you wouldn't want me to. Trust me."

I like that answer. I think I'll stick with it in the future. :-)

In Part 6 of this series, I showed how to create a very basic COM-based input format provider for Log Parser. I wrote that blog post as a follow-up to an earlier blog post where I had written a more complex COM-based input format provider for Log Parser that worked with FTP RSCA events. My original blog post had resulted in several requests for me to write some easier examples about how to get started writing COM-based input format providers for Log Parser, and those appeals led me to write my last blog post:

Advanced Log Parser Part 6 - Creating a Simple Custom Input Format Plug-In

The example in that blog post simply returns static data, which was the easiest example that I could demonstrate.

For this follow-up blog post, I will illustrate how to create a simple COM-based input format plug-in for Log Parser that you can use as a generic provider for consuming data in text-based log files. Please bear in mind that this is just an example to help developers get started writing their own COM-based input format providers; you might be able to accomplish some of what I will demonstrate in this blog post by using the built-in Log Parser functionality. That being said, this still seems like the best example to help developers get started because consuming data in text-based log files was the most-often-requested example that I received.

In Review: Creating COM-based plug-ins for Log Parser

In my earlier blog posts, I mentioned that a COM plug-in has to support several public methods. You can look at those blog posts when you get the chance, but it is a worthwhile endeavor for me to copy the following information from those blog posts since it is essential to understanding how the code sample in this blog post is supposed to work.

Once you have created and registered a COM-based input format plug-in, you call it from Log Parser by using something like the following syntax:

logparser.exe "SELECT * FROM FOO" -i:COM -iProgID:BAR

In the preceding example, FOO is a data source that makes sense to your plug-in, and BAR is the COM class name for your plug-in.

Creating a Generic COM plug-in for Log Parser

As I have done in my previous two blog posts about creating COM-based input format plug-ins, I'm going to demonstrate how to create a COM component by using a scriptlet since no compilation is required. This generic plug-in will parse any text-based log files where records are delimited by CRLF sequences and fields/columns are delimited by a separator that is defined as a constant in the code sample.

To create the sample COM plug-in, copy the following code into a text file, and save that file as "Generic.LogParser.Scriptlet.sct" to your computer. (Note: The *.SCT file extension tells Windows that this is a scriptlet file.)

<SCRIPTLET>
  <registration
    Description="Simple Log Parser Scriptlet"
    Progid="Generic.LogParser.Scriptlet"
    Classid="{4e616d65-6f6e-6d65-6973-526f62657274}"
    Version="1.00"
    Remotable="False" />
  <comment>
  EXAMPLE: logparser "SELECT * FROM 'C:\foo\bar.log'" -i:COM -iProgID:Generic.LogParser.Scriptlet
  </comment>
  <implements id="Automation" type="Automation">
    <method name="OpenInput">
      <parameter name="strFileName"/>
    </method>
    <method name="GetFieldCount" />
    <method name="GetFieldName">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="GetFieldType">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="ReadRecord" />
    <method name="GetValue">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="CloseInput">
      <parameter name="blnAbort"/>
    </method>
  </implements>
  <SCRIPT LANGUAGE="VBScript">

Option Explicit

' Define the column separator in the log file.
Const strSeparator = "|"

' Define whether the first row contains column names.
Const blnHeaderRow = True

' Define the field type constants.
Const TYPE_INTEGER   = 1
Const TYPE_REAL      = 2
Const TYPE_STRING    = 3
Const TYPE_TIMESTAMP = 4
Const TYPE_NULL      = 5

' Declare variables.
Dim objFSO, objFile, blnFileOpen
Dim arrFieldNames, arrFieldTypes
Dim arrCurrentRecord

' Indicate that no file has been opened.
blnFileOpen = False

' --------------------------------------------------------------------------------
' Open the input session.
' --------------------------------------------------------------------------------

Public Function OpenInput(strFileName)
    Dim tmpCount
    ' Test for a file name.
    If Len(strFileName)=0 Then
        ' Return a status that the parameter is incorrect.
        OpenInput = 87
        blnFileOpen = False
    Else
        ' Test for single-quotes.
        If Left(strFileName,1)="'" And Right(strFileName,1)="'" Then
            ' Strip the single-quotes from the file name.
            strFileName = Mid(strFileName,2,Len(strFileName)-2)
        End If
        ' Open the file system object.
        Set objFSO = CreateObject("Scripting.Filesystemobject")
        ' Verify that the specified file exists.
        If objFSO.FileExists(strFileName) Then
            ' Open the specified file.
            Set objFile = objFSO.OpenTextFile(strFileName,1,False)
            ' Set a flag to indicate that the specified file is open.
            blnFileOpen = true
            ' Retrieve an initial record.
            Call ReadRecord()
            ' Redimension the array of field names.
            ReDim arrFieldNames(UBound(arrCurrentRecord))
            ' Loop through the record fields.
            For tmpCount = 0 To (UBound(arrFieldNames))
                ' Test for a header row.
                If blnHeaderRow = True Then
                    arrFieldNames(tmpCount) = arrCurrentRecord(tmpCount)
                Else
                    arrFieldNames(tmpCount) = "Field" & (tmpCount+1)
                End If
            Next
            ' Test for a header row.
            If blnHeaderRow = True Then
                ' Retrieve a second record.
                Call ReadRecord()
            End If
            ' Redimension the array of field types.
            ReDim arrFieldTypes(UBound(arrCurrentRecord))
            ' Loop through the record fields.
            For tmpCount = 0 To (UBound(arrFieldTypes))
                ' Test if the current field contains a date.
                If IsDate(arrCurrentRecord(tmpCount)) Then
                    ' Specify the field type as a timestamp.
                    arrFieldTypes(tmpCount) = TYPE_TIMESTAMP
                ' Test if the current field contains a number.
                ElseIf IsNumeric(arrCurrentRecord(tmpCount)) Then
                    ' Test if the current field contains a decimal.
                    If InStr(arrCurrentRecord(tmpCount),".") Then
                        ' Specify the field type as a real number.
                        arrFieldTypes(tmpCount) = TYPE_REAL
                    Else
                        ' Specify the field type as an integer.
                        arrFieldTypes(tmpCount) = TYPE_INTEGER
                    End If
                ' Test if the current field is null.
                ElseIf IsNull(arrCurrentRecord(tmpCount)) Then
                    ' Specify the field type as NULL.
                    arrFieldTypes(tmpCount) = TYPE_NULL
                ' Test if the current field is empty.
                ElseIf IsEmpty(arrCurrentRecord(tmpCount)) Then
                    ' Specify the field type as NULL.
                    arrFieldTypes(tmpCount) = TYPE_NULL
                ' Otherwise, assume it's a string.
                Else
                    ' Specify the field type as a string.
                    arrFieldTypes(tmpCount) = TYPE_STRING
                End If
            Next
            ' Temporarily close the log file.
            objFile.Close
            ' Re-open the specified file.
            Set objFile = objFSO.OpenTextFile(strFileName,1,False)
            ' Test for a header row.
            If blnHeaderRow = True Then
                ' Skip the first row.
                objFile.SkipLine
            End If
            ' Return success status.
            OpenInput = 0
        Else
            ' Return a file not found status.
            OpenInput = 2
        End If
    End If
End Function

' --------------------------------------------------------------------------------
' Close the input session.
' --------------------------------------------------------------------------------

Public Function CloseInput(blnAbort)
    ' Free the objects.
    Set objFile = Nothing
    Set objFSO = Nothing
    ' Set a flag to indicate that the specified file is closed.
    blnFileOpen = False
End Function

' --------------------------------------------------------------------------------
' Return the count of fields.
' --------------------------------------------------------------------------------

Public Function GetFieldCount()
    ' Specify the default value.
    GetFieldCount = 0
    ' Test if a file is open.
    If (blnFileOpen = True) Then
        ' Test for the number of field names.
        If UBound(arrFieldNames) > 0 Then
            ' Return the count of fields.
            GetFieldCount = UBound(arrFieldNames) + 1
        End If
    End If
End Function

' --------------------------------------------------------------------------------
' Return the specified field's name.
' --------------------------------------------------------------------------------

Public Function GetFieldName(intFieldIndex)
    ' Specify the default value.
    GetFieldName = Null
    ' Test if a file is open.
    If (blnFileOpen = True) Then
        ' Test if the index is valid.
        If intFieldIndex<=UBound(arrFieldNames) Then
            ' Return the specified field name.
            GetFieldName = arrFieldNames(intFieldIndex)
        End If
    End If
End Function

' --------------------------------------------------------------------------------
' Return the specified field's type.
' --------------------------------------------------------------------------------

Public Function GetFieldType(intFieldIndex)
    ' Specify the default value.
    GetFieldType = Null
    ' Test if a file is open.
    If (blnFileOpen = True) Then
        ' Test if the index is valid.
        If intFieldIndex<=UBound(arrFieldTypes) Then
            ' Return the specified field type.
            GetFieldType = arrFieldTypes(intFieldIndex)
        End If
    End If
End Function

' --------------------------------------------------------------------------------
' Return the specified field's value.
' --------------------------------------------------------------------------------

Public Function GetValue(intFieldIndex)
    ' Specify the default value.
    GetValue = Null
    ' Test if a file is open.
    If (blnFileOpen = True) Then
        ' Test if the index is valid.
        If intFieldIndex<=UBound(arrCurrentRecord) Then
            ' Return the specified field value based on the field type.
            Select Case arrFieldTypes(intFieldIndex)
                Case TYPE_INTEGER:
                    GetValue = CInt(arrCurrentRecord(intFieldIndex))
                Case TYPE_REAL:
                    GetValue = CDbl(arrCurrentRecord(intFieldIndex))
                Case TYPE_STRING:
                    GetValue = CStr(arrCurrentRecord(intFieldIndex))
                Case TYPE_TIMESTAMP:
                    GetValue = CDate(arrCurrentRecord(intFieldIndex))
                Case Else
                    GetValue = Null
            End Select
        End If
    End If
End Function
  
' --------------------------------------------------------------------------------
' Read the next record, and return true or false if there is more data.
' --------------------------------------------------------------------------------

Public Function ReadRecord()
    ' Specify the default value.
    ReadRecord = False
    ' Test if a file is open.
    If (blnFileOpen = True) Then
        ' Test if there is more data.
        If objFile.AtEndOfStream Then
            ' Flag the log file as having no more data.
            ReadRecord = False
        Else
            ' Read the current record.
            arrCurrentRecord = Split(objFile.ReadLine,strSeparator)
            ' Flag the log file as having more data to process.
            ReadRecord = True
        End If
    End If
End Function

  </SCRIPT>

</SCRIPTLET>

After you have saved the scriptlet code to your computer, you register it by using the following syntax:

regsvr32 Generic.LogParser.Scriptlet.sct

At the very minimum, you can now use the COM plug-in with Log Parser by using syntax like the following:

logparser "SELECT * FROM 'C:\Foo\Bar.log'" -i:COM -iProgID:Generic.LogParser.Scriptlet

Next, let's analyze what this sample does.

Examining the Generic Scriptlet in Detail

Here are the different parts of the scriptlet and what they do:

• The <registration> section of the scriptlet sets up the COM registration information; you'll notice the COM component class name and GUID, as well as version information and a general description. (Note that you should generate your own GUID for each scriptlet that you create.)
• The <implements> section declares the public methods that the COM plug-in has to support.
• The <script>section contains the actual implementation:
  • The first part of the script section declares the global variables that will be used:
    • The strSeparator  constant defines the delimiter that is used to separate the data between fields/columns in a text-based log file.
    • The blnHeaderRow  constant defines whether the first row in a text-based log file contains the names of the fields/columns:
      • If set to True, the plug-in will use the data in the first line of the log file to name the fields/columns.
      • If set to False, the plug-in will define generic field/column names like "Field1", "Field2", etc.
  • The second part of the script contains the required methods:
    • The OpenInput()  method performs several tasks:
      • Locates and opens the log file that you specify in your SQL statement, or returns an error if the log file cannot be found.
      • Determines the number, names, and data types of fields/columns in the log file.
    • The CloseInput()  method cleans up the session by closing the log file and destroying objects.
    • The GetFieldCount()  method returns the number of fields/columns in the log file.
    • The GetFieldName()  method returns the name of a field/column in the log file.
    • The GetFieldType()  method returns the data type of a field/column in the log file. As a reminder, Log Parser supports the following five data types for COM plug-ins: TYPE_INTEGER, TYPE_REAL, TYPE_STRING, TYPE_TIMESTAMP, and TYPE_NULL.
    • The GetValue()  method returns the data value of a field/column in the log file.
    • The ReadRecord()  method moves to the next line in the log file. This method returns True if there is additional data to read, or False when the end of data is reached.

Next, let's look at how to use the sample.

Using the Generic Scriptlet with Log Parser

As a sample log file for this blog, I'm going to use the data in the Sample XML File (books.xml) from MSDN. By running a quick Log Parser query that I will show later, I was able to export data from the XML file into text file named "books.log" that represents an example of a simple log file format that I have had to work with in the past:

id|publish_date|author|title|price
bk101|2000-10-01|Gambardella, Matthew|XML Developer's Guide|44.950000
bk102|2000-12-16|Ralls, Kim|Midnight Rain|5.950000
bk103|2000-11-17|Corets, Eva|Maeve Ascendant|5.950000
bk104|2001-03-10|Corets, Eva|Oberon's Legacy|5.950000
bk105|2001-09-10|Corets, Eva|The Sundered Grail|5.950000
bk106|2000-09-02|Randall, Cynthia|Lover Birds|4.950000
bk107|2000-11-02|Thurman, Paula|Splish Splash|4.950000
bk108|2000-12-06|Knorr, Stefan|Creepy Crawlies|4.950000
bk109|2000-11-02|Kress, Peter|Paradox Lost|6.950000
bk110|2000-12-09|O'Brien, Tim|Microsoft .NET: The Programming Bible|36.950000
bk111|2000-12-01|O'Brien, Tim|MSXML3: A Comprehensive Guide|36.950000
bk112|2001-04-16|Galos, Mike|Visual Studio 7: A Comprehensive Guide|49.950000

In this example, the data is pretty easy to understand - the first row contains the list of field/column names, and the fields/columns are separated by the pipe ("|") character throughout the log file. That being said, you could easily change my sample code to use a different delimiter that your custom log files use.

With that in mind, let's look at some Log Parser examples.

Example #1: Retrieving Data from a Custom Log

The first thing that you should try is to simply retrieve data from your custom plug-in, and the following query should serve as an example:

logparser "SELECT * FROM 'C:\sample\books.log'" -i:COM -iProgID:Generic.LogParser.Scriptlet

The above query will return results like the following:

While the above example works a good proof-of-concept for functionality, it's not overly useful, so let's look at additional examples.

Example #2: Reformatting Log File Data

Once you have established that you can retrieve data from your custom plug-in, you can start taking advantage of Log Parser's features to process your log file data. In this example, I will use several of the built-in functions to reformat the data:

logparser "SELECT id AS ID, TO_DATE(publish_date) AS Date, author AS Author, SUBSTR(title,0,20) AS Title, STRCAT(TO_STRING(TO_INT(FLOOR(price))),SUBSTR(TO_STRING(price),INDEX_OF(TO_STRING(price),'.'),3)) AS Price FROM 'C:\sample\books.log'" -i:COM -iProgID:Generic.LogParser.Scriptlet

The above query will return results like the following:

This example reformats the dates and prices a little nicer, and it truncates the book titles at 20 characters so they fit a little better on some screens.

Example #3: Processing Log File Data

In addition to simply reformatting your data, you can use Log Parser to group, sort, count, total, etc., your data. The following example illustrates how to use Log Parser to count the number of books by author in the log file:

logparser "SELECT author AS Author, COUNT(Title) AS Books FROM 'C:\sample\books.log' GROUP BY Author ORDER BY Author" -i:COM -iProgID:Generic.LogParser.Scriptlet

The above query will return results like the following:

The results are pretty straight-forward: Log Parser parses the data and presents you with a list of alphabetized authors and the total number of books that were written by each author.

Example #4: Creating Charts

You can also use data from your custom log file to create charts through Log Parser. If I modify the above example, all that I need to do is add a few parameters to create a chart:

logparser "SELECT author AS Author, COUNT(Title) AS Books INTO Authors.gif FROM 'C:\sample\books.log' GROUP BY Author ORDER BY Author" -i:COM -iProgID:Generic.LogParser.Scriptlet -fileType:GIF -groupSize:800x600 -chartType:Pie -categories:OFF -values:ON -legend:ON

The above query will create a chart like the following:

I admit that it's not a very pretty-looking chart - you can look at the other posts in my Log Parser series for some examples about making Log Parser charts more interesting.

Summary

In this blog post and my last post, I have illustrated a few examples that should help developers get started writing their own custom input format plug-ins for Log Parser. As I mentioned in each of the blog posts where I have used scriptlets for the COM objects, I would typically use C# or C++ to create a COM component, but using a scriptlet is much easier for demos because it doesn't require installing Visual Studio and compiling a DLL.

There is one last thing that I would like to mention before I finish this blog; I mentioned earlier that I had used Log Parser to reformat the sample Books.xml file into a generic log file that I could use for the examples in this blog. Since Log Parser supports XML as an input format and it allows you to customize your output, I wrote the following simple Log Parser query to reformat the XML data into a format that I had often seen used for text-based log files:

logparser.exe "SELECT id,publish_date,author,title,price INTO books.log FROM books.xml" -i:xml -o:tsv -headers:ON -oSeparator:"|"

Actually, this ability to change data formats is one of the hidden gems of Log Parser; I have often used Log Parser to change the data from one type of log file to another - usually so that a different program can access the data. For example, if you were given the log file with a pipe ("|") delimiter like I used as an example, you could easily use Log Parser to convert that data into the CSV format so you could open it in Excel:

logparser.exe "SELECT id,publish_date,author,title,price INTO books.csv FROM books.log" -i:tsv -o:csv -headers:ON -iSeparator:"|" -oDQuotes:on

I hope these past few blog posts help you to get started writing your own custom input format plug-ins for Log Parser.

That's all for now. ;-)

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

In Part 4 of this series, I illustrated how to create a new COM-based input provider for Log Parser from a custom input format:

Advanced Log Parser Charts Part 4 - Adding Custom Input Formats

For the sample that I published in that blog, I wrote a plug-in that consumed FTP RSCA events, which is highly structured data, and it added a lot of complexity to my example. In the past ten months or so since I published my original blog, I've had several requests for additional information about how to get started writing COM-based input formats for Log Parser, so it occurred to me that perhaps I could have shown a simpler example to get people started instead of diving straight into parsing RSCA data. ;-)

With that in mind, I thought that I would write a couple of blog posts with simpler examples to help anyone who wants to get started writing custom input formats for Log Parser.

For this blog post, I will show you how to create a very basic COM-based input format provider for Log Parser that simply returns static data; you could use this sample as a template to quickly get up-and-running with the basic concepts. (I promise to follow this blog with another real-world example that is still easier-to-use than my RSCA example.)

A Reminder about Creating COM-based plug-ins for Log Parser

In the blog that I referred to earlier, I mentioned that a COM plug-in has to support the following public methods:

Once you have created and registered a COM plug-in, you call it by using something like the following syntax:

logparser.exe "SELECT * FROM FOO" -i:COM -iProgID:BAR

In the preceding example, FOO is a data source that makes sense to your plug-in, and BAR is the COM class name for your plug-in.

Creating a Simple COM plug-in for Log Parser

Once again, I'm going to demonstrate how to create a COM component by using a scriptlet, which I like to use for demos because they are quick to design, they're easily portable, and updates take place immediately since no compilation is required. (All of that being said, if I were writing a real COM plug-in for Log Parser, I would use C# or C++.)

To create the sample COM plug-in, copy the following code into a text file, and save that file as "Simple.LogParser.Scriptlet.sct" to your computer. (Note: The *.SCT file extension tells Windows that this is a scriptlet file.)

<SCRIPTLET>
  <registration
    Description="Simple Log Parser Scriptlet"
    Progid="Simple.LogParser.Scriptlet"
    Classid="{4e616d65-6f6e-6d65-6973-526f62657274}"
    Version="1.00"
    Remotable="False" />
  <comment>
  EXAMPLE: logparser "SELECT * FROM FOOBAR" -i:COM -iProgID:Simple.LogParser.Scriptlet
  </comment>
  <implements id="Automation" type="Automation">
    <method name="OpenInput">
      <parameter name="strValue"/>
    </method>
    <method name="GetFieldCount" />
    <method name="GetFieldName">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="GetFieldType">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="ReadRecord" />
    <method name="GetValue">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="CloseInput">
      <parameter name="blnAbort"/>
    </method>
  </implements>
  <SCRIPT LANGUAGE="VBScript">

Option Explicit

Const MAX_RECORDS = 5
Dim intRecordCount

' --------------------------------------------------------------------------------
' Open the input session.
' --------------------------------------------------------------------------------

Public Function OpenInput(strValue)
    intRecordCount = 0
End Function

' --------------------------------------------------------------------------------
' Close the input session.
' --------------------------------------------------------------------------------

Public Function CloseInput(blnAbort)
End Function

' --------------------------------------------------------------------------------
' Return the count of fields.
' --------------------------------------------------------------------------------

Public Function GetFieldCount()
    GetFieldCount = 5
End Function

' --------------------------------------------------------------------------------
' Return the specified field's name.
' --------------------------------------------------------------------------------

Public Function GetFieldName(intFieldIndex)
    Select Case CInt(intFieldIndex)
        Case 0:
            GetFieldName = "INTEGER"
        Case 1:
            GetFieldName = "REAL"
        Case 2:
            GetFieldName = "STRING"
        Case 3:
            GetFieldName = "TIMESTAMP"
        Case 4:
            GetFieldName = "NULL"
        Case Else
            GetFieldName = Null
    End Select
End Function

' --------------------------------------------------------------------------------
' Return the specified field's type.
' --------------------------------------------------------------------------------

Public Function GetFieldType(intFieldIndex)
    ' Define the field type constants.
    Const TYPE_INTEGER   = 1
    Const TYPE_REAL      = 2
    Const TYPE_STRING    = 3
    Const TYPE_TIMESTAMP = 4
    Const TYPE_NULL      = 5
    Select Case CInt(intFieldIndex)
        Case 0:
            GetFieldType = TYPE_INTEGER
        Case 1:
            GetFieldType = TYPE_REAL
        Case 2:
            GetFieldType = TYPE_STRING
        Case 3:
            GetFieldType = TYPE_TIMESTAMP
        Case 4:
            GetFieldType = TYPE_NULL
        Case Else
            GetFieldType = Null
    End Select
End Function

' --------------------------------------------------------------------------------
' Return the specified field's value.
' --------------------------------------------------------------------------------

Public Function GetValue(intFieldIndex)
    Select Case CInt(intFieldIndex)
        Case 0:
            GetValue = 1
        Case 1:
            GetValue = 1.0
        Case 2:
            GetValue = "One"
        Case 3:
            GetValue = Now
        Case Else
            GetValue = Null
    End Select
End Function
  
' --------------------------------------------------------------------------------
' Read the next record, and return true or false if there is more data.
' --------------------------------------------------------------------------------

Public Function ReadRecord()
    intRecordCount = intRecordCount + 1
    If intRecordCount <= MAX_RECORDS Then
        ReadRecord = True
    Else
        ReadRecord = False
    End If
End Function

  </SCRIPT>

</SCRIPTLET>

After you have saved the scriptlet code to your computer, you register it by using the following syntax:

regsvr32 Simple.LogParser.Scriptlet.sct

At the very minimum, you can now use the COM plug-in with Log Parser by using syntax like the following:

logparser "SELECT * FROM FOOBAR" -i:COM -iProgID:Simple.LogParser.Scriptlet

This will return results like the following:

Next, let's analyze what this sample does.

Examining the Sample Scriptlet Contents in Detail

Here are the different parts of the scriptlet and what they do:

• The <registration> section of the scriptlet sets up the COM registration information; you'll notice the COM component class name and GUID, as well as version information and a general description. (Note that you should generate your own GUID for each scriptlet that you create.)
• The <implements> section declares the public methods that the COM plug-in has to support.
• The <script>section contains the actual implementation:
  • The OpenInput() method opens your data source, although in this example it only initializes the record count. (Note that the value that is passed to the method will be ignored in this example.)
  • The CloseInput() method would normally clean up your session, (e.g. close a data file or database, etc.), but it doesn't do anything in this example.
  • The GetFieldCount() method returns the number of data fields in each record of your data, which is static in this example.
  • The GetFieldName() method returns the name of a field that is passed to the method as a number; the names are static in this example.
  • The GetFieldType() method returns the data type of a field that is passed to the method as a number, which are statically-defined in this example. As a reminder, Log Parser supports the following five data types for COM plug-ins: TYPE_INTEGER, TYPE_REAL, TYPE_STRING, TYPE_TIMESTAMP, and TYPE_NULL.
  • The GetValue() method returns the data value of a field that is passed to the method as a number. Once again, the data values are statically-defined in this example.
  • The ReadRecord() method moves to the next record in your data set; this method returns True if there is data to read, or False when the end of data is reached. In this example, the method increments the record counter and sets the status based on whether the maximum number of records has been reached.

Summary

That wraps up the simplest example that I could put together of a COM-based input provider for Log Parser. In my next blog, I'll show how to create a generic COM-based input provider for Log Parser that you can use to parse text-based log files.

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

In Part 5 of this series, I'll show you how to create a generic script that you can use to add some color to your Log Parser charts. As I mentioned in Part 1 of this series, the default colors for Log parser charts are really dull and boring. For example, if I parse one month's worth of log files from one of my low-volume websites with the following query:

logparser.exe "SELECT date,COUNT(*) AS Hits INTO HITS.gif FROM *.log GROUP BY date ORDER BY date" -i:w3c -o:CHART -chartType:ColumnClustered -chartTitle:"" -q:ON

Log Parser will create the following ugly daily hits chart:

Generic Color Change Script

Here's the background story for this blog: I have a collection of scripts that I use to format my charts, several of which have faithfully served as the fodder for this blog series. With that in mind, I had a situation recently where I was querying logs with a series of data just like this, and of course the resulting charts were kind of hideous to look at. In one of the scripts that I often use, I create an array of colors to use, and then I apply the various colors to the individual data points in the series.

In the past I have always hard-coded the length for the array of colors based on the data that I am working with, but in this situation I had no idea how many data points I would have, so I decided to put together a quick script with an array that would work with a series of any size.

Here's the resulting script:

// Set a default color for the chart's data.
chart.SeriesCollection(0).Interior.Color = "#ffcccc";

// Define a short array of colors.
var colors = [
    "#ffff99", "#ff99ff", "#ff9999",
    "#99ffff", "#99ff99", "#9999ff",
    "#ffffcc", "#ffccff", "#ffcccc",
    "#ccffff", "#ccffcc", "#ccccff"
];

// Loop through the data points in the series.
for (x=0;x<chart.SeriesCollection(0).Points.Count;++x)
{
    // Set the color for the data point based on modulo division of the array length.
    chart.SeriesCollection(0).Points(x).Interior.Color = colors[x % colors.length ];
}

That's all that there is to the script - it's pretty simple. If I take the above script and save it as "FormatChart.js", I can use that script with my Log Parser query from earlier by adding an extra parameter to the command:

logparser.exe "SELECT date,COUNT(*) AS Hits INTO HITS.gif FROM *.log GROUP BY date ORDER BY date" -i:w3c -o:CHART -chartType:ColumnClustered -chartTitle:"" -q:ON -config:FormatChart.js

Now Log Parser will create the following daily hits chart with a great deal more color to it:

Okay - perhaps that's not the best color palette, but you get the idea. It looks even better when I change the query to use 3D charts:

logparser.exe "SELECT date,COUNT(*) AS Hits INTO HITS.gif FROM *.log GROUP BY date ORDER BY date" -i:w3c -o:CHART -chartType:Column3D -chartTitle:"" -q:ON -config:FormatChart.js

The above query creates the following chart:

Color Changing Pie Charts

I'd like to make a quick change to the script in order to make it work a little better with a pie chart:

// Set a default color for the chart's data.
chart.SeriesCollection(0).Interior.Color = "#cccccc";

// Define a short array of colors.
var colors = [
    "#cc3333", "#3333cc", "#33cc33",
    "#33cccc", "#cccc33", "#cc33cc"
];

// Loop through the data points in the series.
for (x=0;x<chart.SeriesCollection(0).Points.Count;++x)
{
    // Set the color for the data point based on modulo division of the array length.
    chart.SeriesCollection(0).Points(x).Interior.Color = colors[x % colors.length ];
}
// Rotate the chart 180 degrees - just so it looks a little better.
chartSpace.Charts(0).PlotArea.RotateClockwise();
chartSpace.Charts(0).PlotArea.RotateClockwise();

For this query I'd like to see a break down by HTTP status, and this necessitates some small change to the Log parser query:

logparser.exe "SELECT sc-status AS Status,COUNT(*) AS Hits INTO HITS.gif FROM *.log GROUP BY Status ORDER BY Status" -i:w3c -o:CHART -chartType:PieExploded3D -chartTitle:"" -q:ON -config:FormatChart.js

The above query creates the following chart:

Summary

That wraps it up for this blog - I hope that I've given you some ideas for ways that you can easily add some colors to some dull-looking Log Parser charts.

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

In Part 4 of this series, I'll show you how you can do a couple of cool things:

• Create a new input format for Log Parser (and I'll use FTP RSCA data as an example)
• Create charts from your custom input format

For the data source for my custom plug-in, I thought that it would be cool to consume the data from FTP 7's Runtime Status (RSCA). If you've followed some of my old blogs, you would have seen that around five years ago I wrote the following pair of blogs about programmatically viewing FTP 7 sessions:

• Viewing current FTP7 sessions using VBScript
• Viewing current FTP7 sessions using C#

I'm going to recycle some of the FTP RSCA concepts from those blogs in order to create my COM plug-in.

Log Parser Input Formats

If you're like me, you already realize that the existing features of Log Parser simply rock. But what most people don't realize is that Log Parser lets you extend the functionality by adding new input formats, so you can consume the data from any place where you feel compelled to sit down and write your own Log Parser module.

As a quick reminder, Log Parser supports the following built-in input formats:

• IIS Log File Input Formats
  • IISW3C: parses IIS log files in the W3C Extended Log File Format.
  • IIS: parses IIS log files in the Microsoft IIS Log File Format.
  • BIN: parses IIS log files in the Centralized Binary Log File Format.
  • IISODBC: returns database records from the tables logged to by IIS when configured to log in the ODBC Log Format.
  • HTTPERR: parses HTTP error log files generated by Http.sys.
  • URLSCAN: parses log files generated by the URLScan IIS filter.
• Generic Text File Input Formats
  • CSV: parses comma-separated values text files.
  • TSV: parses tab-separated and space-separated values text files.
  • XML: parses XML text files.
  • W3C: parses text files in the W3C Extended Log File Format.
  • NCSA: parses web server log files in the NCSA Common, Combined, and Extended Log File Formats.
  • TEXTLINE: returns lines from generic text files.
  • TEXTWORD: returns words from generic text files.
• System Information Input Formats
  • EVT: returns events from the Windows Event Log and from Event Log backup files (.evt files).
  • FS: returns information on files and directories.
  • REG: returns information on registry values.
  • ADS: returns information on Active Directory objects.
• Special-purpose Input Formats
  • NETMON: parses network capture files created by NetMon.
  • ETW: parses Enterprise Tracing for Windows trace log files and live sessions.
  • COM: provides an interface to Custom Input Format COM plug-ins.

This last input format, COM, is how you interface with Log Parser in order to create your own input formats. When you install Log Parser, there are a few COM-based samples in the Log Parser directory, and you can take a look at those when you get the chance.

A Brief Introduction to Creating a COM plug-ins for Log Parser

To start with, your COM plug-in has to support a few public methods - and each of these will be more clear when I create my plug-in later:

After you've created and registered your COM plug-in, you will call it by using something like the following syntax:

logparser "SELECT * FROM FOO" -i:COM -iProgID:BAR

In this example, FOO is some data source that makes sense to your plug-in, and BAR is the COM class name for your plug-in.

Creating the COM plug-in for FTP RSCA Data

I'm going to demonstrate how to create a COM component as a scriptlet, and then I'll call that from Log Parser to process the data. I chose to use a scriptlet for this demo because they are quick to design and they're easily portable. Since no compilation is required, updates take place on the fly. All of that being said, if I were writing a real COM plug-in for Log Parser, I would use C# or C++.

To create the sample COM plug-in, copy the following code into a text file, and save that file as "MSUtil.LogQuery.FtpRscaScriptlet.sct" to your computer. (Note: The *.SCT file extension tells Windows that this is a scriptlet file.)

<SCRIPTLET>
  <registration
    Description="FTP RSCA for Log Parser Scriptlet"
    Progid="MSUtil.LogQuery.FtpRscaScriptlet"
    Classid="{4e616d65-6f6e-6d65-6973-526f62657274}"
    Version="1.00"
    Remotable="False" />
  <comment>
  EXAMPLE 1: logparser "SELECT * FROM ftp.example.com" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet
  EXAMPLE 2: logparser "SELECT * FROM 1" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet
  </comment>
  <implements id="Automation" type="Automation">
    <method name="OpenInput">
      <parameter name="strValue"/>
    </method>
    <method name="GetFieldCount" />
    <method name="GetFieldName">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="GetFieldType">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="ReadRecord" />
    <method name="GetValue">
      <parameter name="intFieldIndex"/>
    </method>
    <method name="CloseInput">
      <parameter name="blnAbort"/>
    </method>
  </implements>
  <SCRIPT LANGUAGE="VBScript">

Option Explicit

Dim objAdminManager,objSessionDictionary
Dim objSitesSection,objSitesCollection
Dim objSiteElement,objFtpServerElement
Dim objSessionsElement,objSessionElement
Dim intSiteElementPos,intSession,intRecordIndex
Dim clsSession

intRecordIndex = -1

' --------------------------------------------------------------------------------
' Open an input session that reads FTP RSCA data and stores it in a dictionary object.
' --------------------------------------------------------------------------------

Public Function OpenInput(strValue)
  Set objSessionDictionary = CreateObject("Scripting.Dictionary")
  Set objAdminManager = CreateObject("Microsoft.ApplicationHost.WritableAdminManager")
  objAdminManager.CommitPath = "MACHINE/WEBROOT/APPHOST"
  Set objSitesSection = objAdminManager.GetAdminSection("system.applicationHost/sites", "MACHINE/WEBROOT/APPHOST")
  Set objSitesCollection = objSitesSection.Collection
  If IsNumeric(strValue) Then
    intSiteElementPos = FindElement(objSitesCollection, "site", Array("id", strValue))
  Else
    intSiteElementPos = FindElement(objSitesCollection, "site", Array("name", strValue))
  End If
  If intSiteElementPos > -1 Then
    Set objSiteElement = objSitesCollection.Item(intSiteElementPos)
    Set objFtpServerElement = objSiteElement.ChildElements.Item("ftpServer")
    Set objSessionsElement = objFtpServerElement.ChildElements.Item("sessions").Collection
    For intSession = 0 To CLng(objSessionsElement.Count)-1
       Set objSessionElement = objSessionsElement.Item(intSession)
       Set clsSession = New Session
       clsSession.CurrentDateTime = GetUtcDate()
       clsSession.ClientIp = objSessionElement.GetPropertyByName("clientIp").Value
       clsSession.SessionId = objSessionElement.GetPropertyByName("sessionId").Value
       clsSession.SessionStartTime = objSessionElement.GetPropertyByName("sessionStartTime").Value
       clsSession.UserName = objSessionElement.GetPropertyByName("userName").Value
       clsSession.CurrentCommand = objSessionElement.GetPropertyByName("currentCommand").Value
       clsSession.PreviousCommand = objSessionElement.GetPropertyByName("previousCommand").Value
       clsSession.CommandStartTime = objSessionElement.GetPropertyByName("commandStartTime").Value
       clsSession.BytesSent = objSessionElement.GetPropertyByName("bytesSent").Value
       clsSession.BytesReceived = objSessionElement.GetPropertyByName("bytesReceived").Value
       clsSession.LastErrorStatus = objSessionElement.GetPropertyByName("lastErrorStatus").Value
       objSessionDictionary.Add intSession,clsSession
    Next
  End If
End Function

' --------------------------------------------------------------------------------
' Close the input session.
' --------------------------------------------------------------------------------

Public Function CloseInput(blnAbort)
  intRecordIndex = -1
  objSessionDictionary.RemoveAll
End Function

' --------------------------------------------------------------------------------
' Return the count of fields.
' --------------------------------------------------------------------------------

Public Function GetFieldCount()
  GetFieldCount = 11
End Function

' --------------------------------------------------------------------------------
' Return the specified field's name.
' --------------------------------------------------------------------------------

Public Function GetFieldName(intFieldIndex)
  Select Case intFieldIndex
    Case 0
      GetFieldName = "currentDateTime"
    Case 1
      GetFieldName = "clientIp"
    Case 2
      GetFieldName = "sessionId"
    Case 3
      GetFieldName = "sessionStartTime"
    Case 4
      GetFieldName = "userName"
    Case 5
      GetFieldName = "currentCommand"
    Case 6
      GetFieldName = "previousCommand"
    Case 7
      GetFieldName = "commandStartTime"
    Case 8
      GetFieldName = "bytesSent"
    Case 9
      GetFieldName = "bytesReceived"
    Case 10
      GetFieldName = "lastErrorStatus"
  End Select
End Function

' --------------------------------------------------------------------------------
' Return the specified field's type.
' --------------------------------------------------------------------------------

Public Function GetFieldType(intFieldIndex)
  Const TYPE_INTEGER   = 1
  Const TYPE_REAL      = 2
  Const TYPE_STRING    = 3
  Const TYPE_TIMESTAMP = 4
  Const TYPE_NULL      = 5
  
  Select Case intFieldIndex
    Case 0
      GetFieldType = TYPE_STRING
    Case 1
      GetFieldType = TYPE_STRING
    Case 2
      GetFieldType = TYPE_STRING
    Case 3
      GetFieldType = TYPE_STRING
    Case 4
      GetFieldType = TYPE_STRING
    Case 5
      GetFieldType = TYPE_STRING
    Case 6
      GetFieldType = TYPE_STRING
    Case 7
      GetFieldType = TYPE_STRING
    Case 8
      GetFieldType = TYPE_INTEGER
    Case 9
      GetFieldType = TYPE_INTEGER
    Case 10
      GetFieldType = TYPE_INTEGER
  End Select
End Function

' --------------------------------------------------------------------------------
' Return the specified field's value.
' --------------------------------------------------------------------------------

Public Function GetValue(intFieldIndex)
  If objSessionDictionary.Count > 0 Then
    Select Case intFieldIndex
      Case 0
        GetValue = objSessionDictionary(intRecordIndex).CurrentDateTime
      Case 1
        GetValue = objSessionDictionary(intRecordIndex).ClientIp
      Case 2
        GetValue = objSessionDictionary(intRecordIndex).SessionId
      Case 3
        GetValue = objSessionDictionary(intRecordIndex).SessionStartTime
      Case 4
        GetValue = objSessionDictionary(intRecordIndex).UserName
      Case 5
        GetValue = objSessionDictionary(intRecordIndex).CurrentCommand
      Case 6
        GetValue = objSessionDictionary(intRecordIndex).PreviousCommand
      Case 7
        GetValue = objSessionDictionary(intRecordIndex).CommandStartTime
      Case 8
        GetValue = objSessionDictionary(intRecordIndex).BytesSent
      Case 9
        GetValue = objSessionDictionary(intRecordIndex).BytesReceived
      Case 10
        GetValue = objSessionDictionary(intRecordIndex).LastErrorStatus
    End Select
  End If
End Function
  
' --------------------------------------------------------------------------------
' Read the next record, and return true or false if there is more data.
' --------------------------------------------------------------------------------

Public Function ReadRecord()
  If objSessionDictionary.Count > 0 Then
    If intRecordIndex < (objSessionDictionary.Count-1) Then
      intRecordIndex = intRecordIndex + 1
      ReadRecord = True
    Else
      ReadRecord = False
    End If
  End If
End Function

' --------------------------------------------------------------------------------
' Return the current UTC date/time.
' --------------------------------------------------------------------------------

Private Function GetUtcDate()
  Dim dtmNow,dtmUtc,strUtc
  Dim objShell,lngActiveTimeBias
  dtmNow = Now()
  Set objShell = CreateObject("WScript.Shell")
  lngActiveTimeBias = CLng(objShell.RegRead("HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"))
  dtmUtc = DateAdd("n",lngActiveTimeBias,dtmNow)
  strUtc = Year(dtmUtc) & "-" & _
    Right("0" & Month(dtmUtc),2) & "-" & _
    Right("0" & Day(dtmUtc),2) & "T" & _
    Right("0" & Hour(dtmUtc),2) & ":" & _
    Right("0" & Minute(dtmUtc),2) & ":" & _
    Right("0" & Second(dtmUtc),2) & ".000Z"
  GetUtcDate = strUtc
End Function

' --------------------------------------------------------------------------------
' Return an element's position in a collection.
' --------------------------------------------------------------------------------

Private Function FindElement(objCollection, strElementTagName, arrValuesToMatch)
  Dim i,elem,matches,j,prop,value
  For i = 0 To CInt(objCollection.Count) - 1
    Set elem = objCollection.Item(i)
    If elem.Name = strElementTagName Then
      matches = True
      For j = 0 To UBound(arrValuesToMatch) Step 2
        Set prop = elem.GetPropertyByName(arrValuesToMatch(j))
        value = prop.Value
        If Not IsNull(value) Then
          value = CStr(value)
        End If
        If Not value = CStr(arrValuesToMatch(j + 1)) Then
          matches = False
          Exit For
        End If
      Next
      If matches Then
        Exit For
      End If
    End If
  Next
  If matches Then
    FindElement = i
  Else
    FindElement = -1
  End If
End Function

' --------------------------------------------------------------------------------
' Define a generic class for holding session data.
' --------------------------------------------------------------------------------

Class Session
  Public CurrentDateTime
  Public ClientIp
  Public SessionId
  Public SessionStartTime
  Public UserName
  Public CurrentCommand
  Public PreviousCommand
  Public CommandStartTime
  Public BytesSent
  Public BytesReceived
  Public LastErrorStatus
End Class

  </SCRIPT>

</SCRIPTLET>

After you've saved the scriptlet code to your computer, you will register it by using the following syntax:

regsvr32 MSUtil.LogQuery.FtpRscaScriptlet.sct

At the very minimum, you can now use the COM plug-in with Log Parser by using syntax like the following:

logparser "SELECT * FROM ftp.example.com" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet

Next, we'll analyze what the scriptlet does.

Examining the COM plug-in in Detail

Here are the different parts of the scriptlet and what they do:

• The <registration> section of the scriptlet sets up the COM registration information; you'll notice the COM component class name and GUID, as well as version information and a general description.
• The <implements> section declares the public methods that the COM plug-in has to support.
• The <script> section contains the actual implementation:
  • The first part of the script section declares the global variables that will be used.
  • The second part of the script contains the required methods:
    • The OpenInput() method opens the FTP RSCA data for a specific FTP site:
      • The site will be specified in your Log Parser query, and the COM plug-in supports using either the site name or the site's numeric ID:
        • "SELECT * FROM ftp.example.com"
        • "SELECT * FROM 1"
      • All of the RSCA data for the site in stored in classes that will be cached in a global dictionary for quick access
    • The  CloseInput() method doesn't do much in this script, but your COM plug-ins may require more clean up depending on your data source.
    • The GetFieldCount() method simply returns the number of data fields in each record of your data.
    • The GetFieldName() method returns the name of a field that is passed to the method as a number.
    • The GetFieldType() method returns the data type of a field that is passed to the method as a number; Log Parser supports the following five data types for COM plug-ins:
      • TYPE_INTEGER
      • TYPE_REAL
      • TYPE_STRING
      • TYPE_TIMESTAMP
      • TYPE_NULL
    • The GetValue() method returns the data value of a field that is passed to the method as a number.
    • The ReadRecord() method moves to the next record in your data set; this method returns True if there is data to read, or False when the end of data is reached.
  • The third part of the script contains some helper features:
    • The GetUtcDate() method returns the current date and time in Universal Coordinated Time (UTC) format.
    • The FindElement() method locates a specified element's position within an IIS collection, or -1 if the element cannot be found. This method is used to determine the specified FTP site within the IIS configuration.
    • The Session class is a generic construct to hold the information for a single FTP RSCA data record.

This wraps up the description of how the scriptlet works as a COM plug-in, in the next part of my blog we'll look at how to actually use it.

Using the COM plug-in with Log Parser

Earlier I showed you how you can use the COM plug-in with Log Parser by using syntax like the following:

logparser "SELECT * FROM ftp.example.com" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet

This will return output that resembles something like the following:

That information is something of a jumbled mess, and we can clean that up a bit by simply choosing the fields that we might be interested in:

Now let's look at some interesting data - one of the main focuses for this blog series is charting  with Log Parser, so let's look at doing something useful with the data. To start with, here's how to create a pie chart that counts the number of sessions by user name:

logparser "SELECT CASE UserName WHEN '' THEN 'anonymous' ELSE TO_LOWERCASE(UserName) END AS User,COUNT(*) AS Sessions INTO SessionCountByUser.gif FROM 1 GROUP BY User" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet -chartType:PieExploded -o:CHART -values:on -categories:off -legend:on -chartTitle:"User Sessions"

This will generate a chart like the following:

Here's a variation on that script that illustrates how to create a pie chart that counts the number of authenticated sessions versus anonymous sessions:

logparser "SELECT CASE UserName WHEN '' THEN 'Anonymous' ELSE 'Authenticated' END AS AuthType,COUNT(*) AS Sessions INTO AuthenticatedSessions.gif FROM 1 GROUP BY AuthType" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet -chartType:PieExploded -o:CHART -values:on -categories:off -legend:on -chartTitle:"Authenticated Sessions"

This will generate a chart like the following:

We can also do line, bar, and column charts with the data:

logparser "SELECT CASE UserName WHEN '' THEN 'anonymous' ELSE TO_LOWERCASE(UserName) END AS User,COUNT(*) AS Sessions INTO SessionCountByUser.gif FROM 1 GROUP BY User" -i:COM -iProgID:MSUtil.LogQuery.FtpRscaScriptlet -chartType:Column3D -o:CHART -values:on -legend:off -chartTitle:"User Sessions"

The above code sample will generate a chart like the following:

There's a lot more that we could do with this, but eventually I have to get some sleep, so I think that's enough fun for the day.

Summary

In this blog post, I've shown you how to add your own custom input format to Log Parser by creating scriptlet as a COM plug-in. I hope that you take this information and create some great Log Parser plug-ins of your own.

;-]

In Part 3 of this series, I'll explain what to do when you're missing the Office Web Components that are required for creating the charts that I have been demonstrating in this series.

Here's a brief explanation of the symptoms: you try a simple query that will create a chart like the following example:

logparser.exe "SELECT Date,COUNT(*) AS Hits INTO HitsByDay.gif FROM *.log GROUP BY Date ORDER BY Date" -i:W3C -o:CHART -chartType:Line -legend:off -chartTitle:"Hits By Day"

And you get the following error message:

Error creating output format "CHART": This output format requires a licensed Microsoft Office Chart Web Component to be installed on the local machine

More often than not, this simply means that you have Office 2007 or Office 2010, which do not contain the Office Web Components that are used by Log Parser to create charts. Fortunately, you can download the missing components from the following URL on Microsoft's website:

Office 2003 Add-in: Office Web Components
http://www.microsoft.com/en-us/download/details.aspx?id=22276

When you run the installation, you will see the following license agreement:

When you check the box to accept the license agreement and click Install, you will eventually receive the following dialog box to let you know that the Office 2003 Web Components have been installed:

Once you have the Office 2003 Web Components installed, you can run the same query successfully:

logparser.exe "SELECT Date,COUNT(*) AS Hits INTO HitsByDay.gif FROM *.log GROUP BY Date ORDER BY Date" -i:W3C -o:CHART -chartType:Line -legend:off -chartTitle:"Hits By Day"

Statistics:
-----------
Elements processed: 123330
Elements output: 14
Execution time: 0.57 seconds

Note: The above query generates the following somewhat uninteresting chart:

That being said, the point of this blog was to let you know how to get charting back, not how to make pretty charts. I'll save pretty charts for a future blog. ;-]

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

In Part 2 of this series, I'll show you how to customize the area chart from Part 1 to show the chart area with a gradient. More specifically, there are three different chart gradient methods that we'll take a look at in this blog post:

• SetOneColorGradient
• SetTwoColorGradient
• SetPresetGradient

Before I continue, there is one quick Log Parser convention that you should realize: there are two objects that Log Parser will create and pass to your script. As you look at the sample scripts in this post, you will see these objects in use:

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color.
chart.PlotArea.Interior.Color = "#ffffff";

Before I get started, here's a quick review of VBScript that uses Log Parser COM objects:

Option Explicit

' Declare the variables.
Dim objLogQuery, strSQL
Dim objInputW3CFormat, objOutputChartFormat

' Create the Log Parser objects.
Set objLogQuery = WScript.CreateObject("MSUtil.LogQuery")
Set objInputW3CFormat = WScript.CreateObject("MSUtil.LogQuery.W3CInputFormat")
Set objOutputChartFormat = WScript.CreateObject("MSUtil.LogQuery.ChartOutputFormat")

' Define the SQL query.
strSQL = "SELECT Date, COUNT(*) AS Hits " & _
    " INTO _Part2.gif " & _
    " FROM *.log " & _
    " GROUP BY Date " & _
    " ORDER BY Date"

' Specify the chart options.
objOutputChartFormat.groupSize = "800x600"
objOutputChartFormat.fileType = "GIF"
objOutputChartFormat.chartType = "Area"
objOutputChartFormat.categories = "ON"
objOutputChartFormat.values = "ON"
objOutputChartFormat.legend = "OFF"

' Execute the SQL statement to create the chart.
objLogQuery.ExecuteBatch strSQL, objInputW3CFormat, objOutputChartFormat

As I mentioned in Part 1 of this series, you don't have to use the COM objects, but I chose to do so for this blog series because it makes it a little easier to script. That being said, if I use one month's worth of log files from one of my low-volume websites, Log Parser and this VBScript creates the following rather ugly daily hits chart:

With all of this in mind, let's take a look at some simple configuration scripts.

Setting Fonts and Titles and Such...

The above chart really needs some help, so the first thing that we'll do is change a few things. First things first, we need to specify the name of the chart configuration script in the VBScript sample:

Option Explicit

' Declare the variables.
Dim objLogQuery, strSQL
Dim objInputW3CFormat, objOutputChartFormat

' Create the Log Parser objects.
Set objLogQuery = WScript.CreateObject("MSUtil.LogQuery")
Set objInputW3CFormat = WScript.CreateObject("MSUtil.LogQuery.W3CInputFormat")
Set objOutputChartFormat = WScript.CreateObject("MSUtil.LogQuery.ChartOutputFormat")

' Define the SQL query.
strSQL = "SELECT Date, COUNT(*) AS Hits " & _
    " INTO Part2.gif " & _
    " FROM *.log " & _
    " GROUP BY Date " & _
    " ORDER BY Date"

' Specify the chart options.
objOutputChartFormat.groupSize = "800x600"
objOutputChartFormat.fileType = "GIF"
objOutputChartFormat.chartType = "Area"
objOutputChartFormat.categories = "ON"
objOutputChartFormat.values = "ON"
objOutputChartFormat.legend = "OFF"
objOutputChartFormat.config = "Part2.js"

' Execute the SQL statement to create the chart.
objLogQuery.ExecuteBatch strSQL, objInputW3CFormat, objOutputChartFormat

Next, we need to create the actual chart configuration script, which I wrote in JavaScript; you will need to save this as "Part2.js" in order to use my samples:

// Set the title above the chart.
chart.HasTitle = true;
chart.Title.Caption = "Hits by Day"

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color for the plot area.
chart.PlotArea.Interior.Color = "#f0f0f0";

// Set the font size for the chart values.
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 6;

// Get the start and end dates from the X axis.
var startDate = chart.Axes(0).CategoryLabels.Item(0).Caption;
var endDate = chart.Axes(0).CategoryLabels.Item(chart.Axes(0).CategoryLabels.ItemCount-1).Caption;

// Set the caption below the chart.
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption =
    "This chart shows the hits by day from "
    + startDate + " to " + endDate + ".";
chartSpace.ChartSpaceTitle.Font.Size = 10;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Set the style and caption for the Y axis.
chart.Axes(0).Font.Size = 8;
chart.Axes(0).HasTitle = true;
chart.Axes(0).Title.Caption = "Dates";
chart.Axes(0).Title.Font.Size = 9;

// Set the style and caption for the X axis.
chart.Axes(1).Font.Size = 7;
chart.Axes(1).HasTitle = true;
chart.Axes(1).Title.Caption = "Hits";
chart.Axes(1).Title.Font.Size = 9;

This chart configuration script does several things:

• Sets the title above the chart to "Hits by Day"
• Sets a black border style for the chart
• Sets the background color for the plot area to a light gray
• Sets the font size for the chart values to 6-point
• Sets the caption below the chart for the start and end date
• Sets the font styles and captions for the Y and Y axes

When you run the VBScript, the resulting chart looks like the following:

This looks a little more legible, but now let's look at setting some colors.

Setting a One-Color Gradient

Using the same JavaScript sample from earlier, we just need to make a couple of changes to the chart configuration script in order to use the SetOneColorGradient method:

// Set the title above the chart.
chart.HasTitle = true;
chart.Title.Caption = "Hits by Day"

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color for the plot area.
chart.PlotArea.Interior.Color = "#f0f0f0";

// Specify the chart gradient styles.
chart.SeriesCollection(0).Interior.SetOneColorGradient(
 chartSpace.Constants.chGradientHorizontal,
 chartSpace.Constants.chGradientVariantEnd,
 1.0,
 "#ff0000");

// Set the font size for the chart values.
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 6;

// Get the start and end dates from the X axis.
var startDate = chart.Axes(0).CategoryLabels.Item(0).Caption;
var endDate = chart.Axes(0).CategoryLabels.Item(chart.Axes(0).CategoryLabels.ItemCount-1).Caption;

// Set the caption below the chart.
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption =
    "This chart shows the hits by day from "
    + startDate + " to " + endDate + ".";
chartSpace.ChartSpaceTitle.Font.Size = 10;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Set the style and caption for the Y axis.
chart.Axes(0).Font.Size = 8;
chart.Axes(0).HasTitle = true;
chart.Axes(0).Title.Caption = "Dates";
chart.Axes(0).Title.Font.Size = 9;

// Set the style and caption for the X axis.
chart.Axes(1).Font.Size = 7;
chart.Axes(1).HasTitle = true;
chart.Axes(1).Title.Caption = "Hits";
chart.Axes(1).Title.Font.Size = 9;

When you run the VBScript, this renders a chart that looks like the following:

There are four parameters for the SetOneColorGradient method to look at:

Let's make some quick changes to parameters that we are passing to the SetOneColorGradient method and alter a few of the colors:

// Set the title above the chart.
chart.HasTitle = true;
chart.Title.Caption = "Hits by Day"

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color for the plot area.
chart.PlotArea.Interior.Color = "#333333";

// Specify the chart gradient styles.
chart.SeriesCollection(0).Interior.SetOneColorGradient(
    chartSpace.Constants.chGradientHorizontal,
    chartSpace.Constants.chGradientVariantStart,
    0.0,
    "#00ff00");

// Set the font size for the chart values.
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 6;
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Color = "#ffffff";

// Get the start and end dates from the X axis.
var startDate = chart.Axes(0).CategoryLabels.Item(0).Caption;
var endDate = chart.Axes(0).CategoryLabels.Item(chart.Axes(0).CategoryLabels.ItemCount-1).Caption;

// Set the caption below the chart.
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption =
    "This chart shows the hits by day from "
    + startDate + " to " + endDate + ".";
chartSpace.ChartSpaceTitle.Font.Size = 10;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Set the style and caption for the Y axis.
chart.Axes(0).Font.Size = 8;
chart.Axes(0).HasTitle = true;
chart.Axes(0).Title.Caption = "Dates";
chart.Axes(0).Title.Font.Size = 9;

// Set the style and caption for the X axis.
chart.Axes(1).Font.Size = 7;
chart.Axes(1).HasTitle = true;
chart.Axes(1).Title.Caption = "Hits";
chart.Axes(1).Title.Font.Size = 9;

When you run the VBScript, that results in the following considerably cooler-looking chart:

Setting a Two-Color Gradient

The SetTwoColorGradient method offers more color flexibility than the one-color gradient method, and we only need to make a couple of changes to the JavaScript for the chart configuration script in order to use the new method:

// Set the title above the chart.
chart.HasTitle = true;
chart.Title.Caption = "Hits by Day"

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color for the plot area.
chart.PlotArea.Interior.Color = "#FFFF99";

// Specify the chart gradient styles.
chart.SeriesCollection(0).Interior.SetTwoColorGradient(
 chartSpace.Constants.chGradientVertical,
 chartSpace.Constants.chGradientVariantStart,
 "#0066FF",
 "#00FFCC");

// Set the font size for the chart values.
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 6;

// Get the start and end dates from the X axis.
var startDate = chart.Axes(0).CategoryLabels.Item(0).Caption;
var endDate = chart.Axes(0).CategoryLabels.Item(chart.Axes(0).CategoryLabels.ItemCount-1).Caption;

// Set the caption below the chart.
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption =
    "This chart shows the hits by day from "
    + startDate + " to " + endDate + ".";
chartSpace.ChartSpaceTitle.Font.Size = 10;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Set the style and caption for the Y axis.
chart.Axes(0).Font.Size = 8;
chart.Axes(0).HasTitle = true;
chart.Axes(0).Title.Caption = "Dates";
chart.Axes(0).Title.Font.Size = 9;

// Set the style and caption for the X axis.
chart.Axes(1).Font.Size = 7;
chart.Axes(1).HasTitle = true;
chart.Axes(1).Title.Caption = "Hits";
chart.Axes(1).Title.Font.Size = 9;

When you run the VBScript, this will create the following chart:

There are four parameters for the SetTwoColorGradient method to consider:

Using a Preset Gradient

There is an additional gradient method that uses a collection of preset color palettes; this method is appropriately named SetPresetGradient. Once again, we need to make a couple of changes to the JavaScript for the chart configuration script in order to use the new method:

// Set the title above the chart.
chart.HasTitle = true;
chart.Title.Caption = "Hits by Day"

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color for the plot area.
chart.PlotArea.Interior.Color = "#EEFFDD";

// Specify the chart gradient styles.
chart.SeriesCollection(0).Interior.SetPresetGradient(
 chartSpace.Constants.chGradientHorizontal,
 chartSpace.Constants.chGradientVariantStart,
 chartSpace.Constants.chGradientFire);

// Set the font size for the chart values.
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 6;

// Get the start and end dates from the X axis.
var startDate = chart.Axes(0).CategoryLabels.Item(0).Caption;
var endDate = chart.Axes(0).CategoryLabels.Item(chart.Axes(0).CategoryLabels.ItemCount-1).Caption;

// Set the caption below the chart.
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption =
    "This chart shows the hits by day from "
    + startDate + " to " + endDate + ".";
chartSpace.ChartSpaceTitle.Font.Size = 10;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Set the style and caption for the Y axis.
chart.Axes(0).Font.Size = 8;
chart.Axes(0).HasTitle = true;
chart.Axes(0).Title.Caption = "Dates";
chart.Axes(0).Title.Font.Size = 9;

// Set the style and caption for the X axis.
chart.Axes(1).Font.Size = 7;
chart.Axes(1).HasTitle = true;
chart.Axes(1).Title.Caption = "Hits";
chart.Axes(1).Title.Font.Size = 9;

When you run the VBScript, this will create the following chart:

There are three parameters for the SetPresetGradient method to look at:

There are several of preset gradients in the ChartPresetGradientTypeEnum enumeration, and a little experimentation will yield the best results.

Using 3-D Area Charts

For one last sample, I'd like to show you what gradients can do for your 3-D area charts. To do so, we first need to make a couple of small changes the VBScript that will create the chart:

Option Explicit

' Declare the variables.
Dim objLogQuery, strSQL
Dim objInputW3CFormat, objOutputChartFormat

' Create the Log Parser objects.
Set objLogQuery = WScript.CreateObject("MSUtil.LogQuery")
Set objInputW3CFormat = WScript.CreateObject("MSUtil.LogQuery.W3CInputFormat")
Set objOutputChartFormat = WScript.CreateObject("MSUtil.LogQuery.ChartOutputFormat")

' Define the SQL query.
strSQL = "SELECT Date, COUNT(*) AS Hits " & _
    " INTO _Part2.gif " & _
    " FROM *.log " & _
    " GROUP BY Date " & _
    " ORDER BY Date"

' Specify the chart options.
objOutputChartFormat.groupSize = "1024x768"
objOutputChartFormat.fileType = "GIF"
objOutputChartFormat.chartType = "Area3D"
objOutputChartFormat.categories = "ON"
objOutputChartFormat.values = "ON"
objOutputChartFormat.legend = "OFF"
objOutputChartFormat.config = "Part2.js"

' Execute the SQL statement to create the chart.
objLogQuery.ExecuteBatch strSQL, objInputW3CFormat, objOutputChartFormat

Next, we need to update the JavaScript for the chart configuration script to work with the new VBScript; for the most part, I'm just updating font sizes and chart colors:

// Set the title above the chart.
chart.HasTitle = true;
chart.Title.Caption = "Hits by Day"

// Clear the caption for the chart series.
chart.SeriesCollection(0).Caption = "";

// Set the border style for the chart.
chartSpace.Border.Color = "#000000";
chartSpace.Border.Weight = 2;

// Change the background color for the plot area.
chart.PlotArea.Interior.Color = "#FFFFCC";

// Specify the chart gradient styles.
chart.SeriesCollection(0).Interior.SetTwoColorGradient(
    chartSpace.Constants.chGradientHorizontal,
    chartSpace.Constants.chGradientVariantEnd,
    "#00CCFF",
    "#FFFFFF");

// Set the font size for the chart values.
chart.SeriesCollection(0).DataLabelsCollection(0).Font.Size = 7;

// Get the start and end dates from the X axis.
var startDate = chart.Axes(0).CategoryLabels.Item(0).Caption;
var endDate = chart.Axes(0).CategoryLabels.Item(chart.Axes(0).CategoryLabels.ItemCount-1).Caption;

// Set the caption below the chart.
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption =
    "This chart shows the hits by day from "
    + startDate + " to " + endDate + ".";
chartSpace.ChartSpaceTitle.Font.Size = 10;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Set the style and caption for the Y axis.
chart.Axes(0).Font.Size = 10;
chart.Axes(0).HasTitle = true;
chart.Axes(0).Title.Caption = "Dates";
chart.Axes(0).Title.Font.Size = 11;

// Set the style and caption for the X axis.
chart.Axes(1).Font.Size = 9;
chart.Axes(1).HasTitle = true;
chart.Axes(1).Title.Caption = "Hits";
chart.Axes(1).Title.Font.Size = 11;

When you run the VBScript, this will create the following chart:

Summary

In this blog post, I've written a lot of code samples in order to show you four different ways to set gradients for your Log Parser area charts. In future posts, I'll show you how to do some more cool things with some other types of charts.

;-]

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

I recently had a situation where I wanted to customize the chart output from Log Parser, and after a bunch of research I eventually arrived at the conclusion that configuration scripts to create customized charts are probably the least-documented feature of Log Parser. After a lot of experimentation, (and a bit of frustration), I finally managed to achieve the results that I wanted. With that in mind, I thought that it would make a great blog series if I documented some of the settings that I used.

Log Parser and Chart Configuration Scripts

When you look in the Log Parser help file, it makes mention of using configuration scripts to customize charts, and it provides the following small JavaScript sample:

// Add a caption
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption = "Generated by Log Parser 2.2";
chartSpace.ChartSpaceTitle.Font.Size = 6;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Change the background color
chart.PlotArea.Interior.Color = chartSpace.Constants.chColorNone;

Unfortunately, this sample isn't very useful, although I found dozens of forum posts that quote this sample as a way to do things - but it's the only sample that most people cite. The Log Parser help file mentions looking at the MSDN ChartSpace Object Model documentation, but that documentation is only slightly more useful. These two references are what led me to my earlier conclusion that chart configuration scripts are not well-documented, and especially when you are trying to do something with Log Parser.

What I found to be particularly helpful was to use the Log Parser COM interface and write scripts by using Adersoft's VbsEdit and JsEdit. In case you haven't used either of those applications, they are great IDEs for writing scripts; they both give you a great debugging environment, and they have a great object browser that I used to discover what options were available to me. In the end, these two editors made it possible to create the chart configuration scripts that I will discuss in this blog series.

By the way, chart configuration scripts can be written in VBScript or JavaScript, but for this blog I will use VBScript for the Log Parser COM samples and JavaScript for the configuration script samples. I didn't have to do it that way, but it seemed like a good idea to help differentiate between the samples.

Using COM versus the Command-Line

For the samples in this blog series, I will use Log Parser's COM interface and VBScript to create my charts, but this is not necessary; everything that I am documenting can be done from the command-line version of Log parser, and I'll give you some quick examples to see the differences.

The following examples generate some simple area charts that plot the total number of hits by day, and both examples do exactly the same thing:

Command-Line:

logparser.exe "SELECT Date, COUNT(*) AS Hits INTO HitsByDay.gif FROM *.log GROUP BY Date ORDER BY Date" -i:W3C -fileType:GIF -groupSize:800x600 -chartType:Area -categories:ON -values:ON -legend:OFF

COM Interface:

Option Explicit

Dim objLogQuery, strSQL
Dim objInputW3CFormat, objOutputChartFormat

Set objLogQuery = WScript.CreateObject("MSUtil.LogQuery")
Set objInputW3CFormat = WScript.CreateObject("MSUtil.LogQuery.W3CInputFormat")
Set objOutputChartFormat = WScript.CreateObject("MSUtil.LogQuery.ChartOutputFormat")

strSQL = "SELECT Date, COUNT(*) AS Hits " & _
    " INTO HitsByDay.gif " & _
    " FROM *.log " & _
    " GROUP BY Date " & _
    " ORDER BY Date"

objOutputChartFormat.groupSize = "800x600"
objOutputChartFormat.fileType = "GIF"
objOutputChartFormat.chartType = "Area"
objOutputChartFormat.categories = "ON"
objOutputChartFormat.values = "ON"
objOutputChartFormat.legend = "OFF"

objLogQuery.ExecuteBatch strSQL, objInputW3CFormat, objOutputChartFormat

Ugly Charts

Using some of the log files from one of my websites, the above samples created the following basic chart:

Taking a look at this chart makes it easy to see why you would want to customize your output; that light blue is pretty awful, and those values are pretty hard to read.

Specifying Configuration Scripts

If you remember the incredibly basic configuration script from earlier, you only need to add one parameter to each example in order to specify the configuration script:

Command-Line:

logparser.exe "SELECT Date, COUNT(*) AS Hits INTO HitsByDay.gif FROM *.log GROUP BY Date ORDER BY Date" -i:W3C -fileType:GIF -groupSize:800x600 -chartType:Area -categories:ON -values:ON -legend:OFF -config:HitsByDay.js

COM Interface:

Option Explicit

Dim objLogQuery, strSQL
Dim objInputW3CFormat, objOutputChartFormat

Set objLogQuery = WScript.CreateObject("MSUtil.LogQuery")
Set objInputW3CFormat = WScript.CreateObject("MSUtil.LogQuery.W3CInputFormat")
Set objOutputChartFormat = WScript.CreateObject("MSUtil.LogQuery.ChartOutputFormat")

strSQL = "SELECT Date, COUNT(*) AS Hits " & _
    " INTO HitsByDay.gif " & _
    " FROM *.log " & _
    " GROUP BY Date " & _
    " ORDER BY Date"

objOutputChartFormat.groupSize = "800x600"
objOutputChartFormat.fileType = "GIF"
objOutputChartFormat.chartType = "Area"
objOutputChartFormat.categories = "ON"
objOutputChartFormat.values = "ON"
objOutputChartFormat.legend = "OFF"
objOutputChartFormat.config = "HitsByDay.js"

objLogQuery.ExecuteBatch strSQL, objInputW3CFormat, objOutputChartFormat

Simple Output

Taking a look at the resulting chart, you can see why I mentioned earlier that the configuration script wasn't very useful; all it does is add a centered title to the bottom of the chart:

Yup - that's a pretty useless sample configuration script for chart customization.

Next...

In my subsequent posts, I'll show how to make this chart (and several other types of charts) look a lot better.

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

Around a year ago I wrote a blog entry titled "Converting NCSA log files to W3C format", which showed how to use the MSWC.IISLog object to convert log files in the NCSA format back to W3C format. I wrote that blog entry to make up for the fact that the CONVLOG.EXE utility only converts log files to NCSA format, which some older log analysis software packages require. So what happens if you have a bunch of log files in W3C format and you don't have a copy of CONVLOG.EXE on your computer?

This blog entry is something of a reverse direction on my previous post, and shows you how to use the MSUtil.LogQuery object to convert W3C log files to NCSA format. The MSUtil.LogQuery object is shipped with LogParser, which you can download from one of the following URLs:

• Version 2.2 and 2.0: http://www.microsoft.com/download, search for "Log Parser"
• Version 2.1: http://www.microsoft.com/download, search for "IIS 6.0 Resource Kit Tools" 

Once you've downloaded and installed the LogParser package, you will need to manually register the LogParser.dll file in order to use the MSUtil.LogQuery object. Having done so, you can use the Windows Script Host (WSH) code in this blog article to convert a folder filled with W3C log files to NCSA format. 

To use this code, just copy the code into notepad, and save it with a ".vbs" file extension on your system. To run it, copy the script to a folder that contains your W3C log files, (named "ex*.log"), then double-click it.

Option Explicit
Dim objFSO
Dim objFolder
Dim objInputFile
Dim objOutputFile
Dim objLogQuery
Dim objLogRecordSet
Dim objLogRecord
Dim strInputPath
Dim strOutputPath
Dim strLogRecord
Dim strLogTemp

Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(".")

For Each objInputFile In objFolder.Files
 strInputPath = LCase(objInputFile.Name)
 If Left(strInputPath,2) = "ex" And Right(strInputPath,4) = ".log" Then
  strOutputPath = objFolder.Path & "\" & "nc" & Mid(strInputPath,3)
  strInputPath = objFolder.Path & "\" & strInputPath
  Set objLogQuery = CreateObject("MSUtil.LogQuery")
  Set objLogRecordSet = objLogQuery.Execute("SELECT * FROM " & strInputPath)
  Set objOutputFile = objFSO.CreateTextFile(strOutputPath)
  Do While Not objLogRecordSet.atEnd
  
   Set objLogRecord = objLogRecordSet.getRecord
   strLogRecord = FormatField(objLogRecord.getValue("c-ip"))
   strLogRecord = strLogRecord & " " & FormatField("")
   strLogRecord = strLogRecord & " " & FormatField(objLogRecord.getValue("cs-username"))
   strLogTemp = BuildDateTime(objLogRecord.getValue("date"),objLogRecord.getValue("time"))
   strLogRecord = strLogRecord & " " & FormatField(strLogTemp)
   strLogRecord = strLogRecord & " """ & FormatField(objLogRecord.getValue("cs-method"))
   strLogRecord = strLogRecord & " " & FormatField(objLogRecord.getValue("cs-uri-stem"))
   strLogTemp = FormatField(objLogRecord.getValue("cs-version"))
   If strLogTemp = "-" Then
    strLogRecord = strLogRecord & " HTTP/1.0"""
   Else
    strLogRecord = strLogRecord & " " & strLogTemp & """"
   End If   
   strLogRecord = strLogRecord & " " & FormatField(objLogRecord.getValue("sc-status"))
   strLogRecord = strLogRecord & " " & FormatField(objLogRecord.getValue("sc-bytes"))
    objOutputFile.WriteLine strLogRecord
   objLogRecordSet.moveNext
  Loop
  
  Set objLogQuery = Nothing
  objOutputFile.Close
 
 End If
Next

Function FormatField(tmpField)
 On Error Resume Next
 FormatField = "-"
 If Len(tmpField) > 0 Then FormatField = Trim(tmpField)
End Function

Function BuildDateTime(tmpDate,tmpTime)
 On Error Resume Next
 BuildDateTime = "[" & _
  Right("0" & Day(tmpDate),2) & "/" & _
  Left(MonthName(Month(tmpDate)),3) & "/" & _
  Year(tmpDate) & ":" & _
  Right("0" & Hour(tmpTime),2) & ":" & _
  Right("0" & Minute(tmpTime),2) & ":" & _
  Right("0" & Second(tmpTime),2) & _
  " +0000]"
End Function

I hope this helps!