Sladescross's Blog

Blogging about Sharepoint related stuff

LogParser and Perfmon CSV September 7, 2009

Filed under: Log Parser,PerfMon,Tool — sladescross @ 4:06 pm

Microsoft Log Parser Toolkit

 By Gabriele Giuseppini, Mark Burnett

Checking the fields parsed by LogParser from the CSV file.

logparser.exe file: -i:CSV Test_000001.csv -iTsFormat: “MM/dd/yyyy hh:mm:ss.ll”

Example, of a query to calculate the average, maximum and minimum number of processes in each minute:

SELECT TO_STRING(Minute, ‘hh:mm’) As MinuteStr,

AVG(\\GAB1\System\Processes) As AvgProc,

MAX(\\GAB1\System\Processes) As MaxProc,

MIN(\\GAB1\System\Processes) As MinProc

USING QUANTIZE([(PDH-CSV 4.0) (Pacific Daylight Time) (420)], 60) As Minute

FROM Test_00000.csv


logparser.exe file:Ch01PerfMonAverages.sql -i:CSV -iTsFormat:”MM/dd/yyyy hh:mm:ss.ll”


Perfmon BLG files and LogParser

Filed under: Log Parser,PerfMon,Relog,Tool — sladescross @ 3:46 pm

Example syntax for simply converting between file formats:

relog -f csv inputfile.blg -o outputFile.csv

And then the CSV can be used as input to LogParser.

More sophisticated filter options with relog.exe


LogParser Examples August 19, 2009

Filed under: Example,Log Parser,Tool — sladescross @ 7:58 pm

logparser.exe -i:EVT -o:DATAGRID “SELECT TimeGenerated, EventID, message FROM System”

Slowest 10 pages

SELECT TOP 10 cs-uri-stem AS Url, MIN(time-taken) as [Min],
AVG(time-taken) AS [Avg], max(time-taken) AS [Max],
count(time-taken) AS Hits
FROM ex*.log
WHERE time-taken < 120000

WIN32 errors in IIS log

SELECT cs-uri-stem AS Url,
WIN32_ERROR_DESCRIPTION(sc-win32-status) AS Error, Count(*) AS Total
FROM ex*.log
WHERE (sc-win32-status > 0)
GROUP BY Url, Error

LogParser “select STRCAT(STRCAT(DATE,’ ‘), TIME) as LogDateTime, SOURCE,
Message From ‘c:\program files\Microsoft SQL Server\mssql\log\ERRORLOG.1′”
-i:TSV -iseparator:spaces -iHeaderFile:c:\formatfile.txt -headerRow:OFF

LogParser “select STRCAT(STRCAT(DATE,’ ‘), TIME) as LogDateTime, SOURCE,
Message INTO MY_ERROR_LOG_TABLE From ‘c:\program files\Microsoft SQL
Server\mssql\log\ERRORLOG.1′” -i:TSV -iseparator:spaces
-iHeaderFile:c:\formatfile.txt -headerRow:OFF -nskiplines:9 -o:SQL
-server:LAZYDEVELOPER -database:Pubs -driver:”SQL Server”
-username:TestSQLUser -password:TestSQLPassword -createTable:ON

SELECT  Area  AS [Area], Category  AS [Category], Level  AS Level], COUNT(*)   AS [Count] INTO %COMPUTER_NAME%-area-category-level.tsv FROM %COMPUTER_NAME%-*.log GROUP BY Area, Category, Level HAVING [COUNT] > 1000 ORDER BY [Count] DESC

LogParser -i:tsv -o:tsv file:area-category-level.sql?COMPUTER_NAME=mossidx01


LogParser Introduction and -o:DataGrid Option

Filed under: IIS,Log Parser,Tool — sladescross @ 7:18 pm

Scenario 3: Finding the 20 slowest pages in your Web site

You are using Microsoft Internet Information Services (IIS) 6, have a few ASP.NET Web sites, and are not really happy with the performance. You want to tweak the server and know about the top 20 pages that are taking the highest time to serve from the Web server. It is not as if you determine the highest time-taking pages, fix it, and your issues are resolved. Unfortunately, there are no silver bullets to resolve performance issues. At least there should be a way to start. Correct?

LOGPARSER -i:IISW3C file:C:\LPQ\Slowest20FilesInIIS.sql -o:DataGrid -q:off
Select Top 20

	LogRow as [Line Number],
	date as [Date],
	time as [Time],
	c-ip as [Client-IP],
	s-ip as [Server IP],
	s-port as [Server Port],
	cs-method as [Request Verb],
	cs-uri-stem as [Request URI],
	sc-bytes as [Bytes sent],
	sc-status as [Status],
	sc-substatus as [Sub-status],
	sc-win32-status as [Win 32 Status],
	time-taken as [Time Taken]



Order by
time-taken desc

Here, -i:IISW3C signifies that we are querying the IIS W3C logs.

You can view the complete list of IISW3C input format fields in the documentation and frame your query accordingly.

-o:DataGrid implies that the output should be shown in a data grid.


Log Parser July 17, 2009

Filed under: Log Parser,Microsoft,Tool — sladescross @ 4:19 pm

(1) W3C Log files

SELECT c-ip, date, time, cs-uri-stem, cs-uri-query FROM ex*.log TO exclient*.log

SELECT time, REVERSEDNS(c-ip), cs-uri-stem, cs-uri-query, sc-status FROM ex*.log TO MyTable WHERE (sc-status > 200 AND sc-status <> 404) OR time-taken > 30 ORDER BY time

(2) Windows Event Log

SELECT Message, COUNT(*) AS TotalCount FROM Application GROUP BY Message HAVING TotalCount > 2

Getting available fields

LogParser -i:EVT -h



Get every new post delivered to your Inbox.

Join 63 other followers