Splitting a syslog-ng message

Question

I want to save the source IP address from an sshd log entry into a mysql database from within syslog-ng. Currently, I have a filter defined which matches on a substring of the desired log entry.

I can save the log entry, in it's entirety, to the DB however the $MSG part of the syslog entry contains a lot of extra data I don't want. Is there a way to "split" the fields of the syslog entry to only write the IP address to the DB?

This is my config:

filter f_sshd
{
   # (log entry) Sep  5 14:59:20 myhost4 sshd Starting session: shell on pts/0 for rbackup from 10.120.192.25 port 36894 id 0
   match("Starting session:" value ("MESSAGE") );
};


destination d_sshd
{
   sql( type(mysql)
   username("xxxxx")
   password("xxxxxxx")
   database("syslog")
   host("localhost")
   table("ssh")
   columns("host", "facility", "priority", "level", "pid", "tag", "timestamp", "program", "msg")
   values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PID", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
   indexes("timestamp", "host", "program", "pid", "message"));
};

log
{
   # s_stunnel is defined in syslog-ng/conf.d/stunnel.conf
   source(s_stunnel);
   filter(f_sshd);
   destination(d_sshd);
};

krisFR · Accepted Answer · 2018-09-07T18:25:56.600

You could create a parser using syslog-ng-patterndb to extract parts of the message.

Create the XML file to define your parser (/etc/syslog-ng/template_sshd.xml) :

<patterndb version='4' pub_date='2010-10-17'>
    <ruleset name='ssh' id='123456678'>
        <pattern>ssh</pattern>
            <rules>
                <rule provider='me' id='182437592347598' class='system'>
                    <patterns>
                        <pattern>Starting session: shell on @ESTRING:SSH_TERMINAL: @for @ESTRING:SSH_USERNAME: @from @ESTRING:SSH_CLIENT_ADDRESS: @port @NUMBER:SSH_PORT_NUMBER:@ id @NUMBER:SSH_ID@</pattern>
                    </patterns>
                    <examples>
                        <example>
                            <test_message program="ssh">Starting session: shell on pts/0 for rbackup from 10.120.192.25 port 36894 id 0</test_message>
                            <test_values>
                                <test_value name="SSH_TERMINAL">pts/0</test_value>
                                <test_value name="SSH_USERNAME">sampleuser</test_value>
                                <test_value name="SSH_CLIENT_ADDRESS">192.168.10.12</test_value>
                                <test_value name="SSH_PORT_NUMBER">42156</test_value>
                                <test_value name="SSH_ID">1</test_value>
                            </test_values>
                       </example>
                    </examples>
                </rule>
            </rules>
    </ruleset>
</patterndb>

Then in your syslog-ng.conf :

Define the parser :

parser sshd_pattern { db_parser(file("/etc/syslog-ng/template_sshd.xml")); };

Call the parser in your log directive :

log
{
   # s_stunnel is defined in syslog-ng/conf.d/stunnel.conf
   source(s_stunnel);
   parser(sshd_pattern);  <---- call parser
   filter(f_sshd);
   destination(d_sshd);
};

Use variable SSH_CLIENT_ADDRESS from the parser within your destination :

destination d_sshd
{
  file("/var/log/sshd.log"
  template("${SSH_USERNAME}; ${SSH_CLIENT_ADDRESS}; ${HOST}; ${FACILITY}; ${PRIORITY}; ${LEVEL}; ${PID}; ${TAG}; ${YEAR}-${MONTH}-${DAY} ${HOUR}:${MIN}:${SEC}; ${PROGRAM}; \n")
  template_escape(no)
 );
};

Unit test running :

pdbtool match -P "ssh" -M "Starting session: shell on pts/0 for rbackup from 10.120.192.25 port 36894 id 0" -p template_sshd.xml -c -D -v

Should return :

SSH_TERMINAL=pts/0
SSH_USERNAME=rbackup
SSH_CLIENT_ADDRESS=10.120.192.25
SSH_PORT_NUMBER=36894
SSH_ID=0

Adapted from this link : https://gist.github.com/linickx/8002981

Edit regarding your comment below :

Ideally, the SSH_TERMINAL key could swallow everything from the space after session: to the from bareword

Change the pattern in the XML like this :

<pattern>Starting session: @ESTRING:SSH_TERMINAL:from @@ESTRING:SSH_CLIENT_ADDRESS: @port @NUMBER:SSH_PORT_NUMBER:@ id @NUMBER:SSH_ID@</pattern>

Which returns :

# pdbtool match -P "ssh" -M "Starting session: shell on pts/0 for rbackup from 10.120.192.25 port 36894 id 0" -p template_sshd.xml -c -D -v

SSH_TERMINAL=shell on pts/0 for rbackup  <-- you got all between "session:" to "from"
SSH_CLIENT_ADDRESS=10.120.192.25
SSH_PORT_NUMBER=36894
SSH_ID=0

Further reading about pattern parsers : https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.16/administration-guide/72

Thanks, this is great! I didn't know about pdbtool either so double bonus! — Server Fault, Sep 07 '18 at 13:20
Will the template match across multiple whitespaces? I followed the link at the gist but the links there end in advertisements (still weeding through google results). Trying to adjust it to handle this message as well as the original: `Starting session: forced-command (key-option) '/root/bin/rbackup.sh' for root from 10.120.192.25 port 48528`. Ideally, the `SSH_TERMINAL` key could swallow everything from the space after `session: ` to the `from` bareword. — Server Fault, Sep 07 '18 at 14:08

Splitting a syslog-ng message

1 Answers1