Query counter info from perflog CSV file

In case anyone finds this helpful. However, you can do better reporting with PAL (http://www.codeplex.com/PAL), which also has a VBScript interface.

Here in this sample, we query the average values of the logged counter data in the CSV perflog, using ADO SQL queries. But due to limitations in MS Jet Text/CSV database driver, you can't do much querying against the CSV perflog, which uses CSV field parameters that are not very database driver friendly. The sample code covers some of the limitations found when querying a CSV perflog via ADO.

 Dim arguments, fso, conn
 Set arguments = WScript.Arguments
 
 If arguments.Count < 1 Then
     ShowUsage()
     Set arguments = Nothing
     WScript.Quit(0)
 End If
 
 Dim logFile, logDir, logFileName
 logFile = WScript.Arguments.Named.Item("file")
 Set arguments = Nothing
 
 Set fso = CreateObject("Scripting.FileSystemObject")
 logDir = fso.GetParentFolderName(logFile)
 If logDir = "" Then
     Dim tmpLen
     'tmpLen = Len(WScript.ScriptFullName) - Len(WScript.ScriptName)
     'logDir = Left(WScript.ScriptFullName,tmpLen)
     logDir = fso.GetParentFolderName(WScript.ScriptFullName)
 End If
 logFileName = fso.GetFileName(logFile)
 
 'Parse CSV headers field into Dictionary object
 Dim logFileObj
 Set logFileObj = fso.OpenTextFile(logDir & "\" & logFileName, 1)
 Dim headers, field, numHeaders, tmpField
 headers = split(logFileObj.ReadLine, ",")
 numHeaders = UBound(headers)
 Set field = CreateObject("Scripting.Dictionary")
 For i = 1 To numHeaders 'skip first column, the timestamp column
     'strip out quotes from CSV file, as SQL query of column name doesn't contain quotes
     tmpField = Replace(headers(i), """", "")
     'replace periods with #, as constrained by ADO and MS text driver
     tmpField = Replace(tmpField, ".", "#")
     'trim out CSV column headers to 64 chars as constrained by ADO and MS text driver
     tmpField = Left(tmpField,64)
     'don't add CSV header as field to query if there are duplicates
     If Not field.Exists(tmpField) Then
         field.Add tmpField,i
     End If
     'WScript.Echo headers(i)
 Next
 logFileObj.Close
 Set logFileObj = Nothing
 
 
 Set conn = CreateObject("ADODB.Connection")
 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & logDir & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""
 'conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=" & logDir
 Dim rs, query
 On Error Resume Next
 For Each key In field.Keys
     query = "SELECT AVG(`" & key & "`) AS logstat FROM " & logFileName & ";"
 '    WScript.Echo query
     Set rs = conn.Execute(query)
     WScript.Echo "avg 4 " & key & " is " & FormatNumber(rs("logstat"),2,TristateTrue)
 '    For Each field In rs.Fields
 '        WScript.Echo field.Name & "= " & field.Value
 '    Next
     Set rs = Nothing
 Next
 
 conn.Close
 Set conn = Nothing
 Set fso = Nothing
 
 Sub ShowUsage
     WScript.Echo "Usage:"
     WScript.Echo ""
     WScript.Echo "    cscript parsePerfLog.vbs /file:perflog1.csv"
     WScript.Echo ""
     WScript.Echo "    cscript parsePerfLog.vbs /file:C:\perflogs\perflog1.csv"
     WScript.Echo ""
 End Sub
 

Thanks for sharing. Is there a reason for the On Error Resume Next? More importantly, is there a reason for there not being a matching On Error Goto 0?

You know, that's a good point. I wrote that a long time back, so don't remember the logic to it.