Querying SNORT SQL database

When SNORT stores its data into SQL database then there is obvious question how to get data you would otherwise had in plain log files generated by SNORT. So, here is what I managed to deduce so far (note that the post will be extended as I learn more). In case you have comment/addition/correction please post a comment on this post. That is especially valid for SQL queries as I'm not an expert in that area and some of them might be suboptimal.

Few introductory words

To try the following examples you need working instance of MySQL database and SNORT that logs into database (directly or via barnyard2). If you have that, then run mysql command line client (or some equivalent) and select SNORT database. You are now ready to go...

This post is written using schema version 107. To find out which version of schema you have, run the following query:

mysql> select * from `schema`;
+------+---------------------+
| vseq | ctime               |
+------+---------------------+
|  107 | 2012-07-10 10:20:52 |
+------+---------------------+
1 row in set (0.00 sec)

Note the backticks! Namely, schema is MySQL's reserved word and if you don't use backticks, MySQL will report syntax error! Alternatively, you can use syntax database.tablename to avoid table name being treated as a reserved word.

Finally, because of screen size constraints, I'm limiting the output more often than not, here is what you'll see in that regard:

1. In SELECT statement, I'm using LIMIT N keyword to get only first N rows.
2. I'll explicitly enumerate fields to be returned in SELECT statement instead of using star (i.e. SELECT column1,column2 instead of SELECT *).
3. I'll also use LEFT() function to limit number of characters retrieved from VARCHAR and similarly typed columns.

Examples of queries

The first thing you probably want to find out is how many alerts there were on a certain day, e.g. on a July 10th, 2012. This is easy, just run the following query:

mysql> select count(*) from event where timestamp between '2012-07-10' and '2012-07-11';
+----------+
| count(*) |
+----------+
|    12313 |
+----------+
1 row in set (0.01 sec)

Two things you should note about this query:

1. All the generated events are stored in the table event. There is a column timestamp which stores timestamp when an event was generated.
2. To select date range I'm using between/and keywords. I'm also shortening typing by providing only a date while time is assumed to be 00:00:00 so this query basically catches anything on July 10th, 2012, as requested.

I could equally well use the following query:

select count(*) from event where date(timestamp)='2012-07-10';

to get the same result, but in case I want a range instead of a single day, syntax using BETWEEN keyword is better.

To get number of events generated on a current day, use the following query:

mysql> select count(*) from event where date(timestamp)=date(now());
+----------+
| count(*) |
+----------+
|      178 |
+----------+
1 row in set (0.13 sec)

Note that we are using function NOW() to get current time and then we just extract date using DATE() function.

While we are at the table events, here is its structure:

mysql> show columns from event;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| sid       | int(10) unsigned | NO   | PRI | NULL    |       |
| cid       | int(10) unsigned | NO   | PRI | NULL    |       |
| signature | int(10) unsigned | NO   | MUL | NULL    |       |
| timestamp | datetime         | NO   | MUL | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Only the timestamp column contains data in this table, other columns are links to other tables as follows:

1. sid and cid are links to packet data, i.e. IP/TCP/UDP headers and associated data. Those are placed within separate tables which we'll talk about later.
2. signature is link (foreign key) to signature table column sig_id

Ok, what about finding out number of events per day? Well, easy again, the following select statement will do that:

mysql> select count(*),date(timestamp) as count from event group by date(timestamp);
+----------+------------+
| count(*) | count      |
+----------+------------+
|    11689 | 2012-06-28 |
|    17904 | 2012-06-29 |
|     4353 | 2012-06-30 |
|     4322 | 2012-07-01 |
|    14198 | 2012-07-02 |
|     2977 | 2012-07-03 |
|    12313 | 2012-07-10 |
|    13014 | 2012-07-11 |
|     9126 | 2012-07-12 |
|     2642 | 2012-07-17 |
|     1527 | 2012-07-19 |
+----------+------------+
11 rows in set (0.07 sec)

I could use ORDER BY statement to get a day with largest number of alerts, otherwise they are sorted according to a day. In this case I used function DATE() to chop time part of the timestamp. Otherwise, I would get alerts broken down by minutes.

Ok, let's move on. What about finding out all types of events that occurred, or in other words, all signatures. Well, signatures that SNORT generates are stored in the table signature and simple query on this table will give us the answer what signatures were generated so far:

mysql> select sig_id,sig_name from signature;
+--------+-----------------------------------------------------------------------+
| sig_id | sig_name                                                              |
+--------+-----------------------------------------------------------------------+
|      1 | SCAN UPnP service discover attempt                                    |
|      2 | stream5: TCP Small Segment Threshold Exceeded                         |
|      3 | http_inspect: NO CONTENT-LENGTH OR TRANSFER-ENCODING IN HTTP RESPONSE |
|      4 | http_inspect: MESSAGE WITH INVALID CONTENT-LENGTH OR CHUNK SIZE       |
|      5 | stream5: Reset outside window                                         |
|      6 | ssh: Protocol mismatch                                                |
+--------+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

