Wednesday, January 31, 2007

Trending Metrics for McAfee

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

No comments:

Post a Comment