Thanks. That worked like a charm. On Wednesday 03 February 2010, Zoltán Pallagi wrote:

Hi Jeremy,

Sorry for beeing slow to reply.

From our documentation: MSG or MESSAGE: "Text contents of the log message without the program name and pid. Note that this has changed in syslog-ng version 3.0; in earlier versions this macro included the program name and the pid. In syslog-ng 3.0, the MSG macro became equivalent with the MSGONLY macro. The program name and the pid together are available in the MSGHDR macro."

MSGHDR: "The name and the pid of the program that sent the log message in PROGRAM: PID format. Includes a trailing whitespace. Note that the macro returns an empty value if both the program and pid fields of the message are empty."

So, if you are using v3.0 and if you would like to use the same template being in a file, you need to use one like this: columns("date",'host' "message") values( '$DATE', '$HOST','$MSGHDR$MSG'));

the output of this in a db: sqlite> select * from messages_sqlite; Feb 3 11:12:52|thor|sshd[27062]: Received signal 15; terminating.

and in a file: Feb 3 11:12:52 thor sshd[27062]: Received signal 15; terminating.

Of course, you can also use other macros in this template if it is needed (facility, level, as I saw them in your example template)

Jeremy M. Guthrie írta:

I have an issue where what is being written to file is different than what I have being written to disk. I don't know if the macro is broken, my understanding is wrong, or I am just using the wrong macro altogether.

My understanding is roughly as follows: PROGRAM The name of the program sending the message. PID The PID of the program sending the message. MSG or MESSAGE Message contents including the program name and pid. MSGONLY Message contents without the program name.

1. The first thing I ran into: Example messages on file: Jan 25 08:34:15 XXXXXXXXXX sshd[7209]: service name is :: sshd

Same example message in SQL: Jan 25 08:34:15 XXXXXXXXXX service name is :: sshd

template("INSERT INTO tsyslog (host, facility, severity, date, time, message) VALUES ( '$HOST','$FACILITY_NUM','$LEVEL_NUM','$YEAR-$MONTH-$DAY','$HOUR:$MIN: $SEC', '$MSG');\n" )

2. Then what I tried to get it closer.... Example messages on file: Jan 25 08:40:18 XXXXXXXXXX sshd[7209]: service name is :: sshd

Same example message in SQL: Jan 25 08:40:18 XXXXXXXXXX sshd: service name is :: sshd

template("INSERT INTO tsyslog (host, facility, severity, date, time, message) VALUES ( '$HOST','$FACILITY_NUM','$LEVEL_NUM','$YEAR-$MONTH-$DAY','$HOUR:$MIN: $SEC', '$PROGRAM: $MSG');\n" )

3. Then what I tried to get even closer: Example messages on file: Jan 25 08:53:15 XXXXXXXXXX sshd[7209]: service name is :: sshd

Same example message in SQL: Jan 25 08:53:15 XXXXXXXXXX sshd[7209]: service name is :: sshd

template("INSERT INTO tsyslog (host, facility, severity, date, time, message) VALUES ( '$HOST','$FACILITY_NUM','$LEVEL_NUM','$YEAR-$MONTH-$DAY','$HOUR:$MIN: $SEC', '$PROGRAM[$PID]: $MSG');\n" )

However, this broke messages coming from Cisco devices... Jan 25 08:55:10 FIREWALL %FWSM-3-710003[]: tcp access denied by ACL from 188.23.180.240/3826 to outside:172.26.54.65/445

The message above should be: Jan 25 08:55:10 FIREWALL %FWSM-3-710003: tcp access denied by ACL from 188.23.180.240/3826 to outside:172.26.54.65/445

Notice the above '[]' after the FWSM code? I am running 3.0.5 syslog-ng.

Ideally I'd get both: Jan 25 08:55:10 FIREWALL %FWSM-3-710003: tcp access denied by ACL from 188.23.180.240/3826 to outside:172.26.54.65/445

AND

Jan 25 08:53:15 XXXXXXXXXX sshd[7209]: service name is :: sshd

Is the MSG/MESSAGE macro broken? What am I missing?

-- pzolee

-- -------------------------------------------------- Jeremy M. Guthrie jeremy.guthrie@cdw.com Managed Cisco Security Services Hosting and Managed Services Technical Architect Phone: 608-298-1061 CDW Fax: 608-288-3007 5520 Research Park Drive NOC: 608-298-1102 Madison, WI 53711 NOC Email: hmshelp@cdw.com