SQL – Log Parser Studio – User-Agent

Posted on December 4, 2015December 4, 2015 By David Kittell

Caution: These scripts will take some time to run on large logs.

/* List Browser types used */
SELECT DISTINCT CASE strcnt(cs(user - agent), 'Chrome')
WHEN 1
THEN 'Chrome'
ELSE CASE strcnt(cs(user - agent), 'Firefox')
WHEN 1
THEN 'Firefox'
ELSE CASE strcnt(cs(user - agent), 'MSIE+6')
WHEN 1
THEN 'IE 6'
ELSE CASE strcnt(cs(user - agent), 'MSIE+7')
WHEN 1
THEN 'IE 7'
ELSE CASE strcnt(cs(user - agent), 'MSIE+8')
WHEN 1
THEN 'IE 8'
ELSE CASE strcnt(cs(user - agent), 'MSIE+9')
WHEN 1
THEN 'IE 9'
ELSE CASE strcnt(cs(user - agent), 'MSIE+10')
WHEN 1
THEN 'IE 10'
ELSE CASE strcnt(cs(user - agent), 'IE+11')
WHEN 1
THEN 'IE 11'
ELSE CASE strcnt(cs(user - agent), '+rv:11')
WHEN 1
THEN 'IE 11'
ELSE CASE strcnt(cs(user - agent), 'Opera')
WHEN 1
THEN 'Opera'
ELSE CASE strcnt(cs(user - agent), 'Safari/')
WHEN 1
THEN 'Safari'
ELSE CASE strcnt(cs(user - agent), 'iPhone+OS+5')
WHEN 1
THEN 'Safari'
ELSE CASE strcnt(cs(user - agent), 'iPhone+OS+6')
WHEN 1
THEN 'Safari'
ELSE CASE strcnt(cs(user - agent), 'iPhone+OS+7')
WHEN 1
THEN 'Safari'
ELSE CASE strcnt(cs(user - agent), 'CPU+OS+6')
WHEN 1
THEN 'Safari'
ELSE CASE strcnt(cs(user - agent), 'iTunes/11')
WHEN 1
THEN 'iTunes'
ELSE CASE strcnt(cs(user - agent), 'iTunes/10')
WHEN 1
THEN 'iTunes'
ELSE CASE strcnt(cs(user - agent), 'ZmEu')
WHEN 1
THEN 'Attack - ZmEu' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'bash+')
WHEN 1
THEN 'Attack - ShellShock Bash Hack Attempt' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'echo+')
WHEN 1
THEN 'Attack - ShellShock Bash Hack Attempt' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), '(){+:;+};')
WHEN 1
THEN 'Attack - ShellShock Bash Hack Attempt' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), '()+{+:;}')
WHEN 1
THEN 'Attack - ShellShock Bash Hack Attempt' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'Jorgee')
WHEN 1
THEN 'Attack - Jorgee' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'the+beast')
WHEN 1
THEN 'Attack - the+beast' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'x00_-gawa.sa.pilipinas.2015')
WHEN 1
THEN 'Attack - x00_-gawa.sa.pilipinas.2015' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'CVE-2014-6271+')
WHEN 1
THEN 'Attack - CVE-2014-6271' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'Morfeus+strikes+again')
WHEN 1
THEN 'Attack - Morfeus+strikes+again' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'Morfeus+Fucking+Scanner')
WHEN 1
THEN 'Attack - Morfeus+Fucking+Scanner' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.15.5+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.19.7+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.19.7+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.19.7+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.21.4+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.22.0+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.22.0+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'curl/7.24.0+')
WHEN 1
THEN 'Curl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'perl+')
WHEN 1
THEN 'Perl' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'Python-')
WHEN 1
THEN 'Python' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'masscan/1.0+')
WHEN 1
THEN 'Security Test - Mass IP Port Scanner - https://github.com/robertdavidgraham/masscan' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'Nikto/2.03+')
WHEN 1
THEN 'Security Test - Nikto' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'ShellShock-Scanner+-+https://github.com/gry/shellshock-scanner/')
WHEN 1
THEN 'Security Test - ShellShock Scanner - https://github.com/gry/shellshock-scanner' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'WPScan+v2.7+(http://wpscan.org)')
WHEN 1
THEN 'Security Test - WPScan - http://wpscan.org' /* Added By DKittell */
ELSE CASE strcnt(cs(user - agent), 'Kazehakase')
WHEN 1
THEN 'UNIX - Kazehakase' /* Added By DKittell */
ELSE cs(user - agent)
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END AS Browser,
COUNT(c - ip) AS Hits
FROM '[LOGFILEPATH]'
/* Dont display any cs(User-Agent) which are created by Bots */
WHERE cs(User - Agent) NOT LIKE '%Java%'
AND cs(User - Agent) NOT LIKE '%Baidu%' /* Added By DKittell */
AND cs(User - Agent) NOT LIKE '%SeznamBot%' /* Added By DKittell */
AND cs(User - Agent) NOT LIKE '%moodle%'
AND cs(User - Agent) NOT LIKE '%twitter%'
AND cs(User - Agent) NOT LIKE '%mymmu%'
AND cs(User - Agent) NOT LIKE '%MMU%'
AND cs(User - Agent) NOT LIKE '%admant%'
AND cs(User - Agent) NOT LIKE '%contextAd%'
AND cs(User - Agent) NOT LIKE '%bingbot%'
AND cs(User - Agent) NOT LIKE '%genieo%'
AND cs(User - Agent) NOT LIKE '%proximic%'
AND cs(User - Agent) NOT LIKE '%PageBot%'
AND cs(User - Agent) NOT LIKE '%feedfetcher%'
AND cs(User - Agent) NOT LIKE '%wordpress%'
AND cs(User - Agent) NOT LIKE '%PictureBot%'
AND cs(User - Agent) NOT LIKE '%WeSEE%'
AND cs(User - Agent) NOT LIKE '%Sogou%'
AND cs(User - Agent) NOT LIKE '%msnbot%'
AND cs(User - Agent) NOT LIKE '%Mediapartner%'
AND cs(User - Agent) NOT LIKE '%MagpieRSS%'
AND cs(User - Agent) NOT LIKE '%Affectv%'
AND cs(User - Agent) NOT LIKE '%Nutch%'
AND cs(User - Agent) NOT LIKE '%SkimBot%'
AND cs(User - Agent) NOT LIKE '%WhatWeb%'
AND cs(User - Agent) NOT LIKE '%Googlebot%'
AND cs(User - Agent) NOT LIKE '%Yahoo%'
AND cs(User - Agent) NOT LIKE '%Netcraft%'
AND cs(User - Agent) NOT LIKE '%AhrefsBot%'
AND cs(User - Agent) NOT LIKE '%SemrushBot%'
AND cs(User - Agent) NOT LIKE '%MJ12bot%'
AND cs(User - Agent) NOT LIKE '%DotBot%'
AND cs(User - Agent) NOT LIKE '%Vagabondo%'
AND cs(User - Agent) NOT LIKE '%NetSeer%'
AND cs(User - Agent) NOT LIKE '%PHP%'
/* Dont display any cs(User-Agent) which has an entry of nothing */
AND cs(User - Agent) IS NOT NULL
GROUP BY Browser
ORDER BY Hits DESC

