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!


2 thoughts on “Site Usage Past 30 Days (Distinct Number of Users)

  1. Hey man I downloaded the .exe and try to run the command but I got this error message:

    Cannot open : Cannot find any file matching
    “C:\Users\d7tam4m\Desktop\IIS logfiles\ex*.log”

    Any Ideas?

    Tks Hugo R.

    • Hugo,

      What is in that folder? Where is the directory for your log files? ex* assumes that everything in the directory is has a starting name of ‘ex’

      For example the logs in my directory are: ex0192.log, ex0193.log, ex0194.log,…ex4039.log, etc.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s