Templates
Templates are a key feature of rsyslog. They allow to specify any format a user might want. They are also used for dynamic file name generation. Every output in rsyslog uses templates – this holds true for files, user messages and so on. The database writer expects its template to be a proper SQL statement – so this is highly customizable too.
Templates are specified by template() statements. They can also be specified via $Template legacy statements. Note that these are scheduled for removal in later versions of rsyslog, so it is probably a good idea to avoid them for new uses.
More information for templates here.
In our case rsyslog collects logs from very different equipment in a MySQL database. Windows Event Logs are sent by Datagram SyslogAgent. IIS logs are read and sent from Snare Epilog. For network equipment we use a standard syslog format, and for Linux and BSD servers rsyslog. So we have to use custom templates. In new format this looks like:
template(name="log_win" type="string" option.sql="on"
string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, SysLogTag, EventLogType, EventSource, EventId, NTSeverity, InfoUnitID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', '%syslogtag%', '%procid%', '%syslogtag:F,91:1%', '%msg:F,32:2%', %syslogseverity%, 3)"
)
template(name="log_net" type="string" option.sql="on"
string=Message, Facility, FromHost, Priority, EventID, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%syslogpriority%', '%eventid%', '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%programname%','%procid:R,ERE,0,ZERO:[0-9]+--end%')"
)
template(name="log_iis" type="string" option.sql="on"
string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, SysLogTag, InfoUnitID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', 'IISWebLog', 3)"
)
In oldest format:
$template log_win,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, SysLogTag, EventLogType, EventSource, EventId, NTSeverity, InfoUnitID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', '%syslogtag%', '%procid%', '%syslogtag:F,91:1%', '%msg:F,32:2%', %syslogseverity%, 3)",sql
$template log_net,"insert into SystemEvents (Message, Facility, FromHost, Priority, EventID, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%syslogpriority%', '%eventid%', '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%programname%','%procid:R,ERE,0,ZERO:[0-9]+--end%')",SQL
$template log_iis,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, SysLogTag, InfoUnitID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', 'IISWebLog', 3)",sql
So we defined different formats and now need to apply them. One option is to use server’s hostname like this:
if ( $HOSTNAME == 'Core-Router' or $HOSTNAME startswith_i 'router' or $HOSTNAME startswith_i 'switch' ) then :ommysql:127.0.0.1,MySQL_DB,MySQL_user,MySQL_pass;log_net if ( $HOSTNAME == 'Core-Router' or $HOSTNAME startswith_i 'router' or $HOSTNAME startswith_i 'switch' ) then ~
Or using specific text in one of variables in rsyslog:
if ( $syslogtag startswith_i 'IISWebLog' ) then :ommysql:127.0.0.1,MySQL_DB,MySQL_user,MySQL_pass;log_iis if ( $syslogtag startswith_i 'IISWebLog' ) then ~
And at the end of our rsyslog.con it is a good idea to put a final rule witch collects all non categorized logs before. Something like this:
*.* :ommysql:127.0.0.1,MySQL_DB,MySQL_user,MySQL_pass
Note:
Event logs from windows by default comes with different special symbol for CarrigeReturn. To fix this make a new DWORD in registry HKEY_LOCAL_MACHINE\SOFTWARE\Datagram\SyslogAgent – CarrigeReturnReplacementCharInASCII = 32dec
For searching, reviewing and analyzing network event data, including syslog, windows event log and many other after that we use Adiscon LogAnalyzer. It is a free, GPLed open source application written mostly in php. Data can be obtained from databases but also from plain text files, for example those that are written by the syslogd.