MySQL Query For Browser Version

Question

I have a table in my MySQL database that stores login data, and I store the useragent header info, for example:

{"userAgent":"Mozilla\/5.0 (Windows NT 6.3; WOW64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/54.0.2840.71 Safari\/537.36"}

I am trying to query which browser and version people are using to access the site. This is the query I have so far:

SELECT
Browser,
COUNT(Browser) 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'
        ELSE 'Unknown'
    END AS Browser
    FROM user_log
)
AS Browsers
GROUP BY Browser

My question is how can I add the browser version to this query?

Answer 1

I managed to figure this out, I hope this is useful to someone in the future:

SELECT
Browser,
Version,
COUNT(Browser) AS Count
FROM
(
    SELECT
    CASE
        WHEN userAgent LIKE '%Firefox%' THEN 'Firefox'
        WHEN userAgent LIKE '%Chrome%' THEN 'Chrome'
        WHEN userAgent LIKE '%MSIE %' THEN 'IE'
        ELSE 'Unknown'
    END AS Browser,
    CASE
        WHEN userAgent LIKE '%Firefox%' THEN SUBSTRING(userAgent, LOCATE('Firefox', userAgent) + 9, POSITION('.' IN SUBSTRING(userAgent, LOCATE('Firefox', userAgent) + 9)) + 1)
        WHEN userAgent LIKE '%Chrome%' THEN SUBSTRING(userAgent, LOCATE('Chrome', userAgent) + 8, POSITION('.' IN SUBSTRING(userAgent, LOCATE('Chrome', userAgent) + 8)) + 1)
        WHEN userAgent LIKE '%MSIE %' THEN SUBSTRING(userAgent, LOCATE('MSIE ', userAgent) + 5, POSITION('.' IN SUBSTRING(userAgent, LOCATE('MSIE ', userAgent) + 5)) + 1)
    ELSE 'Unknown'
    END AS Version
    FROM user_log
)
AS Browsers
GROUP BY Browser, Version