MonitorWare Knowledge Base

Here's a general starter for anyone needing to log PIX / ASA output to a database. It's is setup for an ASA but changing it to a PIX is pretty simple, just change all instances of ASA to PIX. The included rsyslog.conf file has everything needed to get started logging to a database. The database structure is as follows;

Code: Select all

DBname: hack

Tables:
+--------------------+
| Tables             |
+--------------------+
| traffic_log        |
+--------------------+
Fields:
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| log_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| log_time   | datetime         | NO   |     | NULL    |                |
| log_detail | varchar(15)      | NO   |     | NULL    |                |
| log_src_ip | varchar(20)      | NO   |     | NULL    |                |
| log_src_pt | varchar(5)       | NO   |     | NULL    |                |
| log_dst_ip | varchar(20)      | NO   |     | NULL    |                |
| log_dst_pt | varchar(5)       | NO   |     | NULL    |                |
| log_action | varchar(20)      | NO   |     | NULL    |                |
| protocol   | varchar(5)       | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+


Code: Select all

$ModLoad imuxsock
$ModLoad imklog
$ModLoad imudp.so
$UDPServerRun 514

$ModLoad MySQL
$ModLoad ommysql.so

###########################################################################
# Define the rsyslog template for syslog style date format.
###########################################################################
$template std_format, "%timestamp% %FROMHOST% %msg%\n"

###########################################################################
# Syslog:
# Insert your syslog stuff here.  Format it to use rsyslog.
###########################################################################
# Example;

*.info;mail,news,authpriv.none,local6.none      /var/log/messages;std_format

# Note the local.6.none.  This is used to keep the ASA logs from filling up
# /var/log/messages and possibly the / filesystem and crashing your machine.
# Firewall log files can grow to HUGE sizes quickly depending on your
# traffic. Make sure to inlcude local6.none (or whatever you decide to use)
# on other log files as well.
###########################################################################

###########################################################################
# PIX and ASA logging:
# PIX and ASA formats are identical as far as I can tell.
#
# Caveats:
# For some reason the standard msg output cuts off the month and day of the
# date string when related to the Cisco firewall.  So, We'll take the
# rawmsg and cutoff everything upto the date.
# NOTE: This format is setup to work with pla2 (Pix Logging Archatecture)
#       That's the reason for the double date stamp.  I don't know why it's
#       that way, but it is.  You can either remove the %timestamp% or
#       change it from %rawmsg% to %msg% and truncate the date section.
#       Or, do whatever you feel. :)
###########################################################################
$template pix_format, "%timestamp% %FROMHOST% %rawmsg:R,ERE,1,BLANK,0:<.*>([A-Z].*)--end%\n"

local6.*     /var/log/asa/asa-main.log;pix_format

###########################################################################
# Statments to filter on;
#
# NOTE: For whatever reason, some of the log identifier levels are differ
# from what's found in the documentation.  I.E. ASA-7-106100 that shows up
# in the log files is ASA-4-106100 in the documentation that I have.  I do
# believe that I have current docs, but I could be wrong.  After a little
# log watching you'll be able to figure it out quite quickly.
#
# This is probably the most needed statment.
# General access-list statemnts:
# ASA-7-106100: access-list Internet_access_in permitted tcp Inside/192.168.0.123(1234) -> Internet/100.100.100.100(21) hit-cnt 1 first hit [0x34098bb0, 0x0]
# ASA-7-106100: access-list Internet_access_in denied udp Internet/100.100.100.100(4321) -> Inside/192.168.0.123(53) hit-cnt 1 first hit [0x34098bb0, 0x0]
#
# Most people won't need this, but I've inlcuded it as an example.
# Shun statments:
# ASA-4-401002: Shun added: 100.100.100.100 0.0.0.0 0 0
# ASA-4-401004: Shunned packet: 100.100.100.100 ==> 192.168.0.123 on interface Internet
# ASA-4-401003: Shun deleted: 100.100.100.100
#
# Not so important. (To me anyway :) )
# ASA-7-710003: TCP access denied by ACL from 100.100.100.100/1234 to Internet:192.168.0.1/22
#
# This one is defined as a template but has not been tested.
# ASA-6-106015: Deny TCP (no connection) from 100.100.100.100/1234 to 192.168.0.123/25 flags RST ACK  on interface Internet
#
###########################################################################
# There are literally hundreds more identifiers.  Build filters on whatever
# you desire based on the templates below.
###########################################################################
# Templates:
# These temples output data to a log file.  I found this to be a great help
# when trying to debug things.
#
# Each temlpleate is associated with a Cisco Log Message Identitier.  The
# log entry for each identifier is unique, so we can output consistant data
# to the database using a filter for each identifier necessary.
#
# NOTE: The last statement in access-list-sql and no-connect-sql are NOT
#       separated as values. I don't know wheather it's a feature, bug,
#       or just supposed to work that way.  In any case it allows for the
#       correct labeling of tcp, udp, permitted, and denied packets.
#       I.E. '%msg:R,ERE,1,BLANK:access-list.*(udp)--end% %msg:R,ERE,1,BLANK:access-list.*(tcp)--end%'
#       Is a single value.
###########################################################################

