Snort mailing list archives

Previous By Date Next
Previous By Thread Next

Total Alert Count in snort_archive out of whack?

From: Joe Christy <joe () eshu net>
Date: Mon, 14 Oct 2002 12:50:29 -0700
Since upgrading to snort-1.9 from 1.8.7 10 days ago I've noticed the following strange behavior, even after upgrading to ACID 0.9.6b22 from v0.9.6b21 today. Sometimes when I archive alerts from the main snort db to the snort_archive db, the Total Number of Alerts in the acid web interface to snort_archive doesn't get incremented and there seems to be no way for search, etc. to retrieve those alerts which were added. Restarting my browser a/o web server has no effect (so much for the magical-thought solution). Even more peculiarly, if I then delete the archived alerts which are visible, the invisible alerts start to show up as added on the web front end and become retrievable. 

Digging around in the db itself I find:

mysql> SELECT COUNT(*) FROM event;
+----------+
| COUNT(*) |
+----------+
|       60 |
+----------+

which accurately reflects how many alerts I have archived, but:

mysql> SELECT COUNT(*) FROM acid_event;
+----------+
| COUNT(*) |
+----------+
|       30 |
+----------+

which doesn't reflect reality, as I perceive it.
While I am almost totally ignorant of PHP, it appears that ACID is generating its "Total Number of Alerts" from acid_event, though.
So, I have several questions: 1) What is the relation between and the event table and the acid_event table? 2) How is the relationship maintained? 2) How can I avoid the problem with newly archived alerts not showing up? 3) How can I force the invisibly archived alerts to appear without deleting the visibly archived alerts? 

ACID: v0.9.6b{21,22}
Browser(s): Mozilla-1.1,1.0.1
Web Server: apache-1.3.23-14 [RedHat 7.3 RPM]
PHP (invoked via CGI): 4.1.2-7.3.4 [RedHat 7.3 RPM]
MySQL: 3.23.49
schema: 106
prior actions: upgrade snort from 1.8.7 to 1.9.0, create a new snort_archive db, archive 30 alerts after handling them, attempt to archive 30 more.
The effect is quasi-replicable, i.e. the ratio visibly-newly-archived-alerts/newly-archived-alerts varies between 0/1 and 1/1. Attached are the snort and snort_archive ACID pages and a mysql trace of moving 10 archive (slected by signature) from snort to snort_archive. NB SELECT COUNT(*) FROM snort_archive.event; increased by 1 and 

   Joe

--
======== Joe Christy ============================== joe () eshu net =======
---- Voice:831/423-7151 --- Mobile:650/483-9123 --- FAX:831/469-0804 ---
__ If I can save you any time, give it to me, I'll keep it with mine. __
www.eshu.net/CA.html BF:38:C1:17:5F:F4:00:19:53:01:7B:4C:88:72:93:85 

--------------------------------------------------------------------------------
PConnect [mysql] snort_archive@localhost: as snort
[Oct 14 2002 12:34:17] /acid-archive/acid_main.php - db version 106
--------------------------------------------------------------------------------

SELECT ip_src FROM iphdr
SELECT sid FROM sensor
SELECT MAX(cid) FROM event WHERE sid='1'
SELECT MAX(cid) FROM acid_event WHERE sid='1'
SELECT count(*) FROM acid_event where sid = '1'
INSERT INTO acid_event (sid,cid,signature,timestamp,
                             ip_src,ip_dst,ip_proto,
                             layer4_sport,layer4_dport,
                             sig_name, sig_priority, sig_class_id)
     SELECT event.sid as sid, event.cid as cid, signature, timestamp, 
            ip_src, ip_dst, ip_proto,
            tcp_sport as layer4_sport, tcp_dport as layer4_dport, sig_name, sig_priority, sig_class_id
    FROM event
     INNER JOIN signature ON (signature = signature.sig_id) 
    INNER JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid) 
    LEFT JOIN tcphdr ON (event.sid=tcphdr.sid AND event.cid=tcphdr.cid)
    WHERE (event.sid = 1 AND event.cid > 217) AND ip_proto = 6
INSERT INTO acid_event (sid,cid,signature,timestamp,
                             ip_src,ip_dst,ip_proto,
                             layer4_sport,layer4_dport,
                             sig_name, sig_priority, sig_class_id)
     SELECT event.sid as sid, event.cid as cid, signature, timestamp,
            ip_src, ip_dst, ip_proto,
            udp_sport as layer4_sport, udp_dport as layer4_dport, sig_name, sig_priority, sig_class_id
     FROM event
      INNER JOIN signature ON (signature = signature.sig_id)
     INNER JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid)
     LEFT JOIN udphdr ON (event.sid=udphdr.sid AND event.cid=udphdr.cid)
     WHERE (event.sid = 1 AND event.cid > 217) AND ip_proto = 17
