Site Usage Past 30 Days (Distinct Number of Users)

So I had a request to find the site usage for the past several months, since November of last year. Unfortunately, the OOTB site usage of the “last 30 days” wouldn’t cut it. SharePoint doesnt allow you to up the ante any further from that. Why not SharePoint? Why would you only restrict reporting to the last 30 days? We have to go elsewhere to find the information we need. I saw a post of Joel Oleson’s blog about trying to parse logs out as an alternative for grabbing site uage past 30 days using the Microsoft tool LogParser (currently at v2.2). The only downfall to this tool is its all command lines, no GUI present. I downloaded it and gave it a spin.

I was searching for all unique users. Well how this tool works is pretty neat. You have an input format, output format, and charting or html features. I kept it plain and simple. We have a collection of IIS logs for our SharePoint server, so I decided to parse those logs to a folder based on modified date. It took a couple tries to perfect but this was my query:

LogParser.exe -i:IISW3C "SELECT DISTINCT cs-username AS Client INTO Results.csv FROM 'D:\IIS Log Files\W3SVC1205259452\ex*.log'" -o:CSV

Basically this lists all unique users throughout all the logs. I think that the LogParser will think case sensitive entries are unique from one another, because I got both ‘marcia’ and ‘MARCIA’ in my results. So I then decided to parse those out from Excel instead of letting LogParser count duplicates through the COUNT(DISTINCT cs-username) due to its case-sensitivity.

I counted the number of cells and Viola!