T-SQL statement to breakdown UserAgent information

Question

I have a table that contains the UserAgent string and a Count of how many times its seen. The T-SQL below is used to give a breakdown of what browsers are seen and how often then are seen.

I'm looking for comments (good or bad) and suggestions (good only).

SELECT
Browser,
LEFT(Version, CHARINDEX('.', Version + '.', CHARINDEX('.', Version + '.0') + 1) - 1) AS Version,
SUM(Count) AS Count
FROM
(
SELECT
CASE
 WHEN UserAgent LIKE '%Firefox/%' THEN 'Firefox'
 WHEN UserAgent LIKE '%Chrome/%' THEN 'Chrome'
 WHEN UserAgent LIKE '%MSIE %' THEN 'IE'
 WHEN UserAgent LIKE '%MSIE+%' THEN 'IE'
 WHEN UserAgent LIKE '%iPhone%' THEN 'iPhone Safari'
 WHEN UserAgent LIKE '%iPad%' THEN 'iPad Safari'
 WHEN UserAgent LIKE '%Opera%' THEN 'Opera'
 WHEN UserAgent LIKE '%BlackBerry%' AND UserAgent LIKE '%Version/%' THEN 'BlackBerry WebKit'
 WHEN UserAgent LIKE '%BlackBerry%' THEN 'BlackBerry'
 WHEN UserAgent LIKE '%Android%' THEN 'Android'
 WHEN UserAgent LIKE '%Safari%' THEN 'Safari'
 WHEN UserAgent LIKE '%bot%' THEN 'Bot'
 WHEN UserAgent LIKE '%http://%' THEN 'Bot'
 WHEN UserAgent LIKE '%www.%' THEN 'Bot'
 WHEN UserAgent LIKE '%Wget%' THEN 'Bot'
 WHEN UserAgent LIKE '%curl%' THEN 'Bot'
 WHEN UserAgent LIKE '%urllib%' THEN 'Bot'
 ELSE 'Unknown'
END AS Browser,
CASE
 WHEN UserAgent LIKE '%Firefox/%' THEN LEFT(RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Firefox/', UserAgent + ' ') - 6), CHARINDEX(' ', RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Firefox/', UserAgent + ' ') - 6)) - 1)
 WHEN UserAgent LIKE '%Chrome/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Chrome/', UserAgent) - 6), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Chrome/', UserAgent) - 6)) - 1)
 WHEN UserAgent LIKE '%MSIE %' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE ', UserAgent + ';') - 4), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE ', UserAgent + ';') - 4)) - 1)
 WHEN UserAgent LIKE '%MSIE+%' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE+', UserAgent + ';') - 4), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE+', UserAgent + ';') - 4)) - 1)
 WHEN UserAgent LIKE '%iPhone%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
 WHEN UserAgent LIKE '%iPad%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
 WHEN UserAgent LIKE '%Opera%' THEN LEFT(RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Opera/', UserAgent + ' ') - 4), CHARINDEX(' ', RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Opera/', UserAgent + ' ') - 4)) - 1)
 WHEN UserAgent LIKE '%BlackBerry%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
 WHEN UserAgent LIKE '%BlackBerry%' THEN RIGHT(LEFT(UserAgent + ' ', CHARINDEX(' ', UserAgent + ' ') - 1), LEN(LEFT(UserAgent + ' ', CHARINDEX(' ', UserAgent + ' ') - 1)) - CHARINDEX('/', LEFT(UserAgent + ' ', CHARINDEX(' ', UserAgent + ' ') - 1)))
 WHEN UserAgent LIKE '%Android%' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('Android ', UserAgent + ';') - 7), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('Android ', UserAgent + ';') - 7)) - 1)
 WHEN UserAgent LIKE '%Safari%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
 WHEN UserAgent LIKE '%bot%' THEN '0.0'
 WHEN UserAgent LIKE '%http://%' THEN '0.0'
 WHEN UserAgent LIKE '%www.%' THEN '0.0'
 WHEN UserAgent LIKE '%Wget%' THEN '0.0'
 WHEN UserAgent LIKE '%curl%' THEN '0.0'
 WHEN UserAgent LIKE '%urllib%' THEN '0.0'
 ELSE '0.0'
