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

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!

freeradius anti bruteforce lockout via mysql

hi there

here my version of an user lockout via mysql for freeradius

Create a table like:

CREATE TABLE IF NOT EXISTS `failed` (
 `stationid` text NOT NULL,
 `nasip` text NOT NULL,
 `username` text NOT NULL,
 `authdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
vi /etc/freeradius/policy.conf
lockout_check {
 update control {
 Tmp-Integer-0 := "%{sql:SELECT COUNT(*) FROM failed where username='%{User-Name}' AND authdate>=DATE_SUB(NOW(), INTERVAL 15 MINUTE)}"
 }
 if (control:Tmp-Integer-0 > 5) {
 reject
 }
 }
 lockout_incr {
 update control {
 Tmp-Integer-0 := "%{sql:INSERT INTO failed (stationid,nasip,username,authdate) VALUES ('%{Calling-Station-Id}','%{NAS-IP-Address}','%{User-Name}', NOW())}"
 Tmp-Integer-1 := "%{sql:DELETE FROM failed WHERE authdate<=DATE_SUB(NOW(), INTERVAL 30 MINUTE)}"
 }
 }
vi /etc/freeradius/sites-enabled/default
authorize {
 lockout_check
 .....
 }
post-auth {
 Post-Auth-Type REJECT {
 lockout_incr
 }
 .....
 }

Have fun!

sogo auth with mysql and mysql(view) against ispconfig database

hi

fist step let mysql listen on: 0.0.0.0
therefore verify your /etc/mysql/my.cnf

cat /etc/mysql/my.cnf | grep bind
#bind-address		= 127.0.0.1

(should be done @ispconfig install)

create a mysql database and a user and grant all privileges to that user

CREATE USER 'sogo'@'1.1.1.1' IDENTIFIED BY  '***';
GRANT USAGE ON * . * TO  'sogo'@'1.1.1.1' IDENTIFIED BY  '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS  `sogo` ;
GRANT ALL PRIVILEGES ON  `sogo` . * TO  'sogo'@'1.1.1.1;

use mysql database as root and create special view

use sogo;
CREATE VIEW sogo_auth_view AS
SELECT  email AS c_uid,
             email AS c_name,
             password AS c_password,
             name AS c_cn,
             email AS mail
FROM dbispconfig.mail_user
WHERE disableimap='n' AND postfix ='y';

in your sogo config set the following lines:

<key>userPasswordAlgorithm</key>
<string>crypt</string>
<key>viewURL</key>
<string>mysql://sogo:yourpass@1.1.1.2:3306/sogo/sogo_auth_view</string>

Have Fun!

high cpu load due to leap second

hi

since july 1st 00:00 2012 I’ve seen high cpu load due to some java app. In my case it was the adaptec storage agent.

strace -p

showed me some problem with the realtime clock

futex(0xFUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME,ETIMEDOUT (Connection timed out)
Clock: inserting leap second 23:59:60 UTC

dmesg showed me this output

you don’t have to restart any serivce
just run the following:

/etc/init.d/ntp stop
date `date +"%m%d%H%M%C%y.%S"`
/etc/init.d/ntp start

so due to an leap second all machines running adaptec storage_agent got high cpu load :-(

this guy reportet the bug too:
http://blog.wpkg.org … une-1-july-2012-fix/

update:
seen this bug with mysql and bind9 too

simple sogo backup in perl with mysql

Hi

Need to export(backup) your sogo users contacts calendar etc?

Here some simple approach to get this done.

#!/usr/bin/perl
use warnings;
use DBI;

my $db ="databasename";
my $user = "username";
my $pass = "mypassword";
my $host = "localhost";
my $query = "SELECT mail FROM sogo_users";
my $bkppath = "/path/to/backup";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

my $dbh = DBI->connect("DBI:mysql:$db", "$user", "$pass")
	or die "Could not connect to database: " . DBI->errstr;

my $sth = $dbh->prepare("$query")
	or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute()
	or die "Couldn't execute statement: " . $sth->errstr;

while ($data = $sth->fetchrow_array()) {
	my $mail = $data;
	my $bkp = `/usr/sbin/sogo-tool backup $bkppath $mail > /dev/null`;
	sleep 2;
	my $mv = `/bin/mv $bkppath/$mail $bkppath/$mail.$wday`;
	}

$sth->finish;
$dbh->disconnect;

Have fun!