Log Parser: Find out how many unique visitors your website has
(Kristofer Gafvert, August 2, 2005)Introduction
Log Parser is a free tool developed by Microsoft that can be used to read and generate reports based on your log files. It lets you run SQL similar queries against your log files, making it extremely easy to get the information you want and need. The following example shows how to use Log Parser 2.2 to find out how many unique visitors your website has per day, and also per month. In this article a unique visitor is the same as a unique IP in the log file. This is not completely true, because one user may be using many IPs, and one IP may be used by many people (in case of a proxy). But this is a common way to measure unique visitors.
Why do we need to know the number of unique visitors?
Needless to say, it is great to know how many people visits your website. First of all, it is fun to know that you yourself is not the only one reading your website. Second, if you plan to do any advertising on your website, the advertisers will probably want to know how many people is visiting your website. And third, you can plan for a server upgrade if the number of visitors is starting to outgrow the hardware of the server.
Use Log Parser 2.2 to find out the number of unique visitors
Version 2.2 of Log Parser does not support aggregate functions with DISTINCT arguments with GROUP BY clauses. So we need to use two queries to find out the number of unique visitors. Otherwise we could have used this query:
SELECT date, COUNT(DISTINCT c-ip)
FROM 'C:\LogFiles\web\ex*'
GROUP BY date
But now instead we need to use these two queries:
SELECT DISTINCT date, c-ip
INTO tmp.txt
FROM 'C:\LogFiles\web\ex*'
SELECT date, count(c-ip) AS UniqueVisitors
INTO Unique.txt
FROM tmp.txt
GROUP BY date
To run these two, you can either download the files and use this syntax:
logparser file:UniqueVisitors1.sql -i:W3C -o:W3C
logparser file:UniqueVisitors2.sql -i:W3C -o:NAT -rtp:-1
Or type the query as an argument to logparser.exe, for example:
logparser "SELECT DISTINCT date, c-ip INTO tmp.txt FROM 'C:\LogFiles\web\ex*'" -i:W3C -o:W3C
And the same with the second query.
As you have probably already figured out, you need to replace "C:\LogFiles\web\ex*" with the path to where you store your log files.
These two queries outputs a text file with a list of the number of unique visitors. Not so fun to read however. Fortunately Log Parser can create a chart as an image. First we need to change the query a little bit:
SELECT date, count(c-ip) AS UniqueVisitors
INTO Unique.gif
FROM tmp.txt
GROUP BY date
And next we run this:
logparser file:UniqueVisitors2-2.sql -i:W3C -o:CHART -chartType:ColumnStacked
The CHART output requires the Microsoft Office Web Components. This installs with products such as Microsoft Office XP and Microsoft Office 2003.
The last example shows how to find out the number of unique visitors per month.
SELECT TO_STRING(date, 'MMMM, yyyy') AS Month, count(c-ip) AS UniqueVisitors
INTO Unique.gif
FROM tmp.txt
GROUP BY Month
logparser file:UniqueVisitors2-3.sql -i:W3C -o:CHART -chartType:ColumnStacked
Applies to [?]
IIS 6.0
Resources
Download SQL Code for this article
Log Parser at Technet Script Center
Download Log Parser
The Unofficial Log Parser Support Site
Book: Microsoft Log Parser Toolkit