this morning i had some free time (and also was fed up with WMI, with which i was experimenting the last days) – so i opened my ever growing outlook task list and one entry catched my attention “check out LogParser!”.

cool – that’s fun. so i installed the IIS 6 Resource Kit Tools which includes the newest version of LogParser (which is 2.1).

LogParser is a tool from Microsoft to (hey the name says it :) parse Log Files. Supported formats include IIS log files, Windows Event log files, URLSCAN logs, IIS 6 HTTPERR logs, CSV files, generic text files and some more.

You can query those files with sql syntax which is very nice. Ouput formats include text or sql tables or xml.

What i always wanted to do is to write some little scripts to check several security related logs on my web server.

First i started to query some event log data.

The following query gives all events from the system eventlog, ordered by number of occurences and event type (error, warning, info). this gives a good overview of the most common types of errors on the system (the -O:DATAGRID parameters opens a window and shows all results in a data grid).

logparser “select distinct EventID, EventTypeName, Message, Count(*) as Entries from System group by EventID, Message, EventTypeName order by EventTypeName, Entries DESC” -i:EVT -o:DATAGRID

Another thing i am interested in, is to see failed logon attempts to the machine.

logparser “select distinct SID from Security where EventID IN (529; 530; 531; 532; 533; 534; 535; 537; 539)”

A very nice feature of LogParser is the ability to resolve SIDs to account names. The RESOLVE_SID function does this.

logparser “select distinct SID, RESOLVE_SID(SID) as Username from Security where EventID IN (529; 530; 531; 532; 533; 534; 535; 537; 539)

Now i turned my attention to querying several IIS log files. First i wanted to see the IP addresses with the most rejected pakets in URLSCAN.

logparser “SELECT TOP 10 ClientIP, COUNT(*) as Entries FROM URLSCAN WHERE Comment LIKE ‘Url%’ GROUP BY ClientIP ORDER BY Entries DESC” -o:DATAGRID

Comparable to RESOLVE_SID LogParser include the RESOLVEDNS function which can resolve IP addresses to DNS names. This is very slow but can come in handy.

logparser “SELECT TOP 10 REVERSEDNS(ClientIP), COUNT(*) as Entries FROM URLSCAN WHERE Comment LIKE ‘Url%’ GROUP BY ClientIP ORDER BY Entries DESC” -o:DATAGRID

A similar query can be used to parse IIS 6 HTTPERR log entries.

logparser “SELECT TOP 10 src-ip, s-reason, Count(*) as Hits FROM HTTPERR group by src-ip, s-reason order by Hits DESC”

There’s also an article on SecurityFocus which discusses forensic analysis of a web server with LogParser.

The Fun Part

DasBlog has a Referrer Menu where you can see what different kinds of RSS Reader are used to subscribe and how often they hit the web site. But you can only see the User-Agent names, not how many distinct IP addresses are behind these agents.

So i first wrote a query to see all User-Agents that sent requests to the server.

logparser “select distinct cs(User-Agent) from ex*.log order by cs(User-Agent)”

to see the corresponding IP addresses

logparser  “select distinct c-ip, cs(User-Agent) from ex*.log order by cs(User-Agent)”

To see how many hits the corresponding Agents and IP addresses have produced, use

logparser “SELECT distinct c-ip AS Client, cs(User-Agent), COUNT(*) as Hits FROM ex*.log group by cs(User-Agent), Client order by cs(User-Agent), Hits DESC”

..So if you want to see how many different people are using NewsGator, you can type

logparser “SELECT distinct c-ip AS Client, cs(User-Agent), COUNT(*) as Hits FROM ex*.log where cs(User-Agent) like ‘news%’ group by cs(User-Agent), Client order by cs(User-Agent), Hits DESC”

i like it!

Now my spare time is over and i can delete one entry from my task list (before adding several new ones :). maybe someone finds that useful.

I can also recommend having a look into the LogParser sample files included in the resource kit. they show some common queries and how you can use COM automation from scripts (there is also a sample script to find typical HTTP attack signatures in IIS Logs).

Another good LogParser resource is www.logparser.com, which includes a forum and a sample to use LogParser from C#