Tag: compression

rsyslog own mysql table per host with loganalyzer innodb and innodb-compression

paranoids

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!