0

I am running syslog-ng and I am storing the log entries in mysql. Everything is working ok there. The problem is that I need to break down the field "msg" into columns for further analysis. How can I convert this string:

'[997799.060000] ACCEPT IN=br0 OUT=eth1 
MAC=00:24:aa:ab:f0:d2:80:e6:51:10:e3:86:08:00 
SRC=192.168.1.40 DST=74.120.224.137 LEN=64 
TOS=0x00 PREC=0x00 TTL=63 ID=48537 DF PROTO=TCP 
SPT=51504 DPT=443 SEQ=2289341783 ACK=0 WINDOW=65535'

Everything is on one line (stored in a 'text' field in mysql table), I just added the breaks for visibility... I need to convert that into something like:

INSERT INTO web_traffic(MAC, SRC, DST, PROTO, DPT) 
    VALUES('00:24:aa:ab:f0:d2:80:e6:51:10:e3:86:08:00',
        '192.168.1.40', '74.120.224.137', 'TCP', '443');

Any other insert format that could be easier would also be appreciated. I am looking to do this inside mysql so that I can use a trigger on the tables to insert.

Thanks,

jangeador
  • 594
  • 1
  • 6
  • 17

1 Answers1

3

You can do something like this

INSERT INTO web_traffic (mac, src, dst, proto, dpt)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(msg, 'MAC=', -1), ' ', 1) mac,
       SUBSTRING_INDEX(SUBSTRING_INDEX(msg, 'SRC=', -1), ' ', 1) src,
       SUBSTRING_INDEX(SUBSTRING_INDEX(msg, 'DST=', -1), ' ', 1) dst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(msg, 'PROTO=', -1), ' ', 1) proto,
       SUBSTRING_INDEX(SUBSTRING_INDEX(msg, 'DPT=', -1), ' ', 1) dpt
FROM syslog

Outcome:

|                                       MAC |          SRC |            DST | PROTO | DPT |
|-------------------------------------------|--------------|----------------|-------|-----|
| 00:24:aa:ab:f0:d2:80:e6:51:10:e3:86:08:00 | 192.168.1.40 | 74.120.224.137 |   TCP | 443 |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • That is really cool my friend. Exactly what I needed, plus a bonus because I did not know about SQLFiddle! – jangeador Oct 03 '14 at 15:45