Log Parser for Analyzing IIS Logs

Often we might come across situations where we need to analyze some of production log file. No doubt there are tools available in the market which provides a good logging capability, however one of the useful tools from Microsoft is Log Parser. Log Parser can be downloaded from @ http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=24659 . Below are the steps that I have used to analyze few scenarios top 10  pages of my web site, average bandwidth etc

Steps to analyze the logs using Log Parser.

Prerequisites

1.       Enable IIS logging and enable all the extended fields (if not all the ones that are listed below). This can be done in IISàWebSiteàPropertiesàEnable Logging (checkbox) à Properties and then check all the fields that are required.

2.       Please change the folder name and file name of the log files in below query. Please note the * in the query which is for the wild search. In this case we are going to query multiple files at once.

3.       Please ensure Log Parser tool is available.

Step 1: Identify Top 10 D+ (classic ASP pages)

Run the below query and  output will be stored in  Top10ClassicASP.csv  file

Logparser "Select TOP 10 STRCAT(EXTRACT_PATH(cs-uri-stem),'/') AS RequestPath, EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile, COUNT(*) AS TotalHits, Max(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS AvgBytesSent into Top10ClassicASP.csv FROM C:\IISLogs\ex11121*.log where cs-uri-stem like '%.asp'  GROUP BY cs-uri-stem ORDER BY TotalHits DESC"

Step 2: Identify the top 10 .Net pages

Run the below query and output will be stored in Top10ASPX.csv  file

Logparser "Select TOP 10 STRCAT(EXTRACT_PATH(cs-uri-stem),'/') AS RequestPath, EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile, COUNT(*) AS TotalHits, Max(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS AvgBytesSent into Top10ASPX.csv FROM C:\IISLogs\ex11121*.log where cs-uri-stem like '%.aspx%'  GROUP BY cs-uri-stem ORDER BY TotalHits DESC"

Step 3:  Response time for top 10 pages

Below query gives response time for 100 Hits of each page (Here is an example for login.aspx page). We need to perform this for each Classic ASP and ASP.Net page. Please change the csv file name.

Logparser "Select TOP 100 STRCAT(EXTRACT_PATH(cs-uri-stem),'/') AS RequestPath, EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile, Date, Time,s-computername as ServerName,sc-bytes as BytesSent,time-taken as TimeTaken,s-sitename as SiteName,cs-username as UserName,sc-status as HttpStatus into login.csv FROM C:\IISLogs\ex11121*.log where cs-uri-stem like '%login.aspx' and  sc-status =200 and time-taken > 0 ORDER BY Date DESC"

Step 4: Average network Bandwith/half an hour (Average KiloBytes sent and received by the server per half an hour)

Run the below query and output will be stored in AvgKBytesPerHalfanHour.csv

logparser "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, ADD(SCKBytesSec, CSKBytesSec) as KBytesSec USING DIV(DIV(MUL(1.0, SUM(sc-bytes)), 1024), 1800) as SCKbytesSec, DIV(DIV(MUL(1.0, SUM(cs-bytes)), 1024), 1800) as CSKBytesSec INTO AvgKBytesPerHalfanHour.csv FROM C:\IISLogs\ex11121*.log  GROUP BY HalfHour ORDER BY HalfHour ASC"

Step 5: Average network Bandwith/half an hour (Average KiloBytes sent by the server per half an hour)

logparser "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, SCKBytesSec as KBytesSec USING DIV(DIV(MUL(1.0, SUM(sc-bytes)), 1024), 1800) as SCKbytesSec INTO AvgKBytesPerHalfanHourSent.csv FROM C:\IISLogs\ex11121*.log  GROUP BY HalfHour ORDER BY HalfHour ASC"

Step 6: Average network Bandwith/half an hour (Average KiloBytes received by the server per half an hour)

logparser "SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, CSKBytesSec as KBytesSec USING DIV(DIV(MUL(1.0, SUM(cs-bytes)), 1024), 1800) as CSKBytesSec INTO AvgKBytesPerHalfanHourReceived.csv FROM C:\IISLogs\ex11121*.log  GROUP BY HalfHour ORDER BY HalfHour ASC"




Hope this helps.....