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

[Jeremy M. Guthrie]

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 at 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 at cdw.com