BigQuery (Legacy SQL) - Cloudflare Enterprise Log Share - User Agent Browser Version

gistfile1.txt

-- Looking towards the "Legacy TLS" turndown in June 2018,
-- we needed to analyse which devices / browsers are still accessing, by parsing the User Agent.

-- This is in BigQuery Legacy SQL, with field names based on Cloudflare's Enterprise Log Share data.

CASE
	WHEN ClientRequestUserAgent LIKE '%Windows Phone%' THEN CONCAT('Windows Phone/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'Windows Phone ([0-9\.]+);'), REGEXP_EXTRACT(ClientRequestUserAgent, r'Windows Phone ([0-9\.]+);'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%MSIE%' THEN CONCAT('MSIE/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'MSIE ([0-9\.]+)'), REGEXP_EXTRACT(ClientRequestUserAgent, r'MSIE ([0-9\.]+)'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%Trident%' THEN CONCAT('MSIE/Trident/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'Trident/([0-9\.]+)'), REGEXP_EXTRACT(ClientRequestUserAgent, r'Trident/([0-9\.]+)'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%Firefox%' THEN CONCAT('Firefox/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'Firefox/([0-9\.]+)'), REGEXP_EXTRACT(ClientRequestUserAgent, r'Firefox/([0-9\.]+)'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%Android%' THEN CONCAT('Android/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'Android ([0-9\.]+);'), REGEXP_EXTRACT(ClientRequestUserAgent, r'Android ([0-9\.]+);'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%Chrome%' THEN CONCAT('Chrome/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'Chrome/([0-9\.]+)'), REGEXP_EXTRACT(ClientRequestUserAgent, r'Chrome/([0-9\.]+)'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%iPhone%' THEN CONCAT('iPhone/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'OS ([0-9_]+)'), REPLACE(REGEXP_EXTRACT(ClientRequestUserAgent, r'OS ([0-9_]+)'), '_', '.'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%iPad%' THEN CONCAT('iPad/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'OS ([0-9_]+)'), REPLACE(REGEXP_EXTRACT(ClientRequestUserAgent, r'OS ([0-9_]+)'), '_', '.'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent LIKE '%Safari%' THEN CONCAT('Safari/', IF(REGEXP_MATCH(ClientRequestUserAgent, r'Safari/([0-9\.]+)'), REGEXP_EXTRACT(ClientRequestUserAgent, r'Safari/([0-9\.]+)'), 'UNKNOWN'))
	WHEN ClientRequestUserAgent = '' THEN 'blank'
	WHEN ClientRequestUserAgent LIKE '%spider%' THEN CONCAT('bot/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%bot%' THEN CONCAT('bot/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%http://%' THEN CONCAT('bot/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%https://%' THEN CONCAT('bot/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%www.%' THEN CONCAT('bot/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%Wget%' THEN CONCAT('wget/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%curl%' THEN CONCAT('curl/', '0.0')
	WHEN ClientRequestUserAgent LIKE '%urllib%' THEN CONCAT('urllib/', '0.0')
	ELSE ClientRequestUserAgent
END AS ClientBrowserVersion