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:
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:
| Method Name |
Description |
OpenInput |
Opens your data source and sets up any initial environment settings. |
GetFieldCount |
Returns the number of fields that your plug-in will provide. |
GetFieldName |
Returns the name of a specified field. |
GetFieldType |
Returns the datatype of a specified field. |
GetValue |
Returns the value of a specified field. |
ReadRecord |
Reads the next record from your data source. |
CloseInput |
Closes your data source and cleans up any environment settings. |
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_INTEGERTYPE_REALTYPE_STRINGTYPE_TIMESTAMPTYPE_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:
currentDateTime |
clientIp |
sessionId |
sessionStartTime |
userName |
currentCommand |
previousCommand |
commandStartTime |
bytesSent |
bytesReceived |
lastErrorStatus |
---------------- |
-------- |
--------- |
---------------- |
-------- |
-------------- |
--------------- |
---------------- |
--------- |
------------- |
--------------- |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
3950d1e5-3e94-4734-a89a-9768c52aa924 |
2012-05-25T10:08:09.861Z |
robert |
PASS |
USER |
2012-05-25T11:42:06.080Z |
6049 |
1193 |
0 |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
d1591fa8-3b09-4afd-b2c0-950421ba79fe |
2012-05-25T10:08:18.184Z |
robert |
RETR |
NLST |
2012-05-25T11:42:07.172Z |
5887 |
1169 |
0 |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
0f92b5ed-920a-441d-a15d-39056a36f2a4 |
2012-05-25T10:08:22.327Z |
robert |
NOOP |
NLST |
2012-05-25T11:41:40.917Z |
5857 |
1163 |
0 |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
16925f0d-1fc5-4cb7-be19-ab33face2da9 |
2012-05-25T10:08:48.756Z |
|
NLST |
SYST |
2012-05-25T11:41:44.770Z |
6026 |
1192 |
0 |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
aeb68389-869b-4afc-8c81-47b578e74824 |
2012-05-25T10:08:54.214Z |
|
USER |
HOST |
2012-05-25T11:41:42.087Z |
5864 |
1168 |
0 |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
4ed55569-ee25-47d1-8388-12cdb90a1c07 |
2012-05-25T10:12:31.555Z |
alice |
RETR |
NLST |
2012-05-25T11:42:01.789Z |
5780 |
1138 |
0 |
2012-05-25T11:42:11.000Z |
10.121.75.26 |
d6b16bb4-cb65-492d-a9fa-fbd6b72de0f3 |
2012-05-25T10:12:54.591Z |
bob |
NOOP |
NLST |
2012-05-25T11:41:46.563Z |
5748 |
1130 |
0 |
| |
|
|
|
|
|
|
|
|
|
|
Statistics: |
|
|
|
|
|
|
|
|
|
|
----------- |
|
|
|
|
|
|
|
|
|
|
Elements processed: |
7 |
|
|
|
|
|
|
|
|
|
Elements output: |
7 |
|
|
|
|
|
|
|
|
|
Execution time: |
0.12 seconds |
|
|
|
|
|
|
|
|
|
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:
userName |
currentCommand |
commandStartTime |
-------- |
-------------- |
---------------- |
robert |
PASS |
2012-05-25T11:42:06.080Z |
robert |
RETR |
2012-05-25T11:42:07.172Z |
robert |
NOOP |
2012-05-25T11:41:40.917Z |
| |
NLST |
2012-05-25T11:41:44.770Z |
| |
USER |
2012-05-25T11:41:42.087Z |
alice |
RETR |
2012-05-25T11:42:01.789Z |
bob |
NOOP |
2012-05-25T11:41:46.563Z |
| |
|
|
Statistics: |
|
|
----------- |
|
|
Elements processed: |
7 |
|
Elements output: |
7 |
|
Execution time: |
0.12 seconds |
|
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.
;-]