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?

Answer 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.