LogParser scripts for various occasions...

Carlo Cardella

6 Feb 2008 9:56 AM

Comments 6

I've been working again with LogParser lately to extract some statistics for an IIS server which was facing a suspicious activity from the outside world: they were getting literally thousands of requests from a bunch if IP addresses to their page to request a "forgot password" for their online services. For this post is not important how we resolved the problem, but rather that of the occasion I had to create a few LogParser scripts to extract some statistics from the IIS logs, so I through those might be useful for other people too... Of course you're free to change them to adapt to your needs. Before you proceed, a couple of words on the scripts: those are meant to be "generic" and run with a batch file which accepts some input arguments, but you can run them from a command prompt directly replacing the "%x" placeholders; also, I print them on multiple lines to be easier to read, but you must run then on a single line.

This is to count how many requests you got to a specific page with a specific value in the query string (we were extracting data for the "change password" page with a "reset password" switch):

logparser "SELECT COUNT(*) INTO %1 FROM *.log WHERE EXTRACT_FILENAME(cs-uri-stem) = '%2' AND INDEX_OF(cs-uri-query, '%3') > 0" -i:IISW3C

Do you need some more details such as the return code, User-Agent etc...?

logparser "SELECT date, time, c-ip, cs-uri-query, sc-status, cs(User-Agent) 
    INTO %1 FROM *.log WHERE EXTRACT_FILENAME(cs-uri-stem) = '%2' AND INDEX_OF(cs-uri-query, '%3') > 0 
    ORDER BY c-ip" -i:IISW3C -RTP:-1

Since we noted that we were receiving a very high number or requests from specific IP addresses, here is the script to get this statistic:

logparser "SELECT DISTINCT c-ip, COUNT(*) as Hits INTO %1 FROM *.log 
    WHERE EXTRACT_FILENAME(cs-uri-stem) = '%2' AND INDEX_OF(cs-uri-query, '%3') > 0 
    GROUP BY c-ip ORDER BY Hits DESC" -i:IISW3C -RTP:-1

To get the average executing time for your pages (or web services) you must first of all enable extended logging in IIS, then you can run this script:

logparser "SELECT AVG(time-taken) As AverageTimeTaken, MAX(time-taken) As MaxTimeTaken, COUNT(*) As Hits, TO_LOWERCASE(cs-uri-stem) 
    INTO %1 FROM *.log
    WHERE EXTRACT_EXTENSION(TO_LOWERCASE(cs-uri-stem)) = '%2' 
    GROUP BY cs-uri-stem ORDER BY AverageTimeTaken DESC"  -i:IISW3C -RTP:-1

If you want information about requests getting a specific IIS status code, here's what you need:

logparser "SELECT date, time, cs-uri-stem, cs-uri-query, sc-status, cs(User-Agent) 
    INTO %1 FROM *.log WHERE sc-status = '%2'" -i:IISW3C -RTP:-1

If you want a nice pie graphic to summarize the distribution of bytes served for file type (you need extended logging to have the sc-bytes property):

LogParser "SELECT TO_UPPERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType, MUL(PROPSUM(sc-bytes),100.0) AS Bytes 
    INTO %1 FROM *.log GROUP BY PageType ORDER BY Bytes DESC" 
    -chartType:PieExploded -chartTitle:"Bytes per page type" -categories:off -i:IISW3C -o:CHART

On the Event Viewer now... this is if you need to extract all the events for a specific SourceName:

logparser "SELECT TimeWritten, SourceName, EventID, Message INTO %2 FROM %2 
    WHERE SourceName = '%3'" -i:EVT -o:CSV

A very simple way to convert a .evt file into a csv one, if you prefer to use Excel for your filters:

logparser "SELECT * INTO %1 FROM %2" -i:EVT -o:CSV

That's it for today, I'll likely add new ones if you're interested (or if you have specific requests smile_wink ).

Carlo

Quote of the day:
O Lord, help me to be pure, but not yet. - Saint Augustine

LogParser