We help IT Professionals succeed at work.

Exporting Linux Log File to MYSQL

BlakeGriffin
BlakeGriffin asked
on
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
Comment
Watch Question

nociSoftware Engineer
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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/dhcpdfulllog
  :msg, contains, "DHCP" ~
  local7.info /var/log/dhcpd/dhcpdlonslog

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:5%','%msg:F,32:8%','%msg:F,32:OTHERS%'   %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL

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.


nociSoftware Engineer
Distinguished Expert 2018

Commented:
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).