Hi
Here my rsyslog config file placed under /etc/rsyslog.d/48-mysql.conf
$ModLoad ommysql
$template dbFormatHost1,"insert into SystemEventsHost1 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:(.+)(\[[0-9]{1,5}\]).*--end%', '%syslogtag:R,ERE,1,BLANK:\[([0-9]{1,5})\]--end%')",SQL $template dbFormatHost2,"insert into SystemEventsHost2 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:(.+)(\[[0-9]{1,5}\]).*--end%', '%syslogtag:R,ERE,1,BLANK:\[([0-9]{1,5})\]--end%')",SQL
:source , isequal , "host1" :ommysql:localhost,Syslog,rsyslog,mypassword;dbFormatHost1 :source , isequal , "host2" :ommysql:localhost,Syslog,rsyslog,mypassword;dbFormatHost2
Here my database table structure
CREATE TABLE IF NOT EXISTS `SystemEventsHost1` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CustomerID` bigint(20) DEFAULT NULL, `ReceivedAt` datetime DEFAULT NULL, `DeviceReportedTime` datetime DEFAULT NULL, `Facility` smallint(6) DEFAULT NULL, `Priority` smallint(6) DEFAULT NULL, `FromHost` varchar(60) DEFAULT NULL, `Message` text, `NTSeverity` int(11) DEFAULT NULL, `Importance` int(11) DEFAULT NULL, `EventSource` varchar(60) DEFAULT NULL, `EventUser` varchar(60) DEFAULT NULL, `EventCategory` int(11) DEFAULT NULL, `EventID` int(11) DEFAULT NULL, `EventBinaryData` text, `MaxAvailable` int(11) DEFAULT NULL, `CurrUsage` int(11) DEFAULT NULL, `MinUsage` int(11) DEFAULT NULL, `MaxUsage` int(11) DEFAULT NULL, `InfoUnitID` int(11) DEFAULT NULL, `SysLogTag` varchar(60) DEFAULT NULL, `EventLogType` varchar(60) DEFAULT NULL, `GenericFileName` varchar(60) DEFAULT NULL, `SystemID` int(11) DEFAULT NULL, `ProcessID` varchar(60) NOT NULL DEFAULT '', `checksum` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) KEY_BLOCK_SIZE=4, KEY `ReceivedAt` (`ReceivedAt`), KEY `Facility` (`Facility`), KEY `Priority` (`Priority`), KEY `FromHost` (`FromHost`), KEY `DeviceReportedTime` (`DeviceReportedTime`), KEY `SysLogTag` (`SysLogTag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `SystemEventsHost2` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CustomerID` bigint(20) DEFAULT NULL, `ReceivedAt` datetime DEFAULT NULL, `DeviceReportedTime` datetime DEFAULT NULL, `Facility` smallint(6) DEFAULT NULL, `Priority` smallint(6) DEFAULT NULL, `FromHost` varchar(60) DEFAULT NULL, `Message` text, `NTSeverity` int(11) DEFAULT NULL, `Importance` int(11) DEFAULT NULL, `EventSource` varchar(60) DEFAULT NULL, `EventUser` varchar(60) DEFAULT NULL, `EventCategory` int(11) DEFAULT NULL, `EventID` int(11) DEFAULT NULL, `EventBinaryData` text, `MaxAvailable` int(11) DEFAULT NULL, `CurrUsage` int(11) DEFAULT NULL, `MinUsage` int(11) DEFAULT NULL, `MaxUsage` int(11) DEFAULT NULL, `InfoUnitID` int(11) DEFAULT NULL, `SysLogTag` varchar(60) DEFAULT NULL, `EventLogType` varchar(60) DEFAULT NULL, `GenericFileName` varchar(60) DEFAULT NULL, `SystemID` int(11) DEFAULT NULL, `ProcessID` varchar(60) NOT NULL DEFAULT '', `checksum` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) KEY_BLOCK_SIZE=4, KEY `ReceivedAt` (`ReceivedAt`), KEY `Facility` (`Facility`), KEY `Priority` (`Priority`), KEY `FromHost` (`FromHost`), KEY `DeviceReportedTime` (`DeviceReportedTime`), KEY `SysLogTag` (`SysLogTag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=1 ;
Here my loganalyzer source config snippet
$CFG['Sources']['Host1']['ID'] = 'Host1'; $CFG['Sources']['Host1']['Name'] = 'Host1'; $CFG['Sources']['Host1']['ViewID'] = 'SYSLOG'; $CFG['Sources']['Host1']['SourceType'] = SOURCE_DB; $CFG['Sources']['Host1']['DBTableType'] = 'monitorware'; $CFG['Sources']['Host1']['DBType'] = DB_MYSQL; $CFG['Sources']['Host1']['DBServer'] = 'localhost'; $CFG['Sources']['Host1']['DBName'] = 'Syslog'; $CFG['Sources']['Host1']['DBUser'] = 'rsyslog'; $CFG['Sources']['Host1']['DBPassword'] = 'mypassword'; $CFG['Sources']['Host1']['DBTableName'] = 'SystemEventsHost1'; $CFG['Sources']['Host1']['DBEnableRowCounting'] = false;
$CFG['Sources']['Host2']['ID'] = 'Host2'; $CFG['Sources']['Host2']['Name'] = 'Host2'; $CFG['Sources']['Host2']['ViewID'] = 'SYSLOG'; $CFG['Sources']['Host2']['SourceType'] = SOURCE_DB; $CFG['Sources']['Host2']['DBTableType'] = 'monitorware'; $CFG['Sources']['Host2']['DBType'] = DB_MYSQL; $CFG['Sources']['Host2']['DBServer'] = 'localhost'; $CFG['Sources']['Host2']['DBName'] = 'Syslog'; $CFG['Sources']['Host2']['DBUser'] = 'rsyslog'; $CFG['Sources']['Host2']['DBPassword'] = 'mypassword'; $CFG['Sources']['Host2']['DBTableName'] = 'SystemEventsHost2'; $CFG['Sources']['Host2']['DBEnableRowCounting'] = false;
If you’re paranoid you can modify your ubuntu 14.04 apparmor profile also, just add these lines to /etc/apparmor.d/usr.sbin.rsyslogd
and enforce rsyslog’s apparmor profile
# Add these for mysql support /etc/mysql/my.cnf r, /etc/mysql/conf.d/ r, /etc/mysql/conf.d/** r, /{,var/}run/mysqld/mysqld.sock rw, /usr/share/mysql/charsets/Index.xml r,
Have fun!