Archiving Prelude Alerts

Marking alerts as "handled" is an important feature of Security Event Management (SEM) systems.  This is necessary to support an intrusion detection workflow as well as to demonstrate compliance.  Unfortunately the open source edition of Prelude does not support this natively.

In order to add this functionality to Prelude, I have leveraged MySQL triggers to "archive" alerts to a separate database when they are deleted.  This has the advantage of creating a database of "handled" events using the native Prelude delete GUI without modifying the Prelude application.  The down side is that no additional "meta" information is recorded such as who handled the alert, what determination was reached, etc.  Anyone interested in that may want to look into the commercial version of Prelude with ticketing support.  If a simple solution is what you need, read on for details of how I set it up.

 

# First, create an empty archive database with the structure of the existing prelude db

mysqldump --no-data --databases --add-drop-database --ignore-table prelude.Prelude_Heartbeat --ignore-table prelude._format -u root -p prelude|sed -e 's/`prelude`/`archive_prelude`/' -e 's/auto_increment//'>prelude.sql

# Add on _format table including it's data.  Prelude tools like prewikka and preludedb-admin use this to determine the database type and version

mysqldump -u root -p prelude _format>>prelude.sql

# Import the new database structure to MySQL

mysql -u root -p <prelude.sql

# Grant permissions to the prelude user for the archive database

mysql -u root -p -e 'grant all on archive_prelude.* to ’prelude’@localhost;'

# Each table that makes up an alert needs a trigger, which is 35 triggers.  Instead of defining them all by hand, we'll use a SQL script, originally from http://houssem.webdebug.me/content/database-table-history-using-schemas-and-mysql-triggers and modified for our needs.

# Use the following script to create the triggers

vi gen-triggers.sql

--------------------

select     concat(

                   'delimiter $$',char(10)

               ,   'create trigger archive_',tables.table_name,char(10)

               ,   'after delete on ',tables.table_schema,'.',tables.table_name,char(10)

               ,   'for each row ',char(10)

               ,   'begin',char(10)

               ,   '    insert',char(10)

               ,   '    into    archive_',tables.table_schema,'.',tables.table_name,'(',group_concat(

                       concat(

                           '       '

                       ,   columns.column_name

                       ,   char(10)

                       ,   '    '

                       )

                   )

               ,   ') values (',group_concat(

                       concat(

                           '       '

                       ,   'old.',columns.column_name

                       ,   char(10)

                       ,   '    '

                       )

                   )

               ,   ');',char(10)

               ,   'end',char(10)

               ,   '$$',char(10)

               )

    from       information_schema.tables       

    inner join information_schema.columns

    on         tables.table_schema        = columns.table_schema

    and        tables.table_name          = columns.table_name 

    where      tables.table_schema        = SCHEMA()

    and        tables.table_type          = 'BASE TABLE'

    and        tables.table_name          NOT IN ('_format','Prelude_Heartbeat')

    group by   tables.table_schema

    ,          tables.table_name

    ;

--------------------

# Run the SQL script, removing some extra garbage in the output.  It's a hack, but it's better than doing it by hand.

mysql -u prelude -p prelude <gen-triggers.sql|sed -e '1,6d' -e '{:q;N;s/\\n/\n/g;t q}'>triggers.sql

# Use mysql to create triggers

mysql -u root -p prelude <triggers.sql

# Now any deleted alerts will be added to the archive database

# To delete an alert or group of alerts, select them in the prewikka interface and click the delete button

# Use the following command to view count of archived alerts (example is for November 2009)

/usr/local/bin/preludedb-admin count alert --criteria "alert.create_time > '2009-11-1' && alert.create_time < '2009-11-30'" "type=mysql user=prelude pass=<<Enter Password Here>> name=archive_prelude"

• Modify triggers for tables containing a _parent_type to only insert rows where _parent_type is not 'H' - to prevent archiving heartbeats
• Update one of the existing fields - probably alert.analyzer_time - on insert into the archive database so that it's possible to identify when an alert was archived
• Assess the performance impact from the triggers
• Database optimization in general including researching table optimization, storing tables in separate files per table

Obviously this technique is not going to work for everyone, but should provide some basic workflow structure.