INSERT INTO acid_event (sid,cid,signature,timestamp,
                              ip_src,ip_dst,ip_proto,
                              sig_name, sig_priority, sig_class_id)
      SELECT event.sid as sid, event.cid as cid, signature, timestamp,
             ip_src, ip_dst, ip_proto, sig_name, sig_priority, sig_class_id
      FROM event
       INNER JOIN signature ON (signature = signature.sig_id)
      INNER JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid)
      LEFT JOIN icmphdr ON (event.sid=icmphdr.sid AND event.cid=icmphdr.cid)
      WHERE (event.sid = 1 AND event.cid > 217) and ip_proto = 1
INSERT INTO acid_event (sid,cid,signature,timestamp,
                              ip_src,ip_dst,ip_proto,
                              sig_name, sig_priority, sig_class_id)
      SELECT event.sid as sid, event.cid as cid, signature, timestamp,
             ip_src, ip_dst, ip_proto, sig_name, sig_priority, sig_class_id
      FROM event
       INNER JOIN signature ON (signature = signature.sig_id)
      LEFT JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid)
      WHERE (NOT (ip_proto IN (1, 6, 17))) AND  ( NOT  (sig_name LIKE 'spp_%') ) AND
            (event.sid = 1 AND event.cid > 217)
INSERT INTO acid_event (sid,cid,signature,timestamp,
                              ip_src,ip_dst,ip_proto,
                              sig_name, sig_priority, sig_class_id)
      SELECT event.sid as sid, event.cid as cid, signature, timestamp,
             ip_src, ip_dst, ip_proto, sig_name, sig_priority, sig_class_id
      FROM event
       INNER JOIN signature ON (signature = signature.sig_id)
      LEFT JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid)
      WHERE  (sig_name LIKE 'spp_%')  AND 
      (event.sid = 1 AND event.cid > 217)
SELECT count(*) FROM acid_event where sid = '1'
SELECT min(timestamp), max(timestamp) FROM acid_event
SELECT count(*) FROM sensor
SELECT COUNT(DISTINCT acid_event.signature) FROM acid_event  
SELECT count(*) FROM acid_event
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event  
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event
SELECT COUNT(DISTINCT layer4_sport),  COUNT(DISTINCT layer4_dport) FROM acid_event
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='6'
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='17'
SELECT count(DISTINCT(sig_class_id)) FROM acid_event
SELECT count(*) FROM tcphdr
SELECT count(*) FROM udphdr
SELECT count(*) FROM icmphdr
SELECT count(event.sid) FROM event LEFT JOIN signature ON event.signature=signature.sig_id WHERE sig_name LIKE 
'spp_portscan%'

--------------------------------------------------------------------------------
PConnect [mysql] snort_archive@localhost: as snort
[Oct 14 2002 12:37:18] /acid-archive/acid_main.php - db version 106
--------------------------------------------------------------------------------

SELECT ip_src FROM iphdr
SELECT sid FROM sensor
SELECT MAX(cid) FROM event WHERE sid='1'
SELECT MAX(cid) FROM acid_event WHERE sid='1'
SELECT min(timestamp), max(timestamp) FROM acid_event
SELECT count(*) FROM sensor
SELECT COUNT(DISTINCT acid_event.signature) FROM acid_event  
SELECT count(*) FROM acid_event
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event  
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event
SELECT COUNT(DISTINCT layer4_sport),  COUNT(DISTINCT layer4_dport) FROM acid_event
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='6'
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='17'
SELECT count(DISTINCT(sig_class_id)) FROM acid_event
SELECT count(*) FROM tcphdr
SELECT count(*) FROM udphdr
SELECT count(*) FROM icmphdr
SELECT count(event.sid) FROM event LEFT JOIN signature ON event.signature=signature.sig_id WHERE sig_name LIKE 
'spp_portscan%'

--------------------------------------------------------------------------------
PConnect [mysql] snort_archive@localhost: as snort
[Oct 14 2002 12:40:19] /acid-archive/acid_main.php - db version 106
--------------------------------------------------------------------------------

SELECT ip_src FROM iphdr
SELECT sid FROM sensor
SELECT MAX(cid) FROM event WHERE sid='1'
SELECT MAX(cid) FROM acid_event WHERE sid='1'
SELECT min(timestamp), max(timestamp) FROM acid_event
SELECT count(*) FROM sensor
SELECT COUNT(DISTINCT acid_event.signature) FROM acid_event  
SELECT count(*) FROM acid_event
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event  
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event
SELECT COUNT(DISTINCT layer4_sport),  COUNT(DISTINCT layer4_dport) FROM acid_event
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='6'
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='17'
SELECT count(DISTINCT(sig_class_id)) FROM acid_event
SELECT count(*) FROM tcphdr
SELECT count(*) FROM udphdr
SELECT count(*) FROM icmphdr
SELECT count(event.sid) FROM event LEFT JOIN signature ON event.signature=signature.sig_id WHERE sig_name LIKE 
'spp_portscan%'

--------------------------------------------------------------------------------
PConnect [mysql] snort_archive@localhost: as snort
[Oct 14 2002 12:43:20] /acid-archive/acid_main.php - db version 106
--------------------------------------------------------------------------------

SELECT ip_src FROM iphdr
SELECT sid FROM sensor
SELECT MAX(cid) FROM event WHERE sid='1'
SELECT MAX(cid) FROM acid_event WHERE sid='1'
SELECT min(timestamp), max(timestamp) FROM acid_event
SELECT count(*) FROM sensor
SELECT COUNT(DISTINCT acid_event.signature) FROM acid_event  
SELECT count(*) FROM acid_event
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event  
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event
SELECT COUNT(DISTINCT layer4_sport),  COUNT(DISTINCT layer4_dport) FROM acid_event
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='6'
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='17'
SELECT count(DISTINCT(sig_class_id)) FROM acid_event
SELECT count(*) FROM tcphdr
SELECT count(*) FROM udphdr
SELECT count(*) FROM icmphdr
SELECT count(event.sid) FROM event LEFT JOIN signature ON event.signature=signature.sig_id WHERE sig_name LIKE 
'spp_portscan%'

--------------------------------------------------------------------------------
PConnect [mysql] snort_archive@localhost: as snort
[Oct 14 2002 12:46:21] /acid-archive/acid_main.php - db version 106
--------------------------------------------------------------------------------

SELECT ip_src FROM iphdr
SELECT sid FROM sensor
SELECT MAX(cid) FROM event WHERE sid='1'
SELECT MAX(cid) FROM acid_event WHERE sid='1'
SELECT min(timestamp), max(timestamp) FROM acid_event
SELECT count(*) FROM sensor
SELECT COUNT(DISTINCT acid_event.signature) FROM acid_event  
SELECT count(*) FROM acid_event
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event  
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event
SELECT COUNT(DISTINCT layer4_sport),  COUNT(DISTINCT layer4_dport) FROM acid_event
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='6'
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='17'
SELECT count(DISTINCT(sig_class_id)) FROM acid_event
SELECT count(*) FROM tcphdr
SELECT count(*) FROM udphdr
SELECT count(*) FROM icmphdr
SELECT count(event.sid) FROM event LEFT JOIN signature ON event.signature=signature.sig_id WHERE sig_name LIKE 
'spp_portscan%'

--------------------------------------------------------------------------------
PConnect [mysql] snort_archive@localhost: as snort
[Oct 14 2002 12:49:22] /acid-archive/acid_main.php - db version 106
--------------------------------------------------------------------------------

SELECT ip_src FROM iphdr
SELECT sid FROM sensor
SELECT MAX(cid) FROM event WHERE sid='1'
SELECT MAX(cid) FROM acid_event WHERE sid='1'
SELECT min(timestamp), max(timestamp) FROM acid_event
SELECT count(*) FROM sensor
SELECT COUNT(DISTINCT acid_event.signature) FROM acid_event  
SELECT count(*) FROM acid_event
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event  
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event
SELECT COUNT(DISTINCT layer4_sport),  COUNT(DISTINCT layer4_dport) FROM acid_event
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='6'
SELECT COUNT(DISTINCT acid_event.layer4_sport),  COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE 
ip_proto='17'
SELECT count(DISTINCT(sig_class_id)) FROM acid_event
SELECT count(*) FROM tcphdr
SELECT count(*) FROM udphdr
SELECT count(*) FROM icmphdr
SELECT count(event.sid) FROM event LEFT JOIN signature ON event.signature=signature.sig_id WHERE sig_name LIKE 
'spp_portscan%'
Previous By Date Next
Previous By Thread Next

Current thread: