extract data from user agent string  [ 877 views ]

Goal: collect some information about visitors

There is some information in the air. The basic source is the user agent string which used by browsers and other gadgets on the internet to show some information about the visitor. Let’s suppose we have a table which contains the user agent strings. From this data we can create a quick report about our visitors.
Here is a small sample an oracle view how can I extract some basic information (operating system, browser type) from this data.

CREATE OR REPLACE VIEW V_AGENTS (OS, USER_AGENT)
AS 
select distinct /*+index(t INX_USER_AGENT)*/
  case when instr(t.user_agent,'Firefox') > 0 then 'Firefox'
       when instr(t.user_agent,'rv:11.0') > 0 then 'IE 11'
       when instr(t.user_agent,'MSIE 9.0') > 0 then 'IE 9'
       when instr(t.user_agent,'MSIE 8.0') > 0 then 'IE 8'          
       when instr(t.user_agent,'MSIE 7.0') > 0 then 'IE 7'
       when instr(t.user_agent,'MSIE 6.0') > 0 then 'IE 6'
       when instr(t.user_agent,'MSIE 5.5') > 0 then 'IE 5.5'
       when instr(t.user_agent,'MSIE 5.01') > 0 then 'IE 5.01'
       when instr(t.user_agent,'MSIE 5.0') > 0 then 'IE 5.0'
       when instr(t.user_agent,'Chrome') > 0 then 'Chrome'
       when instr(t.user_agent,'Opera') > 0 then 'Opera'
       when instr(t.user_agent,'Safari') > 0 then 'Safari'
  end Browser,
  case when instr(t.user_agent,'Windows CE') > 0 then 'Windows CE'
       when instr(t.user_agent,'Windows 95') > 0 then 'Windows 95'
       when instr(t.user_agent,'Windows 98; Win 9x 4.90') > 0 then 'Windows Me'
       when instr(t.user_agent,'Windows 98') > 0 then 'Windows 98'
       when instr(t.user_agent,'Win98') > 0 then 'Windows 98'
       when instr(t.user_agent,'Windows NT 4.0') > 0 then 'Windows NT 4.0'
       when instr(t.user_agent,'Windows NT 5.01') > 0 then 'Windows 2000, Service Pack 1 (SP1)'
       when instr(t.user_agent,'Windows NT 5.0') > 0 then 'Windows 2000'
       when instr(t.user_agent,'Windows NT 5.1') > 0 then 'Windows XP'
       when instr(t.user_agent,'Windows NT 5.2') > 0 then 'Windows 2003; Windows XP/x64'
       when instr(t.user_agent,'Windows NT 6.0') > 0 then 'Windows Vista'
       when instr(t.user_agent,'Windows NT 6.1') > 0 then 'Windows 7'
       when instr(t.user_agent,'Windows NT 6.2') > 0 then 'Windows 8'
       when instr(t.user_agent,'Windows NT 6.3') > 0 then 'Windows 8.1'
       when instr(t.user_agent,'Mac OS X') > 0 then 'Macintosh'
       when instr(t.user_agent,'Android') > 0 then 'Android' 
       when instr(t.user_agent,'Linux') > 0 then 'Linux' 
  end OS
 from my_table t
where t.user_agent is not null
  and t.user_agent != 'Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp)'
  and lower(t.user_agent) not like '%spider%'
  and lower(t.user_agent) not like '%bot%'
/

This view provides a not too detailed summary image about my visitors.
nota bene: There are many robots hitting the page. We need to drop those out from this view.

The result like this sample
dailystat

The unsupported WinXP the favourite os and my visitors use chrome mostly, firefox and some kind of internet explorer. We have 1 visitor with ie6. May be we need to offer him to upgrade the browser…

#sidebar a { color:#fff; } #sidebar ul ul li { color: #DEF585; } #sidebar h2 { color: #fff; } #sidebar ul p, #sidebar ul select { color: #BEDDBE; } #backfly { background: url(images/golfBallWallPaper.jpg) left bottom fixed repeat-x #65a51d; }