Using Log Parser with SharePoint to Generate Interesting Reports

What is Log parser?
Well, as per Microsoft’s definition: 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.

With Log parser, we can analyze the IIS Logs and get info like:

  • Number of unique users
  • Top IP hits
  • What referrers were sending people to my site
  • Which pages were most popular
  • How much data out
  • Which HTTP status codes are being generated , etc

Log parser SharePoint Examples

Say for example, I would like to retrieve the user information in CSV format for my SharePoint site, from multiple WFEs, I can do by the following query:

LogParser -i:W3C “SELECT distinct TO_LOWERCASE(cs-username) As UserName INTO 3319_User_names.csv FROM <IIS LOG FOLDER>,<IS LOG FOLDER of another server> WHERE UserName Is Not Null” -o:CSV

IIS LOG FOLDER: Its a folder where IIS Logs are stored. you can retrieve the from Multiple servers too. e.g. //mywfe2/D$/Logs/IIS/MyWebApp/W3SVC1126740424/*

Query to Find When a SharePoint Site was accessed last time:
Logparser -i:iisw3c “SELECT Date, cs-username, cs-uri-stem, COUNT (*) FROM c:\WINDOWS\system32\LogFiles\W3SVC2133324272\*.log WHERE cs-username <> NULL AND cs-uri-stem LIKE ‘/sites/Sales/%’ GROUP BY DATE,cs-username,cs-uri-stem” -q:Off -e:1 -o:datagrid 

Find who are all the users accessed a particular page:
Logparser -i:iisw3c “SELECT Date, cs-username, cs-uri-stem, COUNT (*) FROM C:\inetpub\logs\LogFiles\W3SVC222984324\*.log WHERE cs-username <> NULL AND cs-uri-stem LIKE ‘%ParCo-Review-Committee-2017.aspx’ GROUP BY DATE,cs-username,cs-uri-stem” -q:Off -e:1 -o:CSV

Find who Has Deleted a Particular Site:
Logparser -i:iisw3c “SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) FROM C:\inetpub\logs\LogFiles\W3SVC222984324\*.log WHERE cs-uri-stem like ‘%%deleteweb.aspx%%’ ORDER BY sc-status, date, cs-uri-stem, cs-uri-query” -q:Off -e:1 -o:CSV

Find a particular User’s activity:
LOGPARSER -i:IISW3C “SELECT date, time, cs-uri-stem, c-ip, cs-username INTO c:\UserActivity.csv from C:\inetpub\logs\LogFiles\W3SVC222984324\*.log where date > ‘2015-01-31’ AND cs-username like ‘%salaudeen%'” -o:CSV

Query to find Most Accessed Site URLs:

@path=C:\Program Files\Log Parser 2.2\

LogParser -i:W3C “SELECT TOP 50 cs-uri-stem as Url, COUNT(*) As Hits INTO MaxHits.csv FROM C:\WINDOWS\system32\LogFiles\W3SVC1* GROUP BY cs-uri-stem ORDER By Hits DESC” -o:CSV

Query to get user count of SharePoint Intranet sites with their Hit counts:

@path=C:\Program Files (x86)\Log Parser 2.2\

LogParser -i:W3C “SELECT TO_LOWERCASE(cs-username) As UserName, count(*) as [Total Hits] INTO PortalUsersList.csv FROM C:\WINDOWS\system32\LogFiles\W3SVC2133324272\*, \\WFE02\c$\WINDOWS\system32\LogFiles\W3SVC2133324272\*, \\WFE03\c$\WINDOWS\system32\LogFiles\W3SVC2133324272\* WHERE date > ‘2010-12-31’ AND cs-username Is Not Null group by TO_LOWERCASE(cs-username)” -o:CSV

Log Parser Query to get Users & Hits on a Sub-Site: /operations/partners/salesforce/

@path=C:\Program Files (x86)\Log Parser 2.2\

LogParser -i:W3C “SELECT cs-uri-stem, cs-username, count(*) as [Total Hits] INTO SubsiteHits.csv FROM \WFE01\C$\inetpub\logs\LogFiles\W3SVC274624414*, \WFE02\C$\inetpub\logs\LogFiles\W3SVC274624414* WHERE cs-uri-stem like ‘%%/operations/partners/salesforce%%’ AND date > ‘2014-04-18’ and cs-username <> ‘Global\svcSPSearch’ AND cs-username <> NULL group by cs-uri-stem, cs-username” -o:CSV

Log parser is a command line tool, you can the Log Parser Lizard GUI tool:

How to use Log Parser in SharePoint

More info on using Log parser tool:

Log Parser 2.2 download URL:

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

One thought on “Using Log Parser with SharePoint to Generate Interesting Reports

  • This article helped me a lot. Thank you for the info.

    I had to adjust the Script a little bit though to be able to list all users that accessed the SharePoint site of my choice and then export it. Please see below:

    LogParser -i:IISW3C “SELECT DISTINCT cs-username, cs-uri-stem, cs-uri-query, c-ip, date, time, cs(User-Agent) INTO C:\Output\users.csv FROM C:\inetpub\logs\LogFiles\W3SVC1999785934\*.log,C:\inetpub\logs\LogFiles\W3SVC604436182\*.log,C:\inetpub\logs\LogFiles\W3SVC648857543\*.log WHERE cs-uri-stem LIKE ‘/uk/TDX%’ AND cs-username IS NOT NULL” -o:CSV -q:Off

    Keep doing the great job you guys are doing.


Leave a Reply

Your email address will not be published. Required fields are marked *