LogParser and Perfmon CSV September 7, 2009

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
GROUP BY Url
ORDER BY [Avg] DESC

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
ORDER BY Total DESC

http://www.lazydba.com/sql/1__21454.html

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
-nskiplines:9

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

Leave a Comment

LogParser Introduction and -o:DataGrid Option

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

http://support.microsoft.com/kb/910447

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]

From

	C:\ex060813.log

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.

Leave a Comment

Log Parser July 17, 2009

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

(1) W3C Log files

http://technet.microsoft.com/en-us/magazine/2006.08.insidemsft.aspx

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