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.
You can import perflog (BLG or CSV file) into SQL Server via relog.exe or we can assume you already logged the performance data directly into SQL Server database. Now we can query for the data following the code sample.
Dim arguments, conn
Set arguments = WScript.Arguments
If arguments.Count < 2 Then
ShowUsage()
Set arguments = Nothing
WScript.Quit(0)
End If
Dim logFile, logName, odbcDsn, dbName, outputType
logFile = WScript.Arguments.Named.Item("file")
logName = WScript.Arguments.Named.Item("logname")
If IsEmpty(WScript.Arguments.Named.Item("dsn")) Then
odbcDsn = "perflogdb"
Else
odbcDsn = WScript.Arguments.Named.Item("dsn")
End If
If IsEmpty(WScript.Arguments.Named.Item("dbname")) Then
dbName = "perflogreports"
Else
dbName = WScript.Arguments.Named.Item("dbname")
End If
If IsEmpty(WScript.Arguments.Named.Item("output")) Then
outputType = "csv"
Else
outputType = WScript.Arguments.Named.Item("output")
End If
Set arguments = Nothing
'Import CSV perflog into MS SQL Svr db
Dim shell
Set shell = WScript.CreateObject("WScript.Shell")
shell.Run("relog " & logFile & " -f sql -o SQL:" & odbcDsn & "!" & logName)
Set shell = Nothing
'Now perform SQL queries
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=perflogreports;" & _
"Integrated Security=SSPI"
Dim rsProc, rsMain, rsLogInfo, query
On Error Resume Next
'Query for Test/Log Information (timestamp, log name, etc.)
query = "SELECT DISTINCT " & _
"i.DisplayString AS LogName, " & _
"c.MachineName, " & _
"i.LogStartTime, " & _
"i.LogStopTime, " & _
"i.TimeZoneName AS TimeZone " & _
"FROM " & _
"CounterDetails AS c, " & _
"DisplayToID AS i;"
Set rsLogInfo = conn.Execute(query)
'Query for system memory, CPU stats, etc.
query = "SELECT " & _
"c.ObjectName + ' ' + c.CounterName AS Counter, " & _
"AVG(d.CounterValue) AS AverageValue " & _
"FROM " & _
"CounterDetails AS c, " & _
"CounterData AS d " & _
"WHERE " & _
"c.CounterID = d.CounterID AND " & _
"c.InstanceName IS NULL " & _
"GROUP BY " & _
"c.ObjectName, " & _
"c.InstanceName, " & _
"c.CounterName " & _
"ORDER BY " & _
"Counter ASC;"
Set rsMain = conn.Execute(query)
'Query for CPU, memory stats, etc. for each logged process
query = "SELECT " & _
"c.ObjectName + ' ' + c.InstanceName + ' ' + c.CounterName " & _
"AS Counter, " & _
"AVG(d.CounterValue) AS AverageValue " & _
"FROM " & _
"CounterDetails AS c, " & _
"CounterData AS d " & _
"WHERE " & _
"c.CounterID = d.CounterID AND " & _
"c.InstanceName IS NOT NULL " & _
"GROUP BY " & _
"c.ObjectName, " & _
"c.InstanceName, " & _
"c.CounterName " & _
"ORDER BY " & _
"Counter ASC;"
Set rsProc = conn.Execute(query)
'Delete imported data by dropping the SQL database tables
'they will be created again on next CSV data import
query = "DROP TABLE CounterData, CounterDetails, DisplayToID;"
conn.Execute query
'Output stats to selected format
If outputType = "csv" Then
WScript.Echo ""
WScript.Echo """Log Name"","""&rsLogInfo("LogName")&""""
WScript.Echo """Machine Name"","""&rsLogInfo("MachineName")&""""
WScript.Echo """Log Start Time"","""&rsLogInfo("LogStartTime")&""""
WScript.Echo """Log Stop Time"","""&rsLogInfo("LogStopTime")&""""
WScript.Echo """Time Zone"","""&rsLogInfo("TimeZone")&""""
WScript.Echo ","
WScript.Echo """Counter Name"",""Average Value"""
Do While Not rsMain.EOF
WScript.Echo """"&rsMain("Counter")&""","""&rsMain("AverageValue")&""""
rsMain.MoveNext
Loop
Do While Not rsProc.EOF
WScript.Echo """"&rsProc("Counter")&""","""&rsProc("AverageValue")&""""
rsProc.MoveNext
Loop
ElseIf outputType = "html" Then
WScript.Echo ""
WScript.Echo "<h1 align=center>"&rsLogInfo("LogName")&"</h1>"
WScript.Echo "<p><b>Machine Name</b>"&rsLogInfo("MachineName")&"</p>"
WScript.Echo "<p><b>Log Start Time</b>"&rsLogInfo("LogStartTime")&"</p>"
WScript.Echo "<p><b>Log Stop Time</b>"&rsLogInfo("LogStopTime")&"</p>"
WScript.Echo "<p><b>Time Zone</b>"&rsLogInfo("TimeZone")&"</p>"
WScript.Echo "<p> </p>"
WScript.Echo "<p><b>Counter Averages:</b>"
WScript.Echo "<table>"
WScript.Echo "<tr>"
WScript.Echo "<td>Counter Name</td>"
WScript.Echo "<td>Value</td>"
WScript.Echo "</tr>"
Do While Not rsMain.EOF
WScript.Echo "<tr>"
WScript.Echo "<td>" & rsMain("Counter") & "</td>"
WScript.Echo "<td>" & rsMain("AverageValue") & "</td>"
WScript.Echo "</tr>"
rsMain.MoveNext
Loop
Do While Not rsProc.EOF
WScript.Echo "<tr>"
WScript.Echo "<td>" & rsProc("Counter") & "</td>"
WScript.Echo "<td>" & rsProc("AverageValue") & "</td>"
WScript.Echo "</tr>"
rsProc.MoveNext
Loop
WScript.Echo "</table>"
Else 'If outputType = "xml" Then
WScript.Echo ""
WScript.Echo "<PerfLogReport>"
WScript.Echo "<LogInfo>"
WScript.Echo "<LogName>" & rsLogInfo("LogName") & "</LogName>"
WScript.Echo "<MachineName>" & rsLogInfo("MachineName") & "</MachineName>"
WScript.Echo "<LogStartTime>" & rsLogInfo("LogStartTime") & "</LogStartTime>"
WScript.Echo "<LogStopTime>" & rsLogInfo("LogStopTime") & "</LogStopTime>"
WScript.Echo "<TimeZone>" & rsLogInfo("TimeZone") & "</TimeZone>"
WScript.Echo "</LogInfo>"
WScript.Echo "<AvgCounterVals>"
Do While Not rsMain.EOF
WScript.Echo "<"&rsMain("Counter")&">"&rsMain("AverageValue")&"</"&rsMain("Counter")&">"
rsMain.MoveNext
Loop
Do While Not rsProc.EOF
WScript.Echo "<"&rsProc("Counter")&">"&rsProc("AverageValue")&"</"&rsProc("Counter")&">"
rsProc.MoveNext
Loop
WScript.Echo "</AvgCounterVals>"
WScript.Echo "</PerfLogReport>"
End If
Set rs = Nothing
conn.Close
Set conn = Nothing
Sub ShowUsage
WScript.Echo "parsePerfLogViaMsSql.vbs - parse PerfMon logs in CSV"
WScript.Echo "format by importing them to MS SQL Server then using SQL"
WScript.Echo "queries to get statistics like average values of counters"
WScript.Echo ""
WScript.Echo "Requirements: Local MS SQL database for CSV data import."
WScript.Echo " ODBC DSN configured for the database."
WScript.Echo " Windows XP or later OS that has relog.exe."
WScript.Echo " See parsePerfLogViaMsSql.htm for details."
WScript.Echo ""
WScript.Echo "Usage:"
WScript.Echo ""
WScript.Echo " cscript //Nologo parsePerfLogViaMsSql.vbs"
WScript.Echo " /file:fileName.csv /logname:value"
WScript.Echo " [/dsn:value] [/dbname:value]"
WScript.Echo " [/output:value]"
WScript.Echo ""
WScript.Echo "/file - fileName.csv should be absolute or relative path"
WScript.Echo " to perflog CSV file"
WScript.Echo ""
WScript.Echo "/logname - descriptive name for perflog report"
WScript.Echo ""
WScript.Echo "/dsn - optional parameter. ODBC DSN name to MS SQL"
WScript.Echo " database for import of CSV perflog by relog.exe."
WScript.Echo " Database assumed to be on local system. Default"
WScript.Echo " value is ""perflogreports"""
WScript.Echo ""
WScript.Echo "/dbname - optional parameter. MS SQL database name for"
WScript.Echo " OLEDB SQL queries of perflog counter statistics"
WScript.Echo " Database assumed to be on local system. Default"
WScript.Echo " value is ""perflogdb"""
WScript.Echo ""
WScript.Echo "/output - optional parameter. Valid values are ""csv"","
WScript.Echo " ""html"" or ""xml"". Default is CSV."
WScript.Echo ""
WScript.Echo "Example:"
WScript.Echo ""
WScript.Echo " cscript //Nologo parsePerfLogViaMsSql.vbs"
WScript.Echo " /file:perfLog.csv /logname:""Load Test 1"""
WScript.Echo " /dsn:myDsn /dbname:myDB /output:xml"
WScript.Echo ""
End Sub