Posted Jan 31, 09:47 AM by ben
One of my clients asked me to generate some data for their internal metrics/reporting on security. I have no love for either MSSQL or McAfee’s database schema so it took an hour or so to craft a query.One of The things to watch out for is sanitizing infection hits. A browser may try upwards to 40 times in the same minute to infect you with, say, a VML exploit to get a foothold to drop some other malware. The below gets around this by grouping within a 10 minute period for the virusname and the host. This snippet averages the number of detected infections on a daily basis for January (change avg to sum to get a total for january).
select avg(b.daytotal) as “Average Virus per Day” from (select count(a.dayhits) as daytotal, convert(char(11),a.clock,0) as completeday
from (
select 1 as dayhits,VirusName, HostName, convert(char(16),eventdatetime,0) as clock
from events
where ((VirusName is not NULL) or (VirusType is not NULL))
and (VirusName != ‘’)
and (TVDEventID < 1506 OR TVDEventID > 1506)
and (TVDEventID < 4600 OR TVDEventID > 4600)
and (TVDEventID < 10000)
and (TVDeventID != 1059)
and (EventDateTime BETWEEN ’2007-01-01’ AND ’2007-01-31 23:59’)
GROUP by virusname, HostName, convert(char(16),eventdatetime,0)
) as a
GROUP BY convert(char(11),a.clock,0)
) as b
Incidently, if you wanted to see the same type of metric for snort/mysql you could do:
select avg(a.sidcount) from (
select count(*) as sidcount from event
where timestamp > date_sub(now(), interval 30 day) and timestamp < now()
group by Date_format(timestamp, ‘%d %m %Y’)
) as a;
// :: metrics/
Commenting is closed for this article.
This work by http://electricfork.com is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.
I lead an information security ops and response team. This site is a collection of interesting notes and brainstorms on the protecting from, detecting of, and responding to badness. You can read more about me or my site here.
You can subscribe to my blog via rss 
, or if you're looking for older items check out my archive of previous posts.
I organize a small infosec meetup in baltimore called charmsec. If you are looking for charmsec details you probably want to go here.
RSS