All in all, our SNORT instance generated six different signatures so far. The table signature has the following structure:

mysql> show columns from signature;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| sig_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sig_name     | varchar(255)     | NO   | MUL | NULL    |                |
| sig_class_id | int(10) unsigned | NO   | MUL | NULL    |                |
| sig_priority | int(10) unsigned | YES  |     | NULL    |                |
| sig_rev      | int(10) unsigned | YES  |     | NULL    |                |
| sig_sid      | int(10) unsigned | YES  |     | NULL    |                |
| sig_gid      | int(10) unsigned | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

The columns are:

1. sig_id is primary key of this table.
2. sig_name is textual representation of signature.
3. sig_class_id
4. sig_priority
5. sig_rev
6. sig_sid
7. sig_gid

Ok, the next thing you might want to know is how many time each alert was generated. So, to achieve this use the following SQL query:

mysql> select sig_id,left(sig_name,30),count(*) from signature as s, event as e where s.sig_id=e.signature group by sig_name;
+--------+--------------------------------+----------+
| sig_id | left(sig_name,30)              | count(*) |
+--------+--------------------------------+----------+
|      4 | http_inspect: MESSAGE WITH INV |      109 |
|      3 | http_inspect: NO CONTENT-LENGT |      198 |
|      1 | SCAN UPnP service discover att |    55440 |
|      6 | ssh: Protocol mismatch         |     2360 |
|      5 | stream5: Reset outside window  |    33698 |
|      2 | stream5: TCP Small Segment Thr |      971 |
+--------+--------------------------------+----------+
6 rows in set (0.23 sec)

We had to do a join across two tables, signature and event. As you can see I got specific signatures with their count. Furthermore, I could order them so that I have most frequent ones on top (or bottom). Also, you should note that I'm using LEFT() function to make the output shorter in order to fit this post.

Ok, what about finding number of signatures generated on a specific day, say, today? Well, this is the same as the previous query but we only have to add one more condition, namely that the rows from the table event are taken into account only if timestamp is from today:

mysql> select sig_id,left(sig_name,30),count(*) from signature as s, event as e where s.sig_id=e.signature and date(e.timestamp)=date(now()) group by sig_name;
+--------+--------------------------------+----------+
| sig_id | left(sig_name,30)              | count(*) |
+--------+--------------------------------+----------+
|      6 | ssh: Protocol mismatch         |      226 |
|      5 | stream5: Reset outside window  |        2 |
|      2 | stream5: TCP Small Segment Thr |       40 |
+--------+--------------------------------+----------+
3 rows in set (0.14 sec)

Easy, the only difference from the previous query is shown in italic font. Now, let us move on. Suppose we want to know hosts that generated packets that triggered alerts. In order to do that we have to include table iphdr in the query. Table iphdr contains data from the IP header of captured packet. So, run the following SELECT statement:

mysql> select signature,count(*) as cnt,inet_ntoa(ip_src) from event,iphdr where event.cid=iphdr.cid and event.sid=iphdr.sid group by ip_src order by cnt;
+-----------+-------+-------------------+
| signature | cnt   | inet_ntoa(ip_src) |
+-----------+-------+-------------------+
|         3 |     1 | 192.168.1.44      |
|         5 |     1 | 192.168.1.89      |
|         5 |     1 | 192.168.1.27      |
|         5 |     1 | 192.168.1.5       |
|         5 |     1 | 192.168.1.120     |
|         5 |     1 | 192.168.0.21      |
+-----------+-------+-------------------+
6 rows in set (0.0 sec)

Ok, I have source IP addresses that triggered total of CNT number of alerts. Note that IP addresses are kept in a decimal form, so they have to be converted into dot form using inet_ntoa() MySQL function.

Here is the structure of iphdr table:

mysql> show columns from iphdr;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| sid      | int(10) unsigned     | NO   | PRI | NULL    |       |
| cid      | int(10) unsigned     | NO   | PRI | NULL    |       |
| ip_src   | int(10) unsigned     | NO   | MUL | NULL    |       |
| ip_dst   | int(10) unsigned     | NO   | MUL | NULL    |       |
| ip_ver   | tinyint(3) unsigned  | YES  |     | NULL    |       |
| ip_hlen  | tinyint(3) unsigned  | YES  |     | NULL    |       |
| ip_tos   | tinyint(3) unsigned  | YES  |     | NULL    |       |
| ip_len   | smallint(5) unsigned | YES  |     | NULL    |       |
| ip_id    | smallint(5) unsigned | YES  |     | NULL    |       |
| ip_flags | tinyint(3) unsigned  | YES  |     | NULL    |       |
| ip_off   | smallint(5) unsigned | YES  |     | NULL    |       |
| ip_ttl   | tinyint(3) unsigned  | YES  |     | NULL    |       |
| ip_proto | tinyint(3) unsigned  | NO   |     | NULL    |       |
| ip_csum  | smallint(5) unsigned | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

