We help IT Professionals succeed at work.

How to use PERL to ¨follow" output of syslog, convert to CSV then input to MySQL in real time

rleyba828
rleyba828 asked
on
Hi Team,

 I just need some help on a PIPING problem where I need to capture the output of a syslog file then input this to mysql.  The issues is actually composed of 3 parts.

1. tail -F syslog     --> this captures the lines appended to syslog in real time.

2. http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26218088.html
    This converts a normal syslog file into CSV format as very graciously supplied by one of the experts exchange members.

3.   load data local infile 'syslog.csv' into table test.syslog fields terminated by ',' enclosed by '"' lines terminated by '\n' (month,day,time,host,message);

     This one appends a normal syslog.csv file into a MySQL database.   However it has to be executed from within the MYSQL prompt and not from the linux shell.

---------------------------------------------------------------------------------------------------
I just need help on how to revise the steps above so they can be contracted I suppose in one simple script that captures live syslog output, convert to csv and append to Mysql.

Thanks in advance for any suggestions offered.

Comment
Watch Question

Check out File::Tail
http://search.cpan.org/~mgrabnar/File-Tail-0.99.3/Tail.pm
and using perl to connect to a database
http://www.tizag.com/perlT/perlmysqlquery.php

Inside the while loop (see the page for simple example) do your csv munge, and do mysql inserts.

This probably is not an efficient solution. A better solution would probably be to have syslog log directly to a mysql db.  Not sure which syslog server your using but if yours doesn't support direct logging to sql its easiy enough to replace syslog with one that will.

Author

Commented:
Hi Nick,

   Thanks for the help above.  Unfortunately, I am not a programmer so I am trying my best to follow the first link above...but  it is hard to me to progress beyond the tail -F syslog  command that I know..

   For the second line, was wondering if you could guide me to the part of the example where I could insert the snippet of perl text  (for converting raw lines into CSV) into the SQL statements?

   I agree that this is NOT the efficient solution.....but the syslog server is owned by another team so I have little control over asking them to move to a more current version.   I believe a program like syslog-ng or rsyslog is the better tool.

   Are you able to assist with my original query of tail -F syslog > perl-program-for-mysql-insert?

thanks.
use File::Tail;
  $file=File::Tail->new("/some/log/file");
  while (defined($line=$file->read)) {
      cvs munging here
      sql statements here
  }

Author

Commented:
OK...this should work....I''ll work out the exact syntax and commands.   Thanks for the guidance.