/* List Platform / Device types used */
 
select distinct
case strcnt(cs(user-agent),'Android') when 1 THEN 'Android'
else case strcnt(cs(user-agent),'Windows+NT+6.3') when 1 THEN 'Windows 8.1'
else case strcnt(cs(user-agent),'Windows+NT+6.2') when 1 THEN 'Windows 8'
else case strcnt(cs(user-agent),'Windows+NT+6.1') when 1 THEN 'Windows 7'
else case strcnt(cs(user-agent),'Windows+7') when 1 THEN 'Windows 7'
else case strcnt(cs(user-agent),'Windows+NT+6.0') when 1 THEN 'Windows Vista'
else case strcnt(cs(user-agent),'Windows+Vista') when 1 THEN 'Windows Vista'
else case strcnt(cs(user-agent),'Windows+NT+5.2') when 1 THEN 'Windows Server 2003 / Windows XP x64 Edition'
else case strcnt(cs(user-agent),'Windows+NT+5.1') when 1 THEN 'Windows XP'
else case strcnt(cs(user-agent),'Windows+NT+5') when 1 THEN 'Windows 2000'
else case strcnt(cs(user-agent),'Windows+NT+4') when 1 THEN 'Microsoft Windows NT 4.0'
else case strcnt(cs(user-agent),'Windows+98') when 1 THEN 'Windows 98'
else case strcnt(cs(user-agent),'Windows+95') when 1 THEN 'Windows 95'
else case strcnt(cs(user-agent),'Windows+CE') when 1 THEN 'Windows CE'
else case strcnt(cs(user-agent),'Darwin/14') when 1 THEN 'iOS'  /* Darwin 14 iPad3+ &amp;amp; iOS7 */
else case strcnt(cs(user-agent),'Darwin/13') when 1 THEN 'iOS' /* Darwin 13 iPhone 4S */
else case strcnt(cs(user-agent),'iPhone') when 1 THEN 'iOS' /* iPhone */
else case strcnt(cs(user-agent),'iPad') when 1 THEN 'iOS' /* iPad */
else case strcnt(cs(user-agent),'OS+X') when 1 THEN 'Mac OSX'
else case strcnt(cs(user-agent),'Symbian') when 1 THEN 'Symbian'
else case strcnt(cs(user-agent),'Windows+Phone') when 1 THEN 'Windows phone'
else case strcnt(cs(user-agent),'CrOS') when 1 THEN 'Chrome OS'
else case strcnt(cs(user-agent),'Unix') when 1 THEN 'Unix'
else case strcnt(cs(user-agent),'BlackBerry+') when 1 THEN 'BlackBerry'
else case strcnt(cs(user-agent),'BB10') when 1 THEN 'BlackBerry'
else case strcnt(cs(user-agent),'Nikto/2.03+') when 1 THEN 'Nikto - Security Test' /* Added By DKittell */
/* else case strcnt(cs(user-agent),'iTunes') when 1 THEN 'iTunes application' */
else case strcnt(cs(user-agent),'Linux') when 1 THEN 'Linux'
ELSE cs(user-agent)
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
End
as Platform/Device,
COUNT(c-ip) as Hits
FROM '[LOGFILEPATH]'
 