$template access-list-sql,"insert into traffic_log (log_time, log_detail, log_src_ip, log_src_pt, log_dst_ip, log_dst_pt, log_action, protocol) values ('%timestamp:::date-mysql%', '%msg:R,ERE,1,BLANK:(ASA.*):--end%', '%msg:R,ERE,1,BLANK,0:(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:[A-Z].*/.*\((.*)\) ->.*--end%', '%msg:R,ERE,1,BLANK,0:[A-Z].*/(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:[A-Z].*/.*\((.*)\) hit-cnt.*--end%', '%msg:R,ERE,1,BLANK:access-list.*(permitted)--end% %msg:R,ERE,1,BLANK:access-list.*(denied)--end%', '%msg:R,ERE,1,BLANK:access-list.*(udp)--end% %msg:R,ERE,1,BLANK:access-list.*(tcp)--end%')",stdsql

$template no-connect-sql,"insert into traffic_log (log_time, log_detail, log_src_ip, log_src_pt, log_dst_ip, log_dst_pt, log_action, protocol) values ('%timestamp:::date-mysql% %msg:R,ERE,1,BLANK:(ASA.*):--end%', '%msg:R,ERE,1,BLANK,0:from (\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK,0:from.*/(.*) to--end%', '%msg:R,ERE,1,BLANK,0:to (\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:to.*\/(.*) flag--end%', '%msg:R,ERE,1,BLANK:(Deny)--end%', '%msg:R,ERE,1,BLANK:ASA.*(TCP)--end% %msg:R,ERE,1,BLANK:ASA.*(UDP)--end%')",stdsql

$template shun-add-sql, "insert into traffic_log (log_time, log_detail, log_src_ip, log_dst_ip, log_action) values ('%timestamp:::date-mysql%', '%msg:R,ERE,1,BLANK:(ASA.*): Shun--end%', '%msg:R,ERE,1,BLANK,0:(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK,0:[0-9].*(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:(Shun added)--end%')",stdsql

$template shun-del-sql, "insert into traffic_log (log_time, log_detail, log_src_ip, log_action) values ('%timestamp:::date-mysql%', '%msg:R,ERE,1,BLANK:(ASA.*): Shun--end%', '%msg:R,ERE,1,BLANK,0:(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:(Shun deleted)--end%')",stdsql


###########################################################################
# Expression Based Filters:
# Here we filter by the identifier explained above.  Filters can also be
# put in place for specific hosts.
# Note: when specifying individual hosts make sure you check your log
# file.  Hosts can either be logged by the IP address or the hostname
# depending whether or not, and how they are configured in the firewall.
###########################################################################

if $msg contains 'ASA-7-106100' and $msg contains 'our-ftp-server.com' then :ommysql:localhost,hack,dbuser,dbsecret;access-list-sql
if $msg contains 'ASA-4-401002' then :ommysql:localhost,hack,dbuser,dbsecret;shun-add-sql
if $msg contains 'ASA-4-401003' then :ommysql:localhost,hack,dbuser,dbsecret;shun-del-sql


Hopefully some of these ideas can be used in other configurations.

Please give your comments, improvements, etc...

MPH

• Custom written rsyslog.conf?
• Maintenance Contract?
• Installation support?

just quickly: that's a great post I'll make sure it is also mirrored in the wiki (http://wiki.rsyslog.com/index.php/Configuration_Samples)

Well, maybe I posted too soon. It appears that the BLANK parameter in a regex doesn't actually insert a blank field in the database, rather a space within the field. Hence if the first regex doesn't match and the second or third does, there will be space(s) in front of the matching criteria. Since the fields in question are constant in their placement, the field expression should be used instead of a regular expression.

The following code snippet for access-list-sql should replace the existing one to avoid problems when extracting the fields from the database later on.

Code: Select all

$template access-list-sql,"insert into traffic_log (log_time, log_detail, log_src_ip, log_src_pt, log_dst_ip, log_dst_pt, log_action, protocol) values ('%timestamp:::date-mysql%', '%msg:R,ERE,1,BLANK:(ASA.*):--end%', '%msg:R,ERE,1,BLANK,0:(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:[A-Z].*/.*\((.*)\) ->.*--end%', '%msg:R,ERE,1,BLANK,0:[A-Z].*/(\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)--end%', '%msg:R,ERE,1,BLANK:[A-Z].*/.*\((.*)\) hit-cnt.*--end%', '%msg:F,32:7%', '%msg:F,32:8%')",stdsql

The same will hold true for the no-connect-sql. Just change the

Code: Select all

'%msg:R,ERE,1,BLANK:ASA.*(TCP)--end% %msg:R,ERE,1,BLANK:ASA.*(UDP)--end%'

to

Code: Select all

'%msg:F,32:6%'

and it should be fine. Again I haven't used or test this, so it may need a little massaging.

Sorry for any confusion,

MPH