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

Methodologies

Maybe I'm being a bit pedantic but there seems to be a recursive loop somewhere here. The information security lifecycle tends to be quoted as:

countermeasures/protect -> detect -> respond

And of course everyone then breaks down the respond methodology down somewhere akin to:

prepare -> detect -> contain -> eradicate -> recover -> follow-up

That "d" word is pretty popular huh? I find it interesting that there's not a lot of attention towards it. Especially in IR books. As a rule of thumb, they'll spend the first chapter on the preparing stage, a section on explaining why IDS/detection is out of scope, a bit on containment, then 80% of the remaining book on eradication. (Which, incidentally, when did 'detection' become synonymous to just an IDS? Don't you get calls when things 'act wierd'?)

While it makes logical sense to have detect as a stage in the entire IR process, that doesn't mean it doesn't deserve at least a chapter on the subject. And don't you dare make that subject about snort. Indeed, detection can be broken up into stages just like IR. Since I've yet to come across any in my own reading here is my own process:

discover -> prioritize -> investigate -> escalate -> follow-up

Once we get to 'escalate' the rest of the IR process takes over, notably containment. I guess everyone likes to talk about responding because there are results from it. Same goes to implementing an IDS system or setting up awareness to sysadmins and users. Has anyone taken the time to prioritizing/categorizing events? Finding valuable metrics? Reporting? Writing a detection book that's absolutely not about technology but about managing the detection implementation? How about a magazine article? Am I simply ignorant to some de facto 'standard' on this?

"Never write down passwords"

Does anybody know when this security tenet began? It reminds me of TSA gibberish such as taking off one's shoes to keep people secure. "Never write down passwords" made sense ten years ago when you had one or two passwords each 4 characters long.

Not as easy when there are more and more passwords accumulating in a persons head. There's nothing wrong with writing these passwords down, just when you do it insecurely. The entire slogan boils down to awareness. It seems every security program out there recommends not to jot down the dredded secret you keep in your mind. When did awareness programs stop being honest and practical? Were they ever?

Do I advocate passwords on sticky notes? Don't be silly. But I do think that it's not an all or nothing idea. These awareness programs need to explain how to securely keep passwords documented (keeping them in safes, keeping them on your person, etc) and start being realistic. This "never write down passwords" mantra just makes everyone look like a zealot and accomplishes very little. Stupid.