README
1# Simple readme with some query examples.
2# Examples for MySQL and PostgreSQL
3
4
51- View all rules:
6
7> SELECT rule_id, level, description FROM signature;
8
9
102- View all categories (groups)
11
12> SELECT * FROM category;
13
14
153- View all categories of a specific rule (1002 for example):
16
17> SELECT rule_id, cat_name from category, signature_category_mapping WHERE rule_id = 1002 AND signature_category_mapping.cat_id = category.cat_id;
18
19
204- View all alerts (without data):
21
22> SELECT * FROM alert;
23
24
255- View all alerts (with IP as string):
26
27> SELECT rule_id, timestamp, INET_NTOA(src_ip) srcip from alert;
28
29
306- View all alerts, including locations (IP as string and time as string):
31
32MySQL:
33>SELECT FROM_UNIXTIME(timestamp) time, rule_id,location.name location, INET_NTOA(src_ip) srcip, full_log FROM alert,location, data WHERE location.id = alert.location_id AND data.id = alert.id AND data.server_id = alert.server_id;
34
35PostgreSQL:
36>SELECT to_timestamp(timestamp), rule_id, location.name, full_log FROM alert,location, data WHERE location.id = alert.location_id AND data.id = alert.id AND data.server_id = alert.server_id;
37
38Output:
39
40+---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+
41| time | rule_id | location | srcip | full_log |
42+---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+
43| 2007-08-18 00:28:49 | 1002 | enigma->/var/log/messages | 0.0.0.0 | Aug 18 00:28:49 enigma dcid: Segmentation Fault 1q2 |
44| 2007-08-18 00:38:06 | 5715 | enigma->/var/log/authlog | 192.168.2.10 | Aug 18 00:38:02 enigma sshd[24284]: Accepted password for dcid from 192.168.2.10 port 34631 ssh2 |
45| 2007-08-18 00:38:21 | 5715 | enigma->/var/log/authlog | 192.168.2.10 | Aug 18 00:38:15 enigma sshd[20749]: Accepted password for dcid from 192.168.2.10 port 35755 ssh2 |
46+---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+
47