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