IIS Log Parser and important troubleshooting queiries

Understand your IIS Log better

There are cases when even asp.net and sitecore application level logs doesn't provide much of an information.The reason behind is some request is just processed by IIS webserver but never executed at application server i.e sitecore or asp.net pipeline.

This is where HTTP.Sys , Failed Request Tracing and IIS logs comes into picture. This is not when you looking for memory leak or CPU utilization.But this is certain some of the pattern of page execution or 500 internal server exception indirectly contributes to memory leak or CPU utilization. Always visit your IIS logs post performance load test. This will help you align your performance results with incoming request made in IIS.

Long story short. Download log parser studio if you have IIS log to analyze. This is very handy tool.

https://gallery.technet.microsoft.com/office/Log-Parser-Studio-cd458765

Query#1 Http.5xxx

SELECT TO_LOCALTIME( TO_TIMESTAMP( date, time ) ),

    cs-username,

    sc-status,

    sc-substatus,

    cs-uri-stem,

    time-taken as time_taken_MS,

    DIV(time-taken,60000) as timetaken_MM,

    cs-method, 

    cs-uri-query ,

    Count(*) AS Total

    From '[LOGFILEPATH]'

    Where sc-status = 500

    Group By cs-username, sc-status, cs-uri-stem, cs-uri-query,date,time,cs-method, cs-uri-query ,time-taken,sc-substatus

Query#2:/*  Count and sort all HTTP status codes */

Remember http status code below have apended http SUB STATUS CODE

SELECT TOP 25  

    STRCAT(TO_STRING(sc-status),  

    STRCAT('.', TO_STRING(sc-substatus))) As Status,  

    COUNT(*) AS Hits  

    FROM '[LOGFILEPATH]'  

GROUP BY Status  

ORDER BY Hits DESC 

Query#3 To find DDOS Attack

A distributed denial-of-service (DDoS)attack occurs when multiple systems flood the bandwidth or resources of a targeted system, usually one or more web servers. Such an attack is often the result of multiple compromised systems (for example, a botnet) flooding the targeted system with traffic.

/* Hits on a particular page by IP address */

select c-ip, count(c-ip) as requestcount 

from '[LOGFILEPATH]' 

where cs-uri-stem like '%/%' 

group by c-ip order by count(c-ip) desc