1 # Simple readme with some query examples.
2 # Examples for MySQL and PostgreSQL
7 > SELECT rule_id, level, description FROM signature;
10 2- View all categories (groups)
12 > SELECT * FROM category;
15 3- View all categories of a specific rule (1002 for example):
17 > SELECT rule_id, cat_name from category, signature_category_mapping WHERE rule_id = 1002 AND signature_category_mapping.cat_id = category.cat_id;
20 4- View all alerts (without data):
22 > SELECT * FROM alert;
25 5- View all alerts (with IP as string):
27 > SELECT rule_id, timestamp, INET_ATON(src_ip) srcip from alert;
30 6- View all alerts, including locations (IP as string and time as string):
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;
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;
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 +---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+