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
hm. Syslog format isn't exactly fixed.
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.
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.
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.
Ok, using rsylog I found out how to drop the standard request and ack statements with the following entries into rsyslog.conf
local7.info /var/log/dhcpd/dhcpdfulllo
:msg, contains, "DHCP" ~
local7.info /var/log/dhcpd/dhcpdlonslo
I addition, I believe I can seperate the message into seperate fields with the following rsyslog template
$template StdSQLFormat,"insert into SystemEvents (Type, Addy, MAC, OTHERS, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg:F,32:3%','%msg:F,32
and then output to the appropriate mysql database using rsyslog-mysql.
Now the only problem I have is how to either extend phpLogCon for those additional fields and have them searchable, use another php browser based plugin, or develop a custom option. The custom option would be nice, but have no PHP programming experience. Is there a 'tool' out there to help create the php code for making custom webfrontend for a mysql database/s?
Thanks in advance.
local7.info /var/log/dhcpd/dhcpdfulllo
:msg, contains, "DHCP" ~
local7.info /var/log/dhcpd/dhcpdlonslo
I addition, I believe I can seperate the message into seperate fields with the following rsyslog template
$template StdSQLFormat,"insert into SystemEvents (Type, Addy, MAC, OTHERS, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg:F,32:3%','%msg:F,32
and then output to the appropriate mysql database using rsyslog-mysql.
Now the only problem I have is how to either extend phpLogCon for those additional fields and have them searchable, use another php browser based plugin, or develop a custom option. The custom option would be nice, but have no PHP programming experience. Is there a 'tool' out there to help create the php code for making custom webfrontend for a mysql database/s?
Thanks in advance.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial