This script provides a method for obtaining unique user counts easily and quickly. This method uses existing IIS features and relies upon the capabilities of Microsoft's Log Parser 2.2, which is available free. It involves just two small files: a batch file you execute in a command window and a configuration file containing the SQL. Running it generates three CSV files: the first two are interim data files; the third one is the one you want: it contains a listing of dates and unique users per date. Once the script completes it also writes to the console the total unique users found over all log files analyzed. I run this script on a monthly basis to update unique user metrics. I've also used it to analyze unique users for an entire years's worth of log files - no need to change any configuration when running the analysis on a month's log files or a year's worth: just drop the two files into the folder containing all the log files and execute. What is provided here are the basics to help you get started. OOTB, you can start capturing these metrics right away using the method presented here. Once you get up to speed, you can customize to many other applications.

Solution

Files: here are the two files you need. Just copy and past.

guu.bat

logparser -i:IISW3C file:guuLoad.txt -o:CSV -q >guuOutStep1.csv logparser -i:CSV -o:CSV "select UserName,Date from guuOutStep1.csv group by UserName,Date" -q>guuOutStep2.csv

logparser -i:CSV -o:CSV "select Date, count(*) as users from guuOutStep2.csv group by Date order by Date" -q>guuOutStep3.csv

logparser -i:CSV "select COUNT(DISTINCT UserName) AS Total-Unique-Users-for-this-Month from guuOutStep2.csv"

guuLoad.txt

SELECT

REPLACE_STR(TO_LOWERCASE(REPLACE_STR(cs-username, '0#.w|', '')),'DOMAIN\\','') as UserName,

STRCAT(STRCAT(SUBSTR(EXTRACT_FILENAME(LogFilename),6,2),'/'),STRCAT(STRCAT(SUBSTR(EXTRACT_FILENAME(LogFilename),8,2),'/'),SUBSTR (EXTRACT_FILENAME(LogFilename),4,2))) AS Date

FROM

*.log 

WHERE 

sc-status<>401 AND 

cs-username<>'0#.w|DOMAIN\spcontent' AND 

cs-username<>'0#.w|DOMAIN\spadmin' AND 

cs-username<>'0#.w|DOMAIN\spfarm' AND 

cs-username<>'' AND 

cs-username<>NULL AND 

cs-username<>'DOMAIN\SPCONTENT' AND 

cs-username<>'DOMAIN\SPADMIN' AND 

cs-username<>'DOMAIN\SPFARM'

References

Notes