Date
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;