Custom SQL Queries

BASE-user
SaracenMan
2007-05-18
2013-06-03
  • SaracenMan
    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.