Scott Hanselman

Parsing my IIS Log Files with LogParser 2.2 to learn more about Blogs stats from NewsGator and NewsGatorOnline

January 21, '05 Comments [5] Posted in ASP.NET | XML | Tools
Sponsored By

LogParser is pretty slick. It's wicked hard to learn, in my opinion as I'm not very SQL-y, but it's still awesome. Here's the marketecture description:

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory.

There's a unofficial support site at LogParser.com which makes me respect the tools usefulness even more. Not even unsupported Microsoft tool has it's own unsupported support site supported by MikeG.

It's not only cool because it's a super-psycho command-line tool, and we all know how I love those. But, it's also got a COM Interface so it programmable/scriptable as well.

So, long story short, I noticed recently that GregR over at NewsGator said that he's including subscriber statistics in the NewsGatorOnline User-Agent. Now, I'm not sure what I think of tunnelling information like that in such an odd place, but what do I know? The idea is clever as hell.

I decided this was a good time to learn how to use LogParser. I grabbed a 20-Meg IIS log file from my ISP from a random day last week and setup some queries. Here's what I did in about 5 minutes.

C:\Program Files\Log Parser 2.2>LogParser.exe -i:IISW3C "SELECT COUNT(cs(User-Agent)) AS Client FROM c:\temp\ex050120.log WHERE cs(User-Agent) LIKE 'NewsGatorOnline%' "
Client
------
134

Statistics:
-----------
Elements processed: 90386
Elements output:    1
Execution time:     3.71 seconds

So, if I'm understanding this correctly, NewsGatorOnline's Aggregator hit me 134 times this particular day. What's my number of NewsGatorOnline Subscribers?

C:\Program Files\Log Parser 2.2
>LogParser.exe -i:IISW3C "SELECT DISTINCT cs(User-Agent) AS Client FROM c:\temp\ex050120.log WHERE cs(User-Agent) LIKE 'NewsGatorOnline%' "
Client
---------------------------------------------------------------
NewsGatorOnline/2.0+(
http://www.newsgator.com;+2+subscribers)
NewsGatorOnline/2.0+(
http://www.newsgator.com;+1+subscribers)
NewsGatorOnline/2.0+(
http://www.newsgator.com;+250+subscribers)
NewsGatorOnline/2.0+(
http://www.newsgator.com;+15+subscribers)
NewsGatorOnline/2.0+(
http://www.newsgator.com;+249+subscribers)

Statistics:
-----------
Elements processed: 90386
Elements output:    5
Execution time:     3.67 seconds

Now, I don't know if I'm doing this right, but I'm not sure what I'm being told here. Either I'm doing this query wrong or Greg's stats are off. I'd expect the number of subscribers to be constant or change +/- a few a day.

UPDATE: I realized this on the way home and GregR emailed me to remind me, each distinct RSS Url is tracked by NewsGator. So my ATOM Feed and my Category Specific Feeds each have their own statistics.

This is just NewsGatorOnline folks. I also run queries on actual NewsGator Add-In users for both version 1.0 and 2.0. (I'm sure there's a way to do it all in one query, but again I'm not SQL-y.

C:\Program Files\Log Parser 2.2>LogParser.exe -i:IISW3C "SELECT DISTINCT COUNT(c-ip) AS Client FROM c:\temp\ex050120.log WHERE cs(User-Agent) LIKE 'NewsGator/1%' "
Client
------
25

C:\Program Files\Log Parser 2.2>LogParser.exe -i:IISW3C "SELECT DISTINCT COUNT(c-ip) AS Client FROM c:\temp\ex050120.log WHERE cs(User-Agent) LIKE 'NewsGator/2%' "
Client
------
4874

I thought this means I have around 25 distinct IPs using NewsGator 1.x who hit my site that day and about 4874 using NewsGator 2.0. But, that seems like WAY too many people subscribed to my site with just NewsGator. So, I changed the query:

C:\Program Files\Log Parser 2.2>LogParser.exe -i:IISW3C "SELECT COUNT(DISTINCT c-ip) AS Client FROM c:\temp\ex050120.log WHERE cs(User-Agent) LIKE 'NewsGator/2%' "
Client
------
385

THAT'S more like it. 385 distinct IPs using NewsGator 2.x hit my site this particular day. As an aside, there are actually 2 people using NewsGator 1.x. Cool. I need to spend more time understanding this tool. I believe I can ask it more complicated questions about my site than something like LiveStats, and I could set it up to email me charts or stats, or create an ASPX page for blog-specific reports.

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by ORCS Web

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.