Log Parser, unique page views and unique IP’s

I’ve been working with Log parser lately and wanted to pass along a couple queries.  I have a in-depth filter that involved using three Log parser functions (EXTRACT_EXTENSION, EXTRACT_FILENAME, EXTRACT_PATH).  Each have their own purpose but combined together, you can filter out most things including extensions, specific webpages and folder names all at once. 


‘Retrieves raw page views for a period of time.  The FROM could be a specific location or a websites.  This groups the totals by day.
SELECT Date, COUNT (*) AS PAGEVIEWS
FROM <example.com>
WHERE
EXTRACT_EXTENSION(to_lowercase(cs-uri-stem)) NOT IN (‘asf’;’axd’;’css’;’exe’;’gif’;’ico’;’jpg’;’js’;’msi’;’png’;’txt’;’vsi’;’wmv’;’xml’;’zip’) AND
EXTRACT_FILENAME(to_lowercase(cs-uri-stem)) NOT IN (‘rss.aspx’;’login.aspx’;’logout.aspx’) AND
EXTRACT_PATH(to_lowercase(cs-uri-stem)) not like ‘%/test%’ AND
EXTRACT_PATH(to_lowercase(cs-uri-stem)) not like ‘%/images%’ AND
EXTRACT_PATH(to_lowercase(cs-uri-stem)) not like ‘%/Example%’
and sc-status=200
Group By Date


‘Unique Visitors from example.com
SELECT DISTINCT c-ip
FROM <example.com>
WHERE
EXTRACT_EXTENSION(to_lowercase(cs-uri-stem)) NOT IN (‘asf’;’axd’;’css’;’exe’;’gif’;’ico’;’jpg’;’js’;’msi’;’png’;’txt’;’vsi’;’wmv’;’xml’;’zip’) AND
EXTRACT_FILENAME(to_lowercase(cs-uri-stem)) NOT IN (‘rss.aspx’;’login.aspx’;’logout.aspx’;) AND
EXTRACT_PATH(to_lowercase(cs-uri-stem)) not like ‘%/test%’ AND
EXTRACT_PATH(to_lowercase(cs-uri-stem)) not like ‘%/YourThemes%’ AND
EXTRACT_PATH(to_lowercase(cs-uri-stem)) not like ‘%/images%’ AND
and sc-status=200


Cheers,


Steve Schofield

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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