sid and cid columns are connection to event table, and to tcphdr and udphdr tables. The rest of the columns contain data from IP header. For example, ip_ver contains IP version. So, you can try to see how many protocol versions that triggered alerts there was:

mysql> select ip_ver,count(*) from iphdr group by ip_ver;
+--------+----------+
| ip_ver | count(*) |
+--------+----------+
|      4 |    92445 |
+--------+----------+
1 row in set (0.04 sec)

In my case, it was only IPv4. We can also do the same with the other fields, like which transport layer protocols were observed:

mysql> select ip_proto,count(*) from iphdr group by ip_proto;
+----------+----------+
| ip_proto | count(*) |
+----------+----------+
|        6 |    43076 |
|       17 |    49785 |
+----------+----------+
2 rows in set (0.04 sec)

Obviously, only two, UDP (id 17) and TCP (id 6). BTW, those numbers you can look up in /etc/protocols file on any Linux machine, or you can go to IANA.

To see all source IP addresses that triggered alerts we can use the following query:

mysql> select inet_ntoa(ip_src),count(*) from iphdr group by ip_src limit 5;
+-------------------+----------+
| inet_ntoa(ip_src) | count(*) |
+-------------------+----------+
| 10.61.34.152      |       20 |
| 85.214.67.247     |        2 |
| 134.108.44.54     |        2 |
| 192.168.5.71      |       10 |
| 192.168.102.150   |     2130 |
+-------------------+----------+
5 rows in set (0.00 sec)

Now, it can turn out that there are some IP addresses that we actually didn't expect and we want to know, when and what happened. Take for example the address 10.61.34.152 from the above output, let's see what this address generated:

mysql> select inet_ntoa(ip_src),inet_ntoa(ip_dst),count(*) from iphdr where inet_ntoa(iphdr.ip_src)='10.61.34.152' group by ip_dst;
+-------------------+-------------------+----------+
| inet_ntoa(ip_src) | inet_ntoa(ip_dst) | count(*) |
+-------------------+-------------------+----------+
| 10.61.34.152      | 239.255.255.250   |       20 |
+-------------------+-------------------+----------+
1 row in set (0.03 sec)

Using this query we see that all the packets were destined to address 239.255.255.250. A bit of grouping according to date:

mysql> select date(timestamp),count(*) from event,iphdr where (event.cid,event.sid)=(iphdr.cid,iphdr.sid) and inet_ntoa(ip_src)='10.61.34.152' group by date(timestamp);
+-----------------+----------+
| date(timestamp) | count(*) |
+-----------------+----------+
| 2012-07-02      |       20 |
+-----------------+----------+
1 row in set (0.03 sec)

we see that all events were generated on the same day. And what was the alert:

mysql> select signature.sig_name,count(*) from signature,event,iphdr where (event.cid,event.sid)=(iphdr.cid,iphdr.sid) and inet_ntoa(ip_src)='10.61.34.152' and event.signature=signature.sig_id group by sig_id;
+------------------------------------+----------+
| sig_name                           | count(*) |
+------------------------------------+----------+
| SCAN UPnP service discover attempt |       20 |
+------------------------------------+----------+
1 row in set (0.84 sec)

Well, all were UPnP service discovery requests.

One interesting thing, at least for me, is who sent ICMP Echo Request messages on the network. This is easy to determine using the following query:

mysql> select inet_ntoa(iphdr.ip_src) as SRC,inet_ntoa(iphdr.ip_dst) as DST,timestamp from event,iphdr,icmphdr where (icmphdr.sid,icmphdr.cid)=(event.sid,event.cid) and (iphdr.sid,iphdr.cid)=(event.sid,event.cid) and icmp_type=8 limit 3;
+-------------+--------------+---------------------+
| SRC         | DST          | timestamp           |
+-------------+--------------+---------------------+
| 192.168.1.8 | 192.168.1.55 | 2012-07-20 11:05:01 |
| 192.168.1.8 | 192.168.1.55 | 2012-07-20 11:05:01 |
| 192.168.1.8 | 192.168.1.55 | 2012-07-20 11:05:02 |
+-------------+--------------+---------------------+
3 rows in set (0.00 sec)

Obviousy, host with address 192.168.1.8 sent probes to host 192.168.1.55.

So much for now. Detailed info about DB schema used by SNORT can be found on this link.

In the end, my impression is that it is definitely much more easier and efficient to gather statistics using SQL database than plain files but that it is the best to use some tool that has all those queries predefined and to fall back to SQL only when you have some very specific requirement.