MonitorWare Knowledge Base

I would like to use rsyslog to log my mail server logs to a mysql database. But I need to parse out certain parts of the msg, such as delay time or dsn into their own fields in the database. I can see how to just insert all the msg text into a field in the database called msg, but is there a way to do custom fields and put particular parts of the msg text in those fields?

• Custom written rsyslog.conf?
• Maintenance Contract?
• Installation support?

You can do so with the property replacer, see here:

http://www.rsyslog.com/doc-property_replacer.html

You usually go by specifying regular expressions. If you search the forum, there should be some sample configurations. I am also about to build a regex checker/generator, if all goes well there will be such a page either tomorrow or at the end of the week.

HTH
Rainer

Ok, so I seem to be stuck when trying to get the mail logs into the database with a template.

I read through:
http://www.rsyslog.com/doc-property_replacer.html
and thought it made sense to pull the data I wanted out with based on a comma delimited field number.
Excerpt from maillog on sending host : 6EA161500C5: to=<dxxx@foo.com>, relay=aspmx.l.google.com[209.85.217.181]:25, delay=2.9, delays=0.36/0.07/0.59/1.9, dsn=2.0.0, status=sent

I then referenced
http://www.rsyslog.com/Documentation-/sample.conf.html.phtml for writing data to a database with an insert statement.

Here is the finished statement I came up with,
$template db,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, dsn) values ('%msg%', %syslogfacility%, '%HOSTNAME%',%syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%' '%msg:F,6%' )",sql

mail.* :ommysql:127.0.0.1,Syslog,rsyslog,xxxxx;db

I want to insert the 6th comma delimited field '%msg:F,6%' into the field dsn in my database. Unfortunately it does not seem to be inserting anything. I did notice from the sample.conf file there is a *.* >hostname,dbname,userid,password;dbFormat example but I didn't know if that was depricated for the newer module type statement like this :ommysql:127.0.0.1,Syslog,rsyslog,xxxxx;db

I guess you do not mean F,6. This means the ASCII character 6 (the control character ACK) is used as the field delimiter - that's a non-printable character. Also, you do not specify which field you want, that comes after a colon. So you could probably mean "F:6", but then the field delimiter is TAB, which I think is not present inside the message. What part exactly would you like to parse out?

The dsn=2.0.0.

Sorry, I must have misunderstood the doc I was reading about the position stuff. I think I understand now.

So 44 is the ASCII comma value. So to pull the 6th field with comma delimitation it would be this '%msg:F,44:6%' ?

No problem, and, yes, F,44:6 should be the right sequence. You may need to fiddle a bit with it, but it should work. I did an online config generation tool for regexes yesterday, I should probably do the same thing for fields. If you have a minute, you may want to have a look at http://www.rsyslog.com/tool-regex and let me know what you think.

Thanks,
Rainer

I made the change of '%msg:F,44:6%', but it still does not seem to be dropping anything into that field in the db.

I also noticed that I needed the following to make it actually put stuff in the db:
mail.* >127.0.0.1,Syslog,rsyslog,xxx;db

The syntax at then, ;db is correct to make it apply my template?

doug.hairfield wrote:I made the change of '%msg:F,44:6%', but it still does not seem to be dropping anything into that field in the db.

Play a bit with the field number. I think I counted less fields

doug.hairfield wrote:I also noticed that I needed the following to make it actually put stuff in the db:
mail.* >127.0.0.1,Syslog,rsyslog,xxx;db

The syntax at then, ;db is correct to make it apply my template?

">" is the old style and ":ommysql:" is the new style. They are equivalent. And, yes, ";db" sounds good.

so it seems when I try to apply the template:

$template db,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, dsn) values ('%msg%', %syslogfacility%, '%HOSTNAME%',%syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%' '%msg:F,44:5%')",sql

like so:
mail.* :ommysql:127.0.0.1,Syslog,rsyslog,xxx;db

I don't get anything written to the db, but if I drop the ;db it will put data in the db using the default db template values. I don't see anything in the mysql error log. and I don't get any warnings in the /var/log/messages, is there anywhere else I can look to tell me what's going wrong? I'm assuming I have something wrong with my template syntax somewhere but I can't figure out where.

If nothing else helps, the debug log may be enlightning. Details here:

http://www.rsyslog.com/doc-troubleshoot.html

Rainer

I figured it out, was missing a comma after '%syslogtag%' in my Value string. It's working now.

I have been testing out the reg-expression checker to pull out the first delay value from:
16:22:06 staging brontostaging/smtp[12499]: 734EF1500C5: to=<doug@bronto.com>, relay=aspmx.l.google.com[209.85.217.183]:25, delay=1.9, delays=0.21/0/0.41/1.3, dsn=2.0.0, status=sent (250 2.0.0 OK 1226092921 8si5831882gxk.43)

with the expression: %msg:R,BRE: /delay=(\d\.\d\)/:--end%
but does not see to be working, though I'm pretty n00b at reg-exp.

Could you give an example on how to use the regular-expression checker you wrote? Not sure if I need to have the %msg:R,ERE,1 etc or just the reg-exp in the field.

doug.hairfield wrote:I have been testing out the reg-expression checker to pull out the first delay value from:
16:22:06 staging brontostaging/smtp[12499]: 734EF1500C5: to=<doug@bronto.com>, relay=aspmx.l.google.com[209.85.217.183]:25, delay=1.9, delays=0.21/0/0.41/1.3, dsn=2.0.0, status=sent (250 2.0.0 OK 1226092921 8si5831882gxk.43)

with the expression: %msg:R,BRE: /delay=(\d\.\d\)/:--end%
but does not see to be working, though I'm pretty n00b at reg-exp.

That's probably a bug in the regex tool, as I said, it is brandnew. Thanks for bringing this up, I'll use this sample to see what goes wrong.

Rainer

Just for those that follow the thread (the info is also on another with the OP). I have fixed the regex tool. It should now return the correct results (and has been verified to do so, but you never know... ).

Rainer

Just a suggestion, but it might be good to also include returning NULL when inserting into a DB, which I think is the default value if the data is not found it is looking for.

That's a useful suggestion, at least for numerical values. For text values, it is a bit complicated, because the quotes are not part of the property itself (they are part of the template). I need to think about that. Anyhow, for numerical values it makes a lot of sense...