Xtykutl

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?

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