Get Browser and Operating System from the user agent stored in MySql database.

Here is how to get Browser and Operating System from the user agent stored in MySql database.

select 

user_agent,

CASE

WHEN user_agent REGEXP 'MSIE [[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(user_agent, LOCATE('MSIE ', user_agent), LOCATE(';', user_agent, LOCATE('MSIE ', user_agent))-LOCATE('MSIE ', user_agent))

WHEN user_agent REGEXP 'Firefox/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(user_agent, LOCATE('Firefox/', user_agent))

WHEN user_agent REGEXP 'Chrome/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(user_agent, LOCATE('Chrome/', user_agent))

WHEN user_agent REGEXP 'Safari/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(user_agent, LOCATE('Safari/', user_agent))

WHEN user_agent REGEXP 'SeaMonkey/[[:alnum:]]+.[[:alnum:]]+' THEN SUBSTRING(user_agent, LOCATE('SeaMonkey/', user_agent))

WHEN user_agent REGEXP 'Opera/[[:alnum:]]+.[[:alnum:]]+ ' THEN SUBSTRING(user_agent, LOCATE('Opera/', user_agent), LOCATE(' ', user_agent, LOCATE('Opera/', user_agent))-LOCATE('Opera/', user_agent))

WHEN user_agent REGEXP 'Dolfin/[[:alnum:]]+.[[:alnum:]]+ ' THEN SUBSTRING(user_agent, LOCATE('Dolfin/', user_agent), LOCATE(' ', user_agent, LOCATE('Dolfin/', user_agent))-LOCATE('Dolfin/', user_agent))

WHEN user_agent REGEXP 'AppleWebkit/[[:alnum:]]+.[[:alnum:]]+ ' THEN SUBSTRING(user_agent, LOCATE('AppleWebkit/', user_agent), LOCATE(' ', user_agent, LOCATE('AppleWebkit/', user_agent))-LOCATE('AppleWebkit/', user_agent))

ELSE 'Unknown'

END AS BROWSER,

case 

when instr(user_agent,'compatible')>0 && instr(user_agent,';')>0 && instr(user_agent,'Windows')>0 then CONCAT('Windows',(substring_index(substring_index(user_agent,'; Windows',-1),';',1))) 

when instr(user_agent,';')>0 && right(user_agent,1)!=')' then trim(substring_index(substring_index(user_agent,';',1),'(',-1)) 

when instr(user_agent,';')=0 && right(user_agent,1)=')' then substring_index(substring_index(user_agent,')',1),'(',-1) 

when right(user_agent,1)=')' then trim(substring_index(substring_index(user_agent,';',1),'(',-1)) 

else trim(substring_index(substring_index(user_agent,')',1),'(',-1)) end as 'OS'

from browsers

/*Fetch all records where user_agent is not null*/

where user_agent is not null 

/*Fetch those records whose user_agent is available*/

and instr(user_agent,'Mozilla/5.0')>0 

/*Exclude Google bots and other bots*/

and instr(user_agent,'http')=0