# Simple readme with some query examples.
# Examples for MySQL and PostgreSQL


1- View all rules:

> SELECT rule_id, level, description FROM signature;


2- View all categories (groups)

> SELECT * FROM category;


3- View all categories of a specific rule (1002 for example):

> SELECT rule_id, cat_name from category, signature_category_mapping WHERE rule_id = 1002 AND signature_category_mapping.cat_id = category.cat_id;


4- View all alerts (without data):

> SELECT * FROM alert;


5- View all alerts (with IP as string):

> SELECT rule_id, timestamp, INET_ATON(src_ip) srcip from alert;


6- View all alerts, including locations (IP as string and time as string):

MySQL:
>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;

PostgreSQL:
>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;

Output:

+---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+
| time                | rule_id | location                  | srcip        | full_log                                                                                         |
+---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+
| 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                                              |
| 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 |
| 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 |
+---------------------+---------+---------------------------+--------------+--------------------------------------------------------------------------------------------------+