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

Question

I am looking for find out the browser name and version, OS name and version from User Agent field of IIS log file through Log parser query.

As the User-Agent string has different format for every browser and device how could I get the browser name and version exactly from each string through a log parser query? Actually going to store full UA string in db table. So is any other function available in SQL to get browser and version number from the stored field value?

I tried this query to find 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 in SQL to get browser name? And also how to get browser version?

Solution 1

If you want the details of user agents from IIS Log files you need to use the Log Parser. You can use the following query to get the 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 have installed LogParser if not installed please install from here and try using the following way

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

It will generate an 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.

Solution 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

Comments

[user1905397]

Thanks for you response. I got the user agent string from IIS log file. But i am trying to get browser name and version from the User Agent string. Since every user agent string has different format i dont know how particularly get browser name and version. I am looking for function either to use in Log parser query or in SQL query to get browser name and version number.

[Admin]

notsomany.wordpress.com/2014/05/01/…

[xlecoustillier]

Please edit your answer to add the links you provided in the comments.

[Luís Cruz]

I will go further and say: Please edit your answer and add some description to your solution and, if you add the links, provide the relevant information about them.