BlakeGriffin
asked on
Exporting Linux Log File to MYSQL
I have a DHCP server that is logging DHCP to a separate log file in the format at the end of this post. Currently, I use the built in functionality of rsyslog to write to a mysql database and view with the phpLogCon front end. This is not my ideal implementation and am looking for suggestions to accomplish the following:
1. The phpLogCon lumps the whole description into one field in the database. I want to have a database in which the type, ipaddress, MAC, AID, CID, and event logged into seperate fields.
2. phpLogCon and rsyslog templates/schema would need to be modified to do this, but am unsure on how to do this.
3. I want to remove the standard ack, request, release, expired vents from the mysql database. How can I do this?
4. Is the rysyslog and phpLogCon route the best option or should I look for another route. If so, what should this be?
DHCP log format:
Jun 14 14:56:02 ltcdhcp-desktop dhcpd: ==> STB Lease 172.16.112.51 from MAC 1:0:3:e6:b:3e:ad assigned to AID: CO TEST CID: --- has committed
Jun 14 14:56:02 ltcdhcp-desktop dhcpd: DHCPREQUEST for 172.16.112.51 from 00:03:e6:0b:3e:ad via eth1
Jun 14 14:56:02 ltcdhcp-desktop dhcpd: DHCPACK on 172.16.112.51 to 00:03:e6:0b:3e:ad via eth1
Jun 14 14:56:04 ltcdhcp-desktop dhcpd: ==> DATA Lease 206.10.75.28 from MAC 1:0:1b:38:7a:57:e9 assigned to AID: CO TEST CID: --- has committed
Jun 14 14:56:04 ltcdhcp-desktop dhcpd: DHCPREQUEST for 206.10.75.28 from 00:1b:38:7a:57:e9 (SHOOTER07) via eth1
Jun 14 14:56:04 ltcdhcp-desktop dhcpd: DHCPACK on 206.10.75.28 to 00:1b:38:7a:57:e9 (SHOOTER07) via eth1
1. The phpLogCon lumps the whole description into one field in the database. I want to have a database in which the type, ipaddress, MAC, AID, CID, and event logged into seperate fields.
2. phpLogCon and rsyslog templates/schema would need to be modified to do this, but am unsure on how to do this.
3. I want to remove the standard ack, request, release, expired vents from the mysql database. How can I do this?
4. Is the rysyslog and phpLogCon route the best option or should I look for another route. If so, what should this be?
DHCP log format:
Jun 14 14:56:02 ltcdhcp-desktop dhcpd: ==> STB Lease 172.16.112.51 from MAC 1:0:3:e6:b:3e:ad assigned to AID: CO TEST CID: --- has committed
Jun 14 14:56:02 ltcdhcp-desktop dhcpd: DHCPREQUEST for 172.16.112.51 from 00:03:e6:0b:3e:ad via eth1
Jun 14 14:56:02 ltcdhcp-desktop dhcpd: DHCPACK on 172.16.112.51 to 00:03:e6:0b:3e:ad via eth1
Jun 14 14:56:04 ltcdhcp-desktop dhcpd: ==> DATA Lease 206.10.75.28 from MAC 1:0:1b:38:7a:57:e9 assigned to AID: CO TEST CID: --- has committed
Jun 14 14:56:04 ltcdhcp-desktop dhcpd: DHCPREQUEST for 206.10.75.28 from 00:1b:38:7a:57:e9 (SHOOTER07) via eth1
Jun 14 14:56:04 ltcdhcp-desktop dhcpd: DHCPACK on 206.10.75.28 to 00:1b:38:7a:57:e9 (SHOOTER07) via eth1
ASKER
Yeah......I understand syslog isnt fixed format (as I created log statements to make it more fixed. I also understand I can do that, but dont know where to start. I would like some recommendations on what the best route to take would be. In addition, I have not worked with scripts or rysylog templates to parse the data. So I would be looking for some direction there as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you're better of with a dedicated PHP app. Now i can read and slightly modify php code but i not create a new one from scratch. (Not enough training/use on php i am affraid).
It starts wit a Unix date, then the name of the reporting node then program name (optionaly with pid between []) colon, then the complete message line.
There are no more fields separately.
So what you can do is make a program that extracts all the data you want.
and reinserts it in new tables. That program can filter the data according to your rules.
And you can make a web-frontend that goes with it.