SaracenMan - 2007-05-18

I have recently started a new job and was asked if I knew any SQL. I said a little which made me the "expert"!

What we want is to create queries to run against the BASE database that answer the questions:

1) All alerts of priority 3 from the last 24 hours based on unique src/dest IPs
2) A count of the above items in query above
3) Top 5 src ports from the last 24 hours
4) Top 5 dest ports from the last 24 hours
5) Top 5 NetBIOS ports from the last 24 hours, 135, 137, 139, 136, 138, 445

BASE does not have these exact queries by default, and we would like to get as close as we can to these.

I have tried various queries, so far I have:

SELECT
    sig_class.sig_class_name,
    tcphdr.cid,
    tcphdr.tcp_sport,
    COUNT(tcphdr.tcp_sport) AS freq
FROM
    tcphdr
LEFT JOIN
    sig_class
ON
    tcphdr.sid=sig_class.sig_class_id
GROUP BY
    tcp_sport
ORDER BY freq DESC
LIMIT 5;

Which is a start to answering query number 3, but as soon as I try to join this with the event table to get the time stamp (to filter on last 24hrs) the query runs for ages!

I tried:

SELECT
    event.timestamp,
    sig_class.sig_class_name,
    tcphdr.cid,
    tcphdr.tcp_sport,
    count(tcphdr.tcp_sport) AS freq
FROM event,tcphdr
LEFT JOIN sig_class
ON
    sid=sig_class_id
GROUP BY tcp_sport
ORDER BY freq DESC
LIMIT 5

But no dice!

Any help would be appreciated... Thanks.

Barry Dean
The University of Liverpool, UK.