Insert firelog into mysql

Question

Hi,

im interesting of inserting firewall log which im printing to the stdout to mysql database as well.

the line output is:

16:51:56 drop Nova <eth0 Attack Info: MS Word cascading style sheet vulnerability detected (MS08-026); attack: Content Protection Violation; viola profile: Default_Protection; src: udis; dst: Nova; proto: tcp; product: viola; service: http; s_port: 48125;
16:35:13 drop Nova <eth0 Attack Info: Macrovision InstallShield ActiveX memory corruption; attack: Web Client Enforcement Violation; SmartDefense profile: Default_Protection; src: udis; dst: Nova; proto: tcp; product: SmartDefense; service: http; s_port: 44607;

i can use perl, bash.

Tx

-Udi

Answer 1

1. Define a table structure, which fields will correspond to what part of the lines,
2. use a regex or split to store each defined part into a variable
3. build the INSERT string from the variables obtained in step 2
4. Pipe it to mysql

Example:

~> echo '16:51:56 drop foo <tcpip scan' | \
   perl -nle "m/(\d{2}:\d{2}:\d{2})\s(.+?)\s(.+?)\s<(.+)$/; \
   print \"INSERT INTO flog(date,action,machine,attack) \
   VALUES ('\$1','\$2','\$3','\$4')\"" | mysql -uroot -ppass flog

You could use perl itself to insert the values with placeholders with DBI. That'd be safer in case the log has some special SQL characters.