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 ?).
Carlo
Quote of the day:
O Lord, help me to be pure, but not yet. – Saint Augustine