Chia sẻ qua

W3C IIS Log Analysis using Log Parser

  • Bài viết

I was recently on the PowerScripting Podcast where we talked about the PAL tool and the challenges with developing it in PowerShell v2.0 such as dealing with very large PowerShell scripts. While on the podcast some of listeners asked about analyzing IIS W3C logs.

PAL doesn’t analyze IIS W3C logs, but I have quite a few Microsoft Log Parser queries that I use when I do IIS Health Checks – an offering from Microsoft Premier Field Engineering (PFE). This seems like a good time to publish some of them. Keep in mind that many of these originally came from the Microsoft Log Parser help documentation.

The following Microsoft Log Parser queries are the ones I typically use when analyzing IIS W3C logs. Many of these queries need additional information passed into Log Parser to work properly. See the Microsoft Log Parser documentation for more information on how to use these queries.

[Chart] Top 20 hit URLs:
SELECT TOP 20 cs-uri-stem, COUNT(*) AS Hits INTO IISLOG_ANALYSIS_TOP20_HITS.GIF FROM MERGED_LOG.CSV GROUP BY cs-uri-stem ORDER BY Hits DESC

[Chart] Top 10 ASP/ASPX URLs:
SELECT TOP 10 cs-uri-stem, COUNT(*) AS Hits INTO IISLOG_ANALYSIS_TOP10_ASPX_HITS_Date_10-06-2009_14-37-56PM.GIF FROM MERGED_LOG.CSV where cs-uri-stem like '%%.asp' or cs-uri-stem like '%%.aspx' GROUP BY cs-uri-stem ORDER BY Hits DESC

[Chart] Hit Frequency each hour:
SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)), COUNT(*) AS Hit_Frequency INTO IISLOG_ANALYSIS_HIT_FREQ_Date_10-06-2009_14-37-56PM.GIF FROM MERGED_LOG.LOG GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) DESC

[Chart] Bytes Per Extension:
SELECT EXTRACT_EXTENSION(cs-uri-stem) AS Extension, MUL(PROPSUM(sc-bytes),100.0) AS Bytes INTO IISLOG_ANALYSIS_BYTES_PER_EXT_Date_10-06-2009_14-37-56PM.GIF FROM MERGED_LOG.CSV GROUP BY Extension ORDER BY Bytes DESC

[Chart] Top 20 Client IP Addresses:
SELECT top 20 c-ip AS Client_IP,count(c-ip) AS Count from MERGED_LOG.CSV to IISLOG_ANALYSIS_TOP20_CLIENT_IP_Date_10-06-2009_14-37-56PM.GIF GROUP BY c-ip ORDER BY count(c-ip) DESC

[Chart] Total Unique Client IP's each hour (Users each hour). This requires two queries to create this chart:
Select TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) as Times, c-ip as ClientIP into IISLOG_ANALYSIS_DIST_CLIENT_IP.LOG from MERGED_LOG.LOG group by Times, ClientIP
Select Times, count(*) as Count from IISLOG_ANALYSIS_DIST_CLIENT_IP.LOG to IISLOG_ANALYSIS_HOURLY_UNIQUE_CIP_Date_10-06-2009_14-37-56PM.GIF group by Times order by Times DESC

Number of Errors:
SELECT cs-uri-stem, sc-status,sc-win32-status,COUNT(cs-uri-stem) from MERGED_LOG.CSV to IISLOG_ANALYSIS_ERROR_COUNT_Date_10-06-2009_14-37-56PM.CSV where sc-status>=400 GROUP BY cs-uri-stem,sc-status,sc-win32-status ORDER BY COUNT(cs-uri-stem) DESC

Error Frequency Each Hour:
SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)), COUNT(*) AS Error_Frequency FROM MERGED_LOG.LOG TO IISLOG_ANALYSIS_ERROR_FREQ_Date_10-06-2009_14-37-56PM.GIF WHERE sc-status >= 500 GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) DESC

Status Code Percentages:
SELECT sc-status, COUNT(*) AS Times from MERGED_LOG.CSV to IISLOG_ANALYSIS_STATUS_CODE_Date_10-06-2009_14-37-56PM.GIF GROUP BY sc-status ORDER BY Times DESC

Top 20 Average Longest Requests:
SELECT top 20 cs-uri-stem,count(cs-uri-stem) As Count,avg(sc-bytes) as sc-bytes,avg(cs-bytes) as cs-bytes,max(time-taken) as Max,min(time-taken) as Min,avg(time-taken) as Avg from MERGED_LOG.CSV to IISLOG_ANALYSIS_TOP20_AVG_LONGEST_Date_10-06-2009_14-37-56PM.CSV GROUP BY cs-uri-stem ORDER BY avg(time-taken) DESC

Average Response Times in Milliseconds Per Hour:
SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)), avg(time-taken) INTO IISLOG_ANALYSIS_AVG_RESP_TIME.GIF FROM MERGED_LOG.LOG WHERE sc-status=200 AND (cs-uri-stem like '%%.asp' or cs-uri-stem like '%%.aspx') GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) DESC

Comments

  • Anonymous
    September 01, 2011
    Clint, What don't you know how to do with PowerShell?  This is awesome!