[syslog-ng] Getting a SQL insert to match what's being written to file

[Zoltán Pallagi]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.balabit.hu/pipermail/syslog-ng/attachments/20100203/b02ac883/attachment-0001.htm