Get browser name and version from IIS log file in Log Parser

Question

I searched for the name and version of the browser, the name and OS version from the User Agent field from the IIS log file through a log parser request.

Since the User-Agent string has a different format for each browser and device, how can I get the name and version of the browser exactly from each line through a log parser request? Actually going to store the full UA row in the db table. That is, any other function available in SQL to get the browser and version number from the stored field value?

I tried this query to find the browser name:

SELECT top 100 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), 'Chrome') when 1 THEN 'Chrome' else case strcnt(cs(user-agent), 'MSIE ') when 1 THEN 'IE' else case strcnt(cs(user-agent), 'Safari ') when 1 THEN 'Safari' else case strcnt(cs(user-agent), 'Opera ') when 1 THEN 'Opera' ELSE 'Unknown' End End End End End End End as Browser 

Is there any other function available in Log Parser or SQL to get the browser name? And also how to get the browser version?

Answer 1

If you need user agent details from IIS log files, you need to use Log Parser. You can use the following query to get user agents.

 SELECT cs(User-Agent) As UserAgent, COUNT(*) as Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY UserAgent ORDER BY Hits DESC 

Hope you installed LogParser, if not installed, install here and try using the following path

 LogParser.exe -i:W3C "Query" -o:CSV 

It will generate output similar to the following

 UserAgent Hits iisbot/1.0+(+http://www.iis.net/iisbot.html) 104 Mozilla/4.0+(compatible;+MSIE+8.0;… 77 Microsoft-WebDAV-MiniRedir/6.1.7600 23 DavClnt 

You can read more from here .

Answer 2

 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