Table of Contents

Cisco PIX to DBMS

Parse Cisco PIX messages and queue them into a database. Read from the database queue and send them to syslog and an archive.

Download example- Already included in recent ORION packages

DESCRIPTION

The application demonstrates database queuing of events in detail with a lot of event parsing and normalization. It is also a good example of a “best practices” design pattern for routing events through different filter stacks, such that the application is easily maintainable and expandable over time as message formats are added and changed. Lot's of SQL examples are given for the use with the SQL Command Filter and also regular expression examples to parse messages with the Regular Expression Group Filter. This application uses two ECA files that communicate with each other via a database queue. The application can be split so that the event reading, normalization, and database writing takes place on one host, and the database reading and output processing takes place on another host.

EXPLANATION

The example consists of two ECAs that communicate event records to each other by creating a database event queue.

PixToDBExample

The first ECA (PixToDBExample) reads Cisco PIX type events from a syslog or a textlog (EV_HOME/in.txt) and routes them through a variety of stacks where the PIX messages are normalized to extract certain common Cisco type key fields.

Raw Input Message:
Jan 15 2007 13:55:46: %PIX-4-400025: IDS:2154 Ping of Death Attack from 198.144.206.888 to 192.168.2.7 on interface outside

First stage parsing with Regular Expression Group Filter in the ProcessInput-Stack with the following regular expression provides these output fields:

(.*\:\d{2}\:\d{2})\:\s\%PIX-(\d)-(\d{6})\:\s(.*)

ev:pix.date_time = Jan 15 2007 13:55:46 
ev:pix.severity = 4 
ev:pix.msg_id = 400025 
ev:pix.msg = IDS:2154 Ping of Death Attack from 198.144.206.888 to 192.168.2.7 on interface outside 

In the RouteMessage-Stack the event is routed to another stack that is used to parse the specific message IDs. Please note that the Condition in the To-PIX-4-4000nn Route Event Filter uses a regular expression match 4000\d{2} that selects all PIX messages IDs that start with 4000 (such as 400025 or 400031) and routes them to the PIX-4-4000nn stack. There the Regular Expression Group Filter breaks the ev:pix.msg field into the following subfields with this regular expression:

(.*)\sfrom\s(.*)\sto\s(.*)\son\sinterface\s(.*)

ev:pix.msg_desc = IDS:2154 Ping of Death Attack 
ev:pix.src_ip = 198.144.206.888  
ev:pix.dest_ip = 192.168.2.7 
ev:pix.interface = outside 

All PIX-…. stacks send their events to the RouteOutput-Stack where the events are sent to the appropriate output destinations. In this case, the RouteOutput-Stack first writes the selected event fields into the SQL Database using the SQL Command Filter with the follow SQL expression. Please note that a boolean “READ” column is set to “false” on writing the event to the database, and that the “SAVETIME” column is written with the current system time to provide sequential event ordering in the database.

INSERT INTO PIX (PIX_MSG, SRC_IP, DEST_IP, PIX_MSG_ID, PIX_SEVERITY, SAVETIME,READ) 
VALUES ('ev:pix.msg', 'ev:pix.src_ip', 'ev:pix.dest_ip', 'ev:pix.msg_id', 
        'ev:pix.severity', CURRENT_TIMESTAMP, false)

As the final step, this ECA writes a copy of the event to the InputArchive using the Archive Writer destination. This is mostly done for debugging or diagnostic purposes. In the Event Viewer you can turn on all hidden event fields by selecting “Other misc fields” in the “View” menu. This is particularly helpful for debugging the regular expression field parsing and the SQL expressions during development.

DbtoOutputExample

The second ECA (DbtoOutputExample) generates a timed event once a second using a TimeMarker Source. Each event initiates a SQL database query using the first SQL Command Filter “selectOldestUnread” in the “queryDB” stack which returns the oldest event in the database for which the 'READ' flag is still false with this SQL query:

SELECT * FROM PIX WHERE READ=false AND SAVETIME = (SELECT MIN(SAVETIME) FROM PIX WHERE READ=false)

The database result set returned in the “ev:sql_out” field is broken out using the following regular expression with a Regular Expression Group Filter into these event fields:

27|IDS:2154 Ping of Death Attack from 198.144.206.888 to 192.168.2.7 on interface outside|198.144.206.888|192.168.2.7|400025|4|2007-01-25 15:25:47.825000000|false\n

(?s)(\d+)\|(.*)\|(.*)\|(.*)\|(\d+)\|(\d)\|.*

