Log Parser Studio – User Agent Browser Query

See post below for Query which lists by Device.

This Query lists which Browser your users are accessing your site from.

Hope it helps you out – Feel free to post your versions.

/* List Browser types used */

select distinct
case strcnt(cs(user-agent),'Chrome') when 1 THEN 'Chrome' 
else case strcnt(cs(user-agent),'Firefox') when 1 THEN 'Firefox' 
else case strcnt(cs(user-agent),'MSIE+6') when 1 THEN 'IE 6'
else case strcnt(cs(user-agent),'MSIE+7') when 1 THEN 'IE 7'
else case strcnt(cs(user-agent),'MSIE+8') when 1 THEN 'IE 8'
else case strcnt(cs(user-agent),'MSIE+9') when 1 THEN 'IE 9'
else case strcnt(cs(user-agent),'MSIE+10') when 1 THEN 'IE 10'
else case strcnt(cs(user-agent),'IE+11') when 1 THEN 'IE 11'
else case strcnt(cs(user-agent),'+rv:11') when 1 THEN 'IE 11'
else case strcnt(cs(user-agent),'Opera') when 1 THEN 'Opera' 
else case strcnt(cs(user-agent),'Safari/') when 1 THEN 'Safari'
else case strcnt(cs(user-agent),'iPhone+OS+5') when 1 THEN 'Safari'
else case strcnt(cs(user-agent),'iPhone+OS+6') when 1 THEN 'Safari'
else case strcnt(cs(user-agent),'iPhone+OS+7') when 1 THEN 'Safari'
else case strcnt(cs(user-agent),'CPU+OS+6') when 1 THEN 'Safari'
else case strcnt(cs(user-agent),'iTunes/11') when 1 THEN 'iTunes'
else case strcnt(cs(user-agent),'iTunes/10') when 1 THEN 'iTunes'
ELSE 'Unknown' 
End
End
End
End
End
End
End
End 
End
End 
End 
End 
End 
End 
End
End 
End 
as Browser,
COUNT(c-ip) as Hits
FROM '[LOGFILEPATH]'

/* Dont display any cs(User-Agent) which are created by Bots */

WHERE cs(User-Agent) NOT LIKE '%Java%'
AND cs(User-Agent) NOT LIKE '%moodle%' 
AND cs(User-Agent) NOT LIKE '%twitter%' 
AND cs(User-Agent) NOT LIKE '%mymmu%'
AND cs(User-Agent) NOT LIKE '%MMU%'
AND cs(User-Agent) NOT LIKE '%admant%'
AND cs(User-Agent) NOT LIKE '%contextAd%' 
AND cs(User-Agent) NOT LIKE '%bingbot%'  
AND cs(User-Agent) NOT LIKE '%genieo%'
AND cs(User-Agent) NOT LIKE '%proximic%'
AND cs(User-Agent) NOT LIKE '%PageBot%'
AND cs(User-Agent) NOT LIKE '%feedfetcher%'
AND cs(User-Agent) NOT LIKE '%wordpress%'
AND cs(User-Agent) NOT LIKE '%PictureBot%'
AND cs(User-Agent) NOT LIKE '%WeSEE%'
AND cs(User-Agent) NOT LIKE '%Sogou%'
AND cs(User-Agent) NOT LIKE '%msnbot%'
AND cs(User-Agent) NOT LIKE '%Mediapartner%'
AND cs(User-Agent) NOT LIKE '%MagpieRSS%'
AND cs(User-Agent) NOT LIKE '%Affectv%'
AND cs(User-Agent) NOT LIKE '%Nutch%'
AND cs(User-Agent) NOT LIKE '%SkimBot%'
AND cs(User-Agent) NOT LIKE '%WhatWeb%'
AND cs(User-Agent) NOT LIKE '%Googlebot%'
AND cs(User-Agent) NOT LIKE '%Yahoo%'
AND cs(User-Agent) NOT LIKE '%Netcraft%'
AND cs(User-Agent) NOT LIKE '%AhrefsBot%'
AND cs(User-Agent) NOT LIKE '%SemrushBot%'
AND cs(User-Agent) NOT LIKE '%MJ12bot%'
AND cs(User-Agent) NOT LIKE '%DotBot%'
AND cs(User-Agent) NOT LIKE '%Vagabondo%'
AND cs(User-Agent) NOT LIKE '%NetSeer%'
AND cs(User-Agent) NOT LIKE '%PHP%'

/* Dont display any cs(User-Agent) which has an entry of nothing */

AND cs(User-Agent) is NOT NULL 

GROUP by Browser
ORDER by Hits DESC

One comment

  1. Pingback: Log Parser Studio – User Agent Platform / Device Query | Raymond Reid – LRT

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s