Tell OS from user agent in a MySQL query, for stats and security research. Just the select part. Also allows ordering by OS safety.

os_from_ua.sql

select case
when user_agent like '%iPad%' then '4.iPad'
when user_agent like '%iPhone%' then '4.iPhone'
when user_agent like '%Android%' then '4.Android'
when user_agent like '%Mac OS X%' then '4.OS X'
when user_agent like '%X11%' then '4.Linux'
when user_agent like '%Windows NT 6.3%' then '3.Windows 8.1'
when user_agent like '%Windows NT 6.2%' then '3.Windows 8'
when user_agent like '%Windows NT 6.1%' then '3.Windows 7'
when user_agent like '%Windows NT 6.0%' then '2.Windows Vista'
when user_agent like '%Windows NT 5.2%' then '1.Windows Server 2003; Windows XP x64 Edition'
when user_agent like '%Windows NT 5.1%' then '1.Windows XP'
when user_agent like '%Windows NT 5.0%' then '1.Windows 2000'
when user_agent like '%Windows NT 4.0%' then '1.Microsoft Windows NT 4.0'
when user_agent like '%Windows 9' then '1.Windows 95/98/Millenium'
when user_agent like '%Windows CE' then '1.Windows CE'
else 'Other'
end  OS