How to import data (Cisco PIX) into PostgreSQL database?

[nsa1]

Hello,

How can we get our output from the Cisco PIX 550 Firewall (see example below), into a PostgreSQL database? We would like to import the output into different fields, so that we are able to sort the information by time, date etc but the output from the Cisco PIX firewall is not comma separated as you see. Even if it was, we still would not know how to accomplish the task of importing it to a Postgres database
PS: We are using PostgreSQL 8.0 (Redhat 9.0)

Example of the Cisco PIX output file:

[root@snortids 30]# cat pixlog-2005-03-30.log |more
2005-03-30 00:00:31 192.168.1.105 %PIX-6-302015: Built outbound UDP connection 4
906729 for outside:212.20.204.5/53 (212.20.204.5/53) to inside:192.168.1.111/165
96 (212.20.204.111/16596)
2005-03-30 00:00:31 192.168.1.105 %PIX-6-302016: Teardown UDP connection 4906729
for outside:212.20.204.5/53 to inside:192.168.1.111/39622 duration 0:00:01 byte
s 181
2005-03-30 00:01:33 192.168.1.105 %PIX-6-302013: Built inbound TCP connection 49
06730 for outside:211.112.229.99/264 (211.112.229.99/264) to inside:192.168.1.10
8/22 (212.20.204.108/22)
2005-03-30 00:01:33 192.168.1.105 %PIX-6-302014: Teardown TCP connection 4906730
for outside:211.112.229.99/264 to inside:192.168.1.108/22 duration 0:00:00 byte
s 0 TCP Reset-I
2005-03-30 00:01:33 192.168.1.105 %PIX-6-302015: Built outbound UDP connection 4
906731 for outside:212.20.204.5/53 (212.20.204.5/53) to inside:192.168.1.111/566
15 (212.20.204.111/56615)
2005-03-30 00:01:33 192.168.1.105 %PIX-6-302013: Built inbound TCP connection 49
06732 for outside:211.112.229.99/264 (211.112.229.99/264) to inside:192.168.1.10
9/22 (212.20.204.109/22)
2005-03-30 00:01:33 192.168.1.105 %PIX-6-302016: Teardown UDP connection 4906731
for outside:212.20.204.5/53 to inside:192.168.1.111/39622 duration 0:00:01 byte
s 181

 

[rycamor]

Well, what columns would you *like* to separate this data into? Split the above example into the appropriate columns with a "|" for now, so we can get an idea how you would like to design your table.

Basically, unless you have additional choices in how to output the file in the PIX, you will have to do some text processing/regular expressions work to get things separated the way you want. PostgreSQL itself has pretty good regex and text processing support, so you might be able to import this into a one-column table, and then write queries which extract the fields, using SELECT INTO to create a new table with all your columns.