/* Dont display any cs(User-Agent) which has an entry of nothing */
 
WHERE cs(User-Agent) is not NULL
 
/* Dont display any cs(User-Agent) which are created by Bots */
 AND cs(User-Agent) NOT LIKE '%SeznamBot%' /* Added By DKittell */
 AND cs(User-Agent) NOT LIKE '%java%'
 AND cs(User-Agent) NOT LIKE '%moodle%'
 AND cs(User-Agent) NOT LIKE '%twitter%'
 AND cs(User-Agent) NOT LIKE '%mymmu%'
 AND cs(User-Agent) NOT LIKE '%MMU%'
 AND cs(User-Agent) NOT LIKE '%ADmant%'
 AND cs(User-Agent) NOT LIKE '%contextAd%'
 AND cs(User-Agent) NOT LIKE '%bingbot%' 
 AND cs(User-Agent) NOT LIKE '%genieo%'
 AND cs(User-Agent) NOT LIKE '%proximic%'
 AND cs(User-Agent) NOT LIKE '%PageBot%'
 AND cs(User-Agent) NOT LIKE '%feedfetcher%'
 AND cs(User-Agent) NOT LIKE '%wordpress%'
 AND cs(User-Agent) NOT LIKE '%PictureBot%'
 AND cs(User-Agent) NOT LIKE '%WeSEE%'
 AND cs(User-Agent) NOT LIKE '%Sogou%'
 AND cs(User-Agent) NOT LIKE '%msnbot%'
 AND cs(User-Agent) NOT LIKE '%Mediapartner%'
 AND cs(User-Agent) NOT LIKE '%MagpieRSS%'
 AND cs(User-Agent) NOT LIKE '%Affectv%'
 AND cs(User-Agent) NOT LIKE '%Nutch%'
 AND cs(User-Agent) NOT LIKE '%SkimBot%'
 AND cs(User-Agent) NOT LIKE '%WhatWeb%'
 AND cs(User-Agent) NOT LIKE '%Googlebot%'
 AND cs(User-Agent) NOT LIKE '%Yahoo%'
 AND cs(User-Agent) NOT LIKE '%Netcraft%'
 AND cs(User-Agent) NOT LIKE '%AhrefsBot%'
 AND cs(User-Agent) NOT LIKE '%SemrushBot%'
 AND cs(User-Agent) NOT LIKE '%MJ12bot%'
 AND cs(User-Agent) NOT LIKE '%DotBot%'
 AND cs(User-Agent) NOT LIKE '%Vagabondo%'
 AND cs(User-Agent) NOT LIKE '%NetSeer%'
 AND cs(User-Agent) NOT LIKE '%PHP%'
GROUP by Platform/Device
ORDER by Hits DESC

Reference:

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related

Code Log Parser Studio SQL