There is no real way to extract browser name and version from browser agent header. Inspired from this links, i propose my query suggest :
- How should I deal with user agent parsing in logs? (statement with SQL Server)
- Understanding user-agent strings Internet Explorer user-agent tokens : Version Platform Feature and Trident token
- T-SQL Function: How to parse UserAgent to extract Browser Name (function statement with SQL Server)
- PHP : get_browser - Tells what the user's browser is capable of parent, platform, browser, version, ...
- Group user agents by browser
/* * Extract distinct browser+version from a table named "web_log" with http user agent stored in column "HTTP_USER_AGENT" */ -- SELECT COUNT( * ) AS `Lines` , `BROWSER` , `HTTP_USER_AGENT` SELECT COUNT( * ) AS `Lines` , `BROWSER` FROM ( SELECT CASE /* Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0) Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; Trident/6.0) */ WHEN `HTTP_USER_AGENT` REGEXP 'MSIE [[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('MSIE ', `HTTP_USER_AGENT`), LOCATE(';', `HTTP_USER_AGENT`, LOCATE('MSIE ', `HTTP_USER_AGENT`))-LOCATE('MSIE ', `HTTP_USER_AGENT`)) /* Mozilla/5.0 (Windows; U; Windows NT 6.0; fr; rv:1.9.2) Gecko/20100115 AskTbORJ/3.15.15.36191 Firefox/3.6 ( .NET CLR 3.5.30729; .NET4.0C) Mozilla/5.0 (Windows NT 5.1; rv:19.0) Gecko/20100101 Firefox/19.0 */ WHEN `HTTP_USER_AGENT` REGEXP 'Firefox/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('Firefox/', `HTTP_USER_AGENT`)) /* Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.64 Safari/537.31 Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.116 Safari/537.36 */ WHEN `HTTP_USER_AGENT` REGEXP 'Chrome/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('Chrome/', `HTTP_USER_AGENT`)) /* Mozilla/5.0 (Macintosh; Intel Mac OS X 10_5_8) AppleWebKit/534.50.2 (KHTML, like Gecko) Version/5.0.6 Safari/533.22.3 Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.58.2 (KHTML, like Gecko) Version/5.1.8 Safari/534.58.2 */ WHEN `HTTP_USER_AGENT` REGEXP 'Safari/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('Safari/', `HTTP_USER_AGENT`)) /* Mozilla/5.0 (Windows; U; Windows NT 6.1; fr; rv:1.9.1.16) Gecko/20101123 SeaMonkey/2.0.11 */ WHEN `HTTP_USER_AGENT` REGEXP 'SeaMonkey/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('SeaMonkey/', `HTTP_USER_AGENT`)) /* HTC_Touch_HD_T8282-orange/PPC; 480x800; OpVer 34.159.2.733 Opera/9.50(Microsoft Windows 5.1; U;fr) PPC; 480x800; HTC_HD2_T8585-Orange; OpVer 156.314.2.732 Opera/9.7 (Microsoft Windows 6.5; U;fr) Opera/9.80 (Windows Mobile; Opera Mini/5.1.21594/28.1914; U; fr) Presto/2.8.119 Version/11.10 Opera/9.80 (S60; SymbOS; Opera Mobi/1209; U; fr) Presto/2.5.28 Version/10.1 */ -- WHEN `HTTP_USER_AGENT` REGEXP '^Opera/[[:alnum:]]+.[[:alnum:]]+ ' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('Opera/', `HTTP_USER_AGENT`), LOCATE(' ', `HTTP_USER_AGENT`, LOCATE('Opera/', `HTTP_USER_AGENT`))-LOCATE('Opera/', `HTTP_USER_AGENT`)) WHEN `HTTP_USER_AGENT` REGEXP 'Opera/[[:alnum:]]+.[[:alnum:]]+ ' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('Opera/', `HTTP_USER_AGENT`), LOCATE(' ', `HTTP_USER_AGENT`, LOCATE('Opera/', `HTTP_USER_AGENT`))-LOCATE('Opera/', `HTTP_USER_AGENT`)) /* Mozilla/5.0 (SAMSUNG; SAMSUNG-GT-S8530-ORANGE/S8530BVJL2; U; Bada/1.2; fr-fr) AppleWebKit/533.1 (KHTML, like Gecko) Dolfin/2.2 Mobile WVGA SMM-MMS/1.2.0 NexPlayer/3.0 profile/MIDP-2.1 configuration/CLDC-1.1 OPN-B Mozilla/5.0 (SAMSUNG; SAMSUNG-GT-S8600-ORANGE/S8600BVKK3; U; Bada/2.0; fr-fr) AppleWebKit/534.20 (KHTML, like Gecko) Dolfin/3.0 Mobile WVGA SMM-MMS/1.2.0 NexPlayer/3.0 profile/MIDP-2.1 configuration/CLDC-1.1 OPN-B */ WHEN `HTTP_USER_AGENT` REGEXP 'Dolfin/[[:alnum:]]+.[[:alnum:]]+ ' THEN SUBSTRING(`HTTP_USER_AGENT`, LOCATE('Dolfin/', `HTTP_USER_AGENT`), LOCATE(' ', `HTTP_USER_AGENT`, LOCATE('Dolfin/', `HTTP_USER_AGENT`))-LOCATE('Dolfin/', `HTTP_USER_AGENT`)) /* Mozilla/5.0 (iPad; CPU OS 6_1_3 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Mobile/10B329 AppleCoreMedia/1.0.0.10B329 (iPhone; U; CPU OS 6_1_3 like Mac OS X; fr_fr) +--------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Lignes | BROWSER | HTTP_USER_AGENT | +--------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 6381 | UNKNOW | HTC_Touch_HD_T8282-orange/PPC; 480x800; OpVer 34.159.2.733 Opera/9.50(Microsoft Windows 5.1; U;fr) | | 270 | UNKNOW | SAMSUNG-GT-S5230-Bouygues/S5230AGIF2 SHP/VPP/R5 Jasmine/0.8 Nextreaming SMM-MMS/1.2.0 profile/MIDP-2.1 configuration/CLDC-1.1 | | 180 | UNKNOW | Mozilla/5.0 (Symbian/3; Series60/5.2 NokiaC7-00/013.016; Profile/MIDP-2.1 Configuration/CLDC-1.1 ) AppleWebKit/525 (KHTML, like Gecko) Version/3.0 BrowserNG/7.2.8.10 3gpp-gba | | 13 | UNKNOW | Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_2_1 like Mac OS X; fr-fr) AppleWebKit/533.17.9 (KHTML, like Gecko) Mobile/8C148 | | 2 | UNKNOW | NULL | | 1 | UNKNOW | HTC_Touch_HD_T8282-orange/PPC; 480x800; OpVer 34.119.2.731 Opera/9.50(Microsoft Windows 5.1; U;fr) | +--------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ */ ELSE 'UNKNOW' END AS BROWSER , `HTTP_USER_AGENT` FROM `web_log` -- WHERE HTTP_USER_AGENT LIKE '%opera%' -- WHERE `date` > '2012-01-01 00:00:00' ) AS table_user_agent_filtred -- WHERE `BROWSER` = 'UNKNOW' GROUP BY `BROWSER` -- GROUP BY `BROWSER`, `HTTP_USER_AGENT` ORDER BY `Lines` DESC ; -- 149 rows in set (3 min 52.73 sec) /* +--------+-------------------------------------------+ | Lines | BROWSER | +--------+-------------------------------------------+ | 372929 | Safari/533.1 | | 164338 | Chrome/23.0.1271.97 Safari/537.11 | | 98857 | MSIE 8.0 | | 97130 | MSIE 9.0 | | 68900 | Firefox/14.0.1 | | 67151 | MSIE 6.0 | | 54835 | Opera/9.80 | | 52894 | Chrome/12.0.742.112 Safari/534.30 | | 52333 | Chrome/22.0.1229.94 Safari/537.4 | | 50105 | Chrome/23.0.1271.95 Safari/537.11 | | 37141 | Chrome/23.0.1271.64 Safari/537.11 | | 33968 | Chrome/21.0.1180.89 Safari/537.1 | | 31009 | Firefox/15.0.1 | | 27596 | Chrome/20.0.1132.57 Safari/536.11 | | 26947 | Chrome/19.0.1084.56 Safari/536.5 | | 25467 | Chrome/19.0.1084.52 Safari/536.5 | | 20457 | Safari/530.17 | | 19488 | Chrome/22.0.1229.79 Safari/537.4 | | 19255 | Chrome/24.0.1312.52 Safari/537.17 | | 18824 | Safari/7534.48.3 | | 18449 | Chrome/21.0.1180.83 Safari/537.1 | | 18053 | Firefox/13.0.1 | | 18018 | Opera/9.5 | | 17528 | Chrome/20.0.1132.47 Safari/536.11 | | 16649 | Firefox/16.0 | | 15875 | Firefox/12.0 | | 14798 | Chrome/17.0.963.56 Safari/535.11 | | 13927 | Chrome/16.0.912.75 Safari/535.7 | | 13898 | Firefox/17.0 | | 13133 | Chrome/16.0.912.77 Safari/535.7 | | 12443 | Safari/8536.25 | | 11716 | Firefox/13.0 | | 11345 | Firefox/15.0 | | 11189 | MSIE 7.0 | | 11051 | Opera/9.50 | | 10205 | Chrome/18.0.1025.162 Safari/535.19 | | 8705 | Safari/6533.18.5 | | 8398 | Chrome/21.0.1180.60 Safari/537.1 | | 7826 | Chrome/24.0.1312.56 Safari/537.17 | | 7086 | Chrome/18.0.1025.168 Safari/535.19 | | 6977 | Firefox/11.0 | | 6847 | UNKNOW | | 6301 | Chrome/21.0.1180.79 Safari/537.1 | | 5526 | Chrome/23.0.1271.91 Safari/537.11 | | 5422 | Chrome/16.0.912.63 Safari/535.7 | | 5129 | Chrome/17.0.963.79 Safari/535.11 | | 4785 | Firefox/10.0.2 | | 4614 | Chrome/21.0.1180.75 Safari/537.1 | | 4449 | Chrome/17.0.963.83 Safari/535.11 | | 3841 | Chrome/18.0.1025.152 Safari/535.19 | | 3585 | Chrome/18.0.1025.142 Safari/535.19 | | 3500 | Firefox/9.0.1 | | 3454 | Opera/9.7 | | 3258 | Firefox/18.0 | | 3255 | Firefox/10.0.4 Fennec/10.0.4 | | 3126 | Chrome/22.0.1229.92 Safari/537.4 | | 2969 | Chrome/19.0.1084.46 Safari/536.5 | | 2709 | Chrome/17.0.963.78 Safari/535.11 | | 2599 | Chrome/4.1.249.1045 Safari/532.5 | | 2449 | Chrome/17.0.963.46 Safari/535.11 | | 2300 | Safari/525.20.1 | | 1643 | Firefox/13.0 Fennec/13.0 | | 1621 | Safari/533.1 MMS/LG-Android-MMS-V1.0/1.2 | | 1587 | Chrome/17.0.963.66 Safari/535.11 | | 1414 | Chrome/15.0.874.121 Safari/535.2 | | 1358 | Safari/534.30 | | 1265 | Firefox/8.0 | | 1208 | Firefox/9.0 Fennec/9.0 | | 1151 | Chrome/21.0.1180.77 Safari/537.1 | | 1108 | Firefox/10.0.1 | | 760 | Safari/534.8+ | | 660 | Firefox/10.0.3 Fennec/10.0.3 | | 635 | Safari/6531.22.7 | | 493 | Chrome/18.0.1025.151 Safari/535.19 | | 472 | Firefox/10.0 | | 385 | Firefox/10.0.2 Fennec/10.0.2 | | 382 | Chrome/22.0.1229.96 Safari/537.4 | | 378 | Firefox/10.0.5 Fennec/10.0.5 | | 193 | Firefox/21.0 | | 174 | Firefox/3.6.3 (.NET CLR 3.5.30729) | | 169 | Firefox/10.0 Fennec/10.0 | | 166 | Firefox/19.0 | | 137 | Firefox/20.0 | | 100 | Chrome/17.0.963.65 Safari/535.11 | | 98 | Firefox/10.0.1 Fennec/10.0.1 | | 82 | Chrome/19.0.1084.54 Safari/536.5 | | 69 | Firefox/14.0 | | 66 | Firefox/7.0 | | 57 | Chrome/18.0.1025.163 Safari/535.19 | | 53 | Chrome/18.0.1025.165 Safari/535.19 | | 52 | Firefox/9.0 | | 46 | Firefox/8.0.1 | | 43 | MSIE 10.0 | | 43 | Firefox/22.0 | | 38 | Chrome/26.0.1410.64 Safari/537.31 | | 32 | Chrome/25.0.1364.172 Safari/537.22 | | 29 | Safari/534.13 | | 23 | Safari/536.25 | | 16 | Chrome/18.0.1025.166 Mobile Safari/535.19 | | 14 | Safari/525 | | 12 | Safari/534.52.7 | | 12 | Chrome/12.0.742.122 Safari/534.30 | | 11 | Chrome/20.0.1087.0 Safari/536.6 | | 10 | Safari/536.26.14 | | 8 | Firefox/8.0 webissimo3/3.4.11+svn138183.0 | | 7 | Safari/534.57.2 | | 7 | Safari/533.22.3 | | 7 | Firefox/3.6.8 ( .NET CLR 3.5.30729) | | 6 | Chrome/11.0.696.34 Safari/534.24 | | 5 | Chrome/6.0.495.0 Safari/534.6 | | 5 | Firefox/7.0.1 | | 4 | Safari/534.55.3 | | 4 | Safari/534.56.5 | | 3 | Dolfin/2.2 | | 3 | Firefox/3.6.13 (.NET CLR 3.5.30729) | | 3 | Safari/536.26.17 | | 1 | Safari/531.21.10 | | 1 | Dolfin/3.0 | +--------+-------------------------------------------+ 118 rows in set (3 min 26.34 sec) */