ev:rec.id = 27 
ev:pix.msg = IDS:2154 Ping of Death Attack from 198.144.206.888 to 192.168.2.7 on interface outside 
ev:src_ip = 198.144.206.888
ev:dest_ip = 192.168.2.7 
ev:pix.msg_id = 400025 
ev:pix.severity = 4  

Next the default ev:msg field is formatted using an Edit Field Filter to provide clean human readable messages showing all the relevant information. This ev:msg field is one of the default columns in the Event Viewer.

Found PIX message with following values:
ev:pix.msg = IDS:2154 Ping of Death Attack from 198.144.206.888 to 192.168.2.7 on interface outside
ev:src_ip = 198.144.206.888
ev:dest_ip = 192.168.2.7
ev:pix.msg_id = 400025
ev:pix.severity = 4 

As the last operational step the “updateDbRecord” SQL Command Filter sets the “READ” flag to 'true' for this particular database record, so that it will not be picked up during the next database read using this SQL command:

UPDATE PIX SET READ = true WHERE ID= ev:rec.id

Additional Ideas

Please note that this application retains the record in the database and only marks it as read. If you want to implement a FIFO queue, the “READ” flag is not necessary. Instead of calling an UPDATE command on the processed rec.id, just delete the record from the database. If you need to retrieve records from a database where your are not in control of the schema and cannot add a “READ” column, you can select a oldest record or the record with the lowest record ID, and store that particular record ID or time stamp in an variable or variable array using a Set Variable Filter, and specify that record ID or time stamp in the selection on the next query to avoid rereading records, again.

The ECA writes the resulting record into the “OutputArchive” using the Archive Writer destination, and a copy of the event is sent as a syslog message using the SysLog Sender which writes to the non-standard port 515(!) on localhost. Please note, that if you want to send events to another syslog receiver on another host(!) you should change the port to the standard 514 and change the destination host. Don't send to port 514 on localhost, as that is where this application takes its input, which may cause message looping!

HOW TO RUN THE EXAMPLE

Start the HSQLDB server

Edit the PixIn.txt file and copy the sample lines to the end of the file, and save the file. Please note, the ECS will only read new lines that have been added since the restart of the server, and it will ignore any lines in the input file that existed before the server started. Therefore, always copy the lines after the server shows that it has fully restarted.

If you have a syslog sender such as the free kiwi syslog sender, set it to send messages to the localhost (127.0.0.1) to the standard port 514. Copy and paste the sample lines from the PixIn.txt file, into the message field, and send them.

If you have a Cisco PIX firewall, you can set your ORION host to be a syslog destination on the PIX. Messages sent from the Cisco PIX will be processed, but you may need to change initial regular expression in the ProcessInput-Stack from (.*\:\d{2}\:\d{2})\:\s\%PIX-(\d)-(\d{6})\:\s(.*) to .*\%PIX-(\d)-(\d{6})\:\s(.*) and remove the ev:pix.date_time from the event field list. However, please be aware that only messages that match the particular PIX message IDs will be parsed and inserted into the database.

If everything was installed correctly and is running, you should be able to open the EventViewer and select the InputArchive or the OutputArchive and be able to see the newly processed events that you just sent via syslog or copied and pasted into the PixIn.txt file.

INSTALLATION

If you have run HSQLDB server and still have it running, stop it.
Insert the following line after other “CREATE TABLE” lines in the EV_HOME/sample_db/data/test.script file

CREATE MEMORY TABLE PIX(
      ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, 
      PIX_MSG VARCHAR, SRC_IP VARCHAR, DEST_IP VARCHAR, PIX_MSG_ID VARCHAR, 
      PIX_SEVERITY VARCHAR, SAVETIME TIMESTAMP, READ BOOLEAN)

Then follow the standard installation procedure for examples.

KEY SKILLS DEMONSTRATED

FILES

INPUT PROTOCOLS

SysLog Receiver, TextLog Receiver

OUTPUT PROTOCOLS

SysLog Sender, Database, Archive Writer

SUMMARY

COMPLEXITY: HIGH
ORION VERSION: 6.0
KEY WORDS: CISCO, PIX, DBMS, SQL, MESSAGE PARSING, REGULAR EXPRESSIONS, DBMS, DATABASE QUEUEING, SYSLOG, TEXTLOG, TIMEMARKER, HSQLDB, SysLog Receiver, TextLog Receiver, SysLog Sender, Archive Writer, Regular Expression Group Filter, Condition, Route Event Filter, SQL Command Filter, Set Variable Filter