END AS Version,
Count
FROM AnalyticsBrowsers
) as Browsers
WHERE
Browser NOT LIKE 'Bot'
GROUP BY
Browser, LEFT(Version, CHARINDEX('.', Version + '.', CHARINDEX('.', Version + '.0') + 1) - 1)
ORDER BY Count DESC

Output from a live website data collected 8/25/2011 to 9/7/2011:

Browser Version Count
IE 8.0 1495
IE 7.0 659
IE 6.0 470
Chrome 13.0 354
Firefox 6.0 345
IE 9.0 252
Firefox 3.6 213
Firefox 5.0 156
iPhone Safari 5.0 110
Safari 5.0 98
Safari 5.1 77
Unknown 0.0 47
iPad Safari 5.0 41
Firefox 4.0 34
Android 2.2 31
Firefox 3.5 16
Firefox 3.0 15
Chrome 14.0 11
Opera 9.80 11
Chrome 11.0 10
iPhone Safari 4.0 10
Android 2.3 8
iPhone Safari 0.0 7
Safari 4.0 7
Firefox 2.0 5
Android 2.1-update1 4
Firefox 1.5 4
Chrome 10.0 4
BlackBerry 4.5 4
BlackBerry 5.0 4
IE 5.5 4
Opera 9.24 4
Firefox 7.0 4
Chrome 9.0 3
BlackBerry 4.7 3
Safari 4.1 3
iPad Safari 4.0 3
Chrome 15.0 3
Safari 3.0 2
Safari 0.0 2
BlackBerry WebKit 6.0 2
iPhone Safari 5.1 2
Firefox 8.0a2 2
IE 5.01 1
Chrome 5.0 1
Android 1.5 1
Chrome 12.0 1
Safari 3.2 1

Answer 1

You can eliminate a few of the conditions by combining them. Your two checks for IE in the "Browser" section can be combined into one WHEN UserAgent LIKE '%MSIE%' since you don't actually report those as different entities. Also, your "Version" section doesn't need explicit checks for all of the bot agents since you just report 0.0 for all of them. You can just let that fall to the ELSE clause.

Answer 2

Not a full answer but I would personally try to store all the mapping you use in the "case" statement in table. So you could replace your sub query with all these cases by a join.

Answer 3

Just first impression...not to criticize...

Personally, I'd recommend normalizing your AnalyticBrowsers table just a bit more... Separate columns for browser and version at a minimum.

This will not only make your function-heavy / complex sql easier to write and process...

SELECT
 Browser,
 Version,
 SUM(Count) AS Count
FROM
 AnalyticsBrowsers
WHERE
 Browser NOT LIKE 'Bot'
GROUP BY
 Browser,
 Version
ORDER BY 
 Count DESC

But it will also make your data more scalable, in case you ever in the future want to keep data in new tables that is relatable to 'firefox', etc.

As for content...data from this kind of query doesn't mean much without a date context. I guess you commented about that, but it wasn't in your SQL, so FWIW.

Parse the useragent value before inserting into your AnalyticBrowsers table. Your web page could do this, or a stored procedure in your database, build it into your INSERT syntax, etc.

• Justin808: Hi, thanks for the comments. I do have date information, just left if off this sql :) I do have a browser and version columns as well but am not using them as the data is not of value. The information in the columns is provided by c# HttpContext.Current.Request.Browser and that data is way out of date.

Answer 4

Thanks for that list of the cases, I was getting many Unknowns results, after investigation it turns out that IE11 was not detected as IE, so please consider to add one additional CASE statement:

 WHEN UserAgent LIKE '%Windows NT%Trident%rv:%' THEN 'IE'

it's based according the specification you can find here: https://msdn.microsoft.com/en-us/library/ms537503(v=vs.85).aspx

Answer 5

Keep in mind that the LEN function doesn't count trailing spaces. That means that LEN(UserAgent + ' ') is actually equal to LEN(UserAgent). The linked SO question/answer suggests using DATALENGTH instead (or DATALENGTH(x)/2 for unicode strings). This will help future developers who have to maintain your code and may not be familiar with the specific behavior of the LEN function.