ACID: Database (v0) ER Diagram


Snort (and other devices) log to database with the following schema:

Snort 1.7 (schema v0) and ACID Database ER diagram

Table Component Description
sensor Snort Sensor name
event Snort Meta-data about the detected alert
data Snort Contents of packet payload
iphdr Snort IP protocol fields
tcphdr Snort TCP protocol fields
udphdr Snort UDP protocol fields
icmphdr Snort ICMP protocol fields
opt Snort IP and TCP options
detail Snort (lookup table) Level of detail with which a sensor is logging
protocols SnortDB extra (lookup table) Layer-4 (IP encoded) protocol list
services SnortDB extra (lookup table) TCP and UDP service list
flags SnortDB extra (lookup table) TCP flag list
acid_ag ACID Meta-data for alert groups
acid_ag_alert ACID Alerts in each alert group


sensor

+-----------+------------------+------+-----+---------+----------------------------------------------+
| Field     | Type             | Null | Key | Default | Description                                  |
+-----------+------------------+------+-----+---------+----------------------------------------------+
| sid       | int(10) unsigned |      | PRI | NULL    | Sensor ID                                    |
| hostname  | text             | YES  |     | NULL    | Hostname of the sensor (IP if can't qualify) |
| interface | text             | YES  |     | NULL    | Network interface (e.g. eth0)                |
| filter    | text             | YES  |     | NULL    | BPF filter                                   |
| detail    | tinyint(4)       | YES  |     | NULL    | Detail level of the logging                  |
| encoding  | tinyint(4)       | YES  |     | NULL    | Encoding format of the payload               |
+-----------+------------------+------+-----+---------+----------------------------------------------+


event

+-----------+------------------+------+-----+---------------------+----------------------------------------+
| Field     | Type             | Null | Key | Default             | Description                            |
+-----------+------------------+------+-----+---------------------+----------------------------------------+
| sid       | int(10) unsigned |      | PRI | 0                   | Sensor ID                              |
| cid       | int(10) unsigned |      | PRI | 0                   | Event ID                               |
| signature | int(10) unsigned |      | MUL | 0                   | Signature name                         |
| timestamp | datetime         |      | MUL | 0000-00-00 00:00:00 | Timestamp of when the event was logged |
+-----------+------------------+------+-----+---------------------+----------------------------------------+


data

+--------------+------------------+------+-----+---------+-----------------------------------------------------+
| Field        | Type             | Null | Key | Default | Description                                         |
+--------------+------------------+------+-----+---------+-----------------------------------------------------+
| sid          | int(10) unsigned |      | PRI | 0       | Sensor ID                                           |
| cid          | int(10) unsigned |      | PRI | 0       | Event ID                                            |
| data_payload | text             | YES  |     | NULL    | Packet payload encoded according to sensor.encoding |
+--------------+------------------+------+-----+---------+-----------------------------------------------------+


iphdr

+----------+----------------------+------+-----+---------+----------------------------------------------+
| Field    | Type                 | Null | Key | Default | Description                                  |
+----------+----------------------+------+-----+---------+----------------------------------------------+
| sid      | int(10) unsigned     |      | PRI | 0       | Sensor ID                                    |
| cid      | int(10) unsigned     |      | PRI | 0       | Event ID                                     |
| ip_src   | int(10) unsigned     |      | MUL | 0       | Source IP address (32-bit unsigned int)      |
| ip_dst   | int(10) unsigned     |      | MUL | 0       | Destination IP address (32-bit unsigned int) |
| ip_src0  | tinyint(3) unsigned  | YES  |     | NULL    | Source IP octet 3 (e.g. 127.x.x.x)           |
| ip_src1  | tinyint(3) unsigned  | YES  |     | NULL    | Source IP octet 2 (e.g. x.0.x.x)             |
| ip_src2  | tinyint(3) unsigned  | YES  |     | NULL    | Source IP octet 1 (e.g. x.x.0.x)             |
| ip_src3  | tinyint(3) unsigned  | YES  |     | NULL    | Source IP octet 0 (e.g. x.x.x.1)             |
| ip_dst0  | tinyint(3) unsigned  | YES  |     | NULL    | Dest. IP octet 3 (e.g. 127.x.x.x)            |
| ip_dst1  | tinyint(3) unsigned  | YES  |     | NULL    | Dest. IP octet 2 (e.g. x.0.x.x)              |
| ip_dst2  | tinyint(3) unsigned  | YES  |     | NULL    | Dest. IP octet 1 (e.g. x.x.0.x)              |
| ip_dst3  | tinyint(3) unsigned  | YES  |     | NULL    | Dest. IP octet 0 (e.g. x.x.x.1)              |
| ip_ver   | tinyint(3) unsigned  | YES  |     | NULL    | IP version                                   |
| ip_hlen  | tinyint(3) unsigned  | YES  |     | NULL    | IP Header length                             |
| ip_tos   | tinyint(3) unsigned  | YES  |     | NULL    | IP type-of-service                           |
| ip_len   | smallint(5) unsigned | YES  |     | NULL    | IP datagram length                           |
| ip_id    | smallint(5) unsigned | YES  |     | NULL    | IP ID                                        |
| ip_flags | tinyint(3) unsigned  | YES  |     | NULL    | IP flags                                     |
| ip_off   | smallint(5) unsigned | YES  |     | NULL    | IP fragment offset                           |
| ip_ttl   | tinyint(3) unsigned  | YES  |     | NULL    | IP time-to-live                              |
| ip_proto | tinyint(3) unsigned  |      |     | 0       | IP protocol                                  |
| ip_csum  | smallint(5) unsigned | YES  |     | NULL    | IP checksum                                  |
+----------+----------------------+------+-----+---------+----------------------------------------------+


tcphdr


+-----------+----------------------+------+-----+---------+----------------------+
| Field     | Type                 | Null | Key | Default | Description          |
+-----------+----------------------+------+-----+---------+----------------------+
| sid       | int(10) unsigned     |      | PRI | 0       | Sensor ID            |
| cid       | int(10) unsigned     |      | PRI | 0       | Event ID             |
| tcp_sport | smallint(5) unsigned |      | MUL | 0       | TCP source port      |
| tcp_dport | smallint(5) unsigned |      | MUL | 0       | TCP destination port |
| tcp_seq   | int(10) unsigned     | YES  |     | NULL    | TCP sequence number  |
| tcp_ack   | int(10) unsigned     | YES  |     | NULL    | TCP ACK number       |
| tcp_off   | tinyint(3) unsigned  | YES  |     | NULL    | TCP offset           |
| tcp_res   | tinyint(3) unsigned  | YES  |     | NULL    | TCP reserved         |
| tcp_flags | tinyint(3) unsigned  |      | MUL | 0       | TCP flags            |
| tcp_win   | smallint(5) unsigned | YES  |     | NULL    | TCP window           |
| tcp_csum  | smallint(5) unsigned | YES  |     | NULL    | TCP checksum         |
| tcp_urp   | smallint(5) unsigned | YES  |     | NULL    | TCP urgent pointer   |
+-----------+----------------------+------+-----+---------+----------------------+


udphdr


+-----------+----------------------+------+-----+---------+----------------------+
| Field     | Type                 | Null | Key | Default | Description          |
+-----------+----------------------+------+-----+---------+----------------------+
| sid       | int(10) unsigned     |      | PRI | 0       | Sensor ID            |
| cid       | int(10) unsigned     |      | PRI | 0       | Event ID             |
| udp_sport | smallint(5) unsigned |      | MUL | 0       | UDP soure port       |
| udp_dport | smallint(5) unsigned |      | MUL | 0       | UDP destination port |
| udp_len   | smallint(5) unsigned | YES  |     | NULL    | UDP length           |
| udp_csum  | smallint(5) unsigned | YES  |     | NULL    | UDP checksum         |
+-----------+----------------------+------+-----+---------+----------------------+


icmphdr

+-----------+----------------------+------+-----+---------+----------------------+
| Field     | Type                 | Null | Key | Default | Description          |
+-----------+----------------------+------+-----+---------+----------------------+
| sid       | int(10) unsigned     |      | PRI | 0       | Sensor ID            |
| cid       | int(10) unsigned     |      | PRI | 0       | Event ID             |
| icmp_type | tinyint(3) unsigned  |      | MUL | 0       | ICMP type            |
| icmp_code | tinyint(3) unsigned  |      |     | 0       | ICMP code            |
| icmp_csum | smallint(5) unsigned | YES  |     | NULL    | ICMP checksum        |
| icmp_id   | smallint(5) unsigned | YES  |     | NULL    | ICMP ID              |
| icmp_seq  | smallint(5) unsigned | YES  |     | NULL    | ICMP sequence number |
+-----------+----------------------+------+-----+---------+----------------------+


opt

+-----------+---------------------+------+-----+---------+----------------------------------------+
| Field     | Type                | Null | Key | Default | Description                            |
+-----------+---------------------+------+-----+---------+----------------------------------------+
| sid       | int(10) unsigned    |      | PRI | 0       | Sensor ID                              |
| cid       | int(10) unsigned    |      | PRI | 0       | Event ID                               |
| optid     | int(10) unsigned    |      | PRI | 0       | Option ID (multiple options per alert) |
| opt_proto | tinyint(3) unsigned |      |     | 0       | Option protocol (IP, TCP)              |
| opt_code  | tinyint(3) unsigned |      |     | 0       | Option code                            |
| opt_len   | smallint(6)         | YES  |     | NULL    | Option length                          |
| opt_data  | text                | YES  |     | NULL    | Option data                            |
+-----------+---------------------+------+-----+---------+----------------------------------------+


acid_ag


+----------+------------------+------+-----+---------+-----------------------------------+
| Field    | Type             | Null | Key | Default | Description                       |
+----------+------------------+------+-----+---------+-----------------------------------+
| ag_id    | int(10) unsigned |      | PRI | NULL    | Alert Group (AG) ID               |
| ag_name  | varchar(40)      | YES  |     | NULL    | AG name                           |
| ag_desc  | text             | YES  |     | NULL    | AG description                    |
| ag_ctime | datetime         | YES  |     | NULL    | Timestamp of AG creation time     |
| ag_ltime | datetime         | YES  |     | NULL    | Timestamp of last AG modification |
+----------+------------------+------+-----+---------+-----------------------------------+


acid_ag_alert


+--------+------------------+------+-----+---------+---------------------+
| Field  | Type             | Null | Key | Default | Description         |
+--------+------------------+------+-----+---------+---------------------+
| ag_id  | int(10) unsigned |      | PRI | 0       | Alert Group (AG) ID |
| ag_sid | int(10) unsigned |      | PRI | 0       | Sensor ID           |
| ag_cid | int(10) unsigned |      | PRI | 0       | Event ID            |
+--------+------------------+------+-----+---------+---------------------+