Bulk Update Security Onion Alerts

Posted on Mon 15 May 2017 in howto

Security Onion is a fantastic Open Source IDS distribution created by Doug Burks and Security Onion Solutions. Per their own about page:

Security Onion is a Linux distro for intrusion detection, network security monitoring, and log management. It’s based on Ubuntu and contains Snort, Suricata, Bro, OSSEC, Sguil, Squert, ELSA, Xplico, NetworkMiner, and many other security tools.

Security Onion is really one of my favorite security tools, but as with any IDS tool, alerts can pile up and acknoweldging them or categorizing them correctly can be very time consuming. The Onion uses one database to store alerts (Sguil) and multiple interfaces for working through those alerts. My perferred interface is the web-based Squert app, which comes pre-installed on the Onion. (Sguil also includes a tcl-based interface, which is powerfull but not as pretty or user-friendly.)

When a (very) large number of alerts are generated on a single signature, sometimes it is better to run an UPDATE query on the Sguil database, instead of manually updating alerts in the Squert console.

IMPORTANT: This should not be the first course of action! Please use this process only when the number of alerts on a single signature is prohibitively large for categorizing through the Squert console.

First, stop the Sguil process on the Security Onion central server:

[email protected]:~$ sudo nsm_server_ps-stop
Stopping: securityonion
* stopping: sguil server                        [  OK  ]

Three fields need updated to correctly mark an alert as categorized:

  • status
  • last_modified
  • last_uid

To make SQL queries easier, consider adding this to your ~/.bash_aliases file:

alias query='sudo mysql --defaults-file=/etc/mysql/debian.cnf -Dsecurityonion_db -e '

To determine your uid on the database, run this SELECT query:

jma@onion-server:~$ query 'SELECT uid,username FROM user_info;'
| uid | username |
|   1 | auto     |
|   2 | jma      |

Available status values are:

jma@onion-server:~$ query 'SELECT * FROM status;'
| status_id | description                | long_desc                                  |
|         0 | New                        | Real Time Event                            |
|         1 | No Further Action Required | No Further Action Required                 |
|         2 | Escalated                  | Escalated                                  |
|        11 | Category I                 | Unauthorized Root Access                   |
|        12 | Category II                | Unauthorized User Access                   |
|        13 | Category III               | Attempted Unauthorized Access              |
|        14 | Category IV                | Successful Denial of Service Attack        |
|        15 | Category V                 | Poor Security Practice or Policy Violation |
|        16 | Category VI                | Reconnaissance/Probes/Scans                |
|        17 | Category VII               | Virus Infection                            |

Run a SELECT query, to verify the correct rows will be updated:

jma@onion-server:~$ query 'SELECT count(*) as evt, signature FROM event WHERE signature_id="2101201" AND last_modified IS NULL;'
| evt   | signature                    |
| 30944 | GPL WEB_SERVER 403 Forbidden |

Note the value of the evt field for validation later.

As an aside, the above query did not limit the results based on date. If that is necessary, a sample query might be:

jma@onion-server:~$ query 'SELECT count(*) as evt, signature FROM event WHERE signature_id="2101201" AND timestamp > "2017-05-10 00:00:00" AND last_modified IS NULL;'
| evt | signature                    |
| 848 | GPL WEB_SERVER 403 Forbidden |

Once the proper rows have been identified, the UPDATE query can be executed:

jma@onion-server:~$ query 'UPDATE event SET status="1", last_uid="2", last_modified="2017-05-10 09:44:01" WHERE signature_id="2101201" AND last_modified IS NULL;'

In the above example, the status was set to 'No Further Action Required'.

And a follow-up SELECT query to validate the results:

jma@onion-server:~$ query 'SELECT count(*) as evt, status, last_uid, signature FROM event WHERE last_modified="2017-05-10 09:44:01" AND signature_id="2101201";'
| evt   | status | last_uid | signature                    |
| 30944 |      1 |        2 | GPL WEB_SERVER 403 Forbidden |

The value in the evt field of this query should match what you recorded previously.

Be sure to start the Sguil server process when you're done!

[email protected]:~$ sudo nsm_server_ps-start
Starting: securityonion
  * starting: sguil server                              [  OK  ]

Security Onion has a ton of other useful stuff and I'll probably be working through some of that here in the future.