[syslog-ng]NEW SOLUTION MYSQL INSERT
_/CaT\_
cat_roger22@hotmail.com
Thu, 3 Oct 2002 16:30:52 +0200
Hi *,
It is the code of a system which inserts syslog events into a Mysql DB
whith best performances in a more secure and stable way.
All the scripts are written in Perl, PAM(DBI,Sys::Syslog) and PERL are
required.
1) Inserdb.pl v.1.1 it's the daemon that verify and inserts
the data.
2) Allow_ip_table_db.pl v.1.1 This wizard inserts Authorized Ips that
are allowed to write on DB (It's important for ddos problems)
3) Load_ip_allows.txt the conf file that inserts authorized
Ips
4) Make_facility_level.sql creates tables facility and level
5) syslog_db_insert starts the service under RH 7.3
6) syslog-ng option
------------------------------------------------------------------------
------------------------------------------------------------
(1)
########################################################################
############
#!/usr/bin/perl
#
# SYSLOG_DB_INSERT
# inserdb.pl
# v.1.1
#
# Script to implement INSERT INTO DB MYSQL
#
# description: Utility for insert syslog events
#
# by _/CaT\_:
# Script Author:
# GDM & _/CaT\_ cat_roger22@hotmail.com
########################################################################
#############
$|=1;
use DBI;
#use strict;
use Sys::Syslog;
use POSIX qw(strftime);
sub log_syslog($$);
sub warn_log_exit($$);
sub error_log_exit($$);
&log_syslog('info'," Start $0 - Syslog-ng ");
$ENV{'DBI_DSN'}='xxxxxxx';
$ENV{'DBI_USER'}='xxxxxx';
$ENV{'DBI_PASS'}='xxxxxx';
$ENV{'DBI_TABLE_SYSLOG'}='xxxxx';
$ENV{'DBI_PIPE_SYSLOG'}='/path/xxxxx.pipe';
$ENV{'ERR_MSG'}='/path/err_DB_msg.log';
my $DB_SB='mysql';
###
### Debug mode = 1
###
my $DEBUG = 0;
my $test_dsn = $ENV{'DBI_DSN'} || die "DSN not set\n";
my $test_user = $ENV{'DBI_USER'} || die "User not set\n";
my $test_password = $ENV{'DBI_PASS'} || die "Password not set\n";
my $table = $ENV{'DBI_TABLE_SYSLOG'} || die "Table not set\n";
my $pipe = $ENV{'DBI_PIPE_SYSLOG'} || die "Pipe file not
set\n";
my $errmsg = $ENV{'ERR_MSG'} || die "File ERR_MSG not set\n";
my $DATA_SOURCE='dbi:mysql:'.$test_dsn.';mysql_compression=1';
my $dbh;
my $sth;
my $riga;
### normal-signals
### These are the signals which a program might normally
expect to encounter and which by default cause it to
### terminate. They are HUP, INT, PIPE and TERM.
###
### error-signals
### These signals usually indicate a serious problem with the
Perl interpreter or with your script. They are
### ABRT, BUS, EMT, FPE, ILL, QUIT, SEGV, SYS and TRAP.
###
### old-interface-signals
### These are the signals which were trapped by default by the
old sigtrap interface, they are ABRT, BUS, EMT,
### FPE, ILL, PIPE, QUIT, SEGV, SYS, TERM, and TRAP. If no
signals or signals lists are passed to sigtrap,
### this list is used.
###
local $SIG{HUP} = sub { &error_log_exit('SIGHUP',"Eseguito un kill -1")
}; local $SIG{INT} = sub { &error_log_exit('SIGINT',"Eseguito un kill
-2") }; local $SIG{QUIT} = sub { &error_log_exit('SIGQUIT',"Eseguito un
kill -3") }; local $SIG{TRAP} = sub {
&error_log_exit('SIGTRAP',"Eseguito un kill -5") }; local $SIG{KILL} =
sub { &error_log_exit('SIGKILL',"Eseguito un kill -9") }; local
$SIG{ABRT} = sub { &error_log_exit('SIGABRT',"Eseguito un kill -6") };
local $SIG{BUS} = sub { &error_log_exit('SIGBUS',"Eseguito un kill -7")
}; local $SIG{FPE} = sub { &error_log_exit('SIGFPE',"Eseguito un kill
-8") }; local $SIG{SEGV} = sub { &error_log_exit('SIGSEGV',"Eseguito un
kill -11") }; local $SIG{PIPE} = sub {
&error_log_exit('SIGPIPE',"Eseguito un kill -13") }; local $SIG{ALRM} =
sub { &error_log_exit('SIGALRM',"Eseguito un kill -14") }; local
$SIG{TERM} = sub { &error_log_exit('SIGTERM',"Eseguito un kill -15") };
local $SIG{CHLD} = sub { &error_log_exit('SIGCHLD',"Eseguito un kill
-17") }; local $SIG{STOP} = sub { &error_log_exit('SIGSTOP',"Eseguito un
kill -19") }; local $SIG{SYS} = sub { &error_log_exit('SIGSYS',"Eseguito
un kill -31") };
$dbh = DBI->connect(
$DATA_SOURCE,
$test_user,
$test_password,
{
RaiseError => 1,
PrintError => 2,
AutoCommit =>1
}
) or &error_log_exit('DB CONNECT',"$DBI::errstr");
&log_syslog('info','Connect to DB...Start');
#####################################################
### Load table facility
#####################################################
my $exe;
my $sth;
my $id;
my $query;
my $facility;
my %facility;
my $priority;
my $level;
my %level;
$query='SELECT * FROM facility';
$sth=$dbh->prepare($query) || &error_log_exit("$query",$dbh->errstr);
$exe=$sth->execute() || &error_log_exit('EXEC',$dbh->errstr);
eval {
while(($id,$facility) = $sth->fetchrow_array) {
$facility{$id}=$facility;
print STDOUT "FACILITY: $id -> $facility\n" if $DEBUG ge
1;
}
};
&error_log_exit('FETCH',$dbh->errstr) if $@;
####################################################
### Load table level
###################################################
$query='SELECT * FROM level';
$sth=$dbh->prepare($query) || &error_log_exit("$query",$dbh->errstr);
$exe=$sth->execute() || &error_log_exit('EXEC',$dbh->errstr);
eval {
while(($priority,$level) = $sth->fetchrow_array) {
$level{$priority}=$level;
print STDOUT "LEVEL: ".$priority.' -> '.$level."\n" if
$DEBUG ge 1;
}
};
&error_log_exit('FETCH',$dbh->errstr) if $@;
####################################################
### Load tables hosts
###################################################
if ( $DEBUG ge 1 ) {
my @tables; my $val;
@tables = map{ $_ =~ s/^.*\.//; $_ } $dbh->tables();
foreach $val (@tables) { print STDOUT "TABLE: $val\n"; }
}
####################################################
### Core Function
###################################################
open(FF,"$pipe") || &error_log_exit('PIPE_ERROR_1',"Error on read pipe
$pipe; $!");
while($riga=<FF>) {
my $date;
my $host;
my $facility;
my $level;
my $tag;
my $program;
my @msg=();
my $val;
my $time;
###
### CHECK SINTAX
### Syntax
### |<|$YEAR/$MONTH/$DAY $HOUR:$MIN:$SEC $HOST $FACILITY $LEVEL
$TAG $PROGRAM $MSG|>|
###
chomp($riga);
print STDOUT "BEFORE:$riga\n" if $DEBUG ge 1;
if ( $riga =~ /^\|\<\|(.+)\|\>\|$/) {
$riga=$1;
if ( $riga =~ /\|\>\|\|\<\|/ ) {
&warn_log_exit("SYNTAX_ERROR_1",$riga);
###
### Decomenntando no recupera piu le possibili
righe..
###
#next;
$riga=~ s/^(.+)\|\>\|\|\<\|.+$/$1/;
}
}
else {
&warn_log_exit("SYNTAX_ERROR_2",$riga);
next;
}
($date,$time,$host,$facility,$level,$tag,$program,@msg)=split(/\
/,$riga);
###
### forza la variabile program al primo campo del message
togliendo il pid
###
if ( $program !~ /\w/ ) { $program=$msg[0];
$program=~s/(.+)\[.+/$1/; }
###
### Create string (Pattern Substitution) - Conversione facility
e level in numero
###
foreach $val (keys %facility) {
if ( $facility eq $facility{$val} ) {
$facility=$val;
}
}
foreach $val (keys %level) {
if ( $level eq $level{$val} ) {
$level=$val;
}
}
print STDOUT "NEXT:$date $time $host $facility $level $tag
$program @msg\n" if $DEBUG ge 1;
###
### CHECK TABLES
### |
### if exists -> INSERT INTO ...
### else -> Scrive su file
my $sth="";
my $exe="";
my $query="";
###
### IMPORTANTE!!!
### Conversione IP in INDICE TABELLA
###
my $index=$host;
$index=~s/\./_/g; # converte ip in id
$query="SELECT id FROM main_allow_syslog WHERE id = \'$index\'
AND state = 1";
$sth=$dbh->prepare($query) || &warn_log_exit('PREPARE',"$query
$dbh->errstr");
$exe=$sth->execute() || &warn_log_exit('EXEC',$dbh->errstr);
my $numrows=$sth->rows;
print STDOUT "stato= $exe - righe=$numrows\n" if $DEBUG ge 1;
if ($numrows eq 1) {
###
### Inserisce nel DB
###
###
+----------+--------------+------+-----+---------------------+-------+
### | Field | Type | Null | Key | Default
| Extra |
###
+----------+--------------+------+-----+---------------------+-------+
### | date | datetime | | |
0000-00-00 00:00:00 | |
### | facility | int(2) | | | 0
| |
### | level | int(2) | | | 0
| |
### | tag | varchar(4) | | |
| |
### | program | varchar(64) | | |
| |
### | msg | varchar(255) | | |
| |
###
+----------+--------------+------+-----+---------------------+-------+
###
$query="INSERT INTO
$index(date,facility,level,tag,program,msg) VALUES (\'$date
$time\',\'$facility\',\'$level\',\'$tag\',\'$program\',\'@msg\')";
$dbh->do($query) ||
&warn_log_exit('INSERT_FAILED_1',$query);
print STDOUT "INSERT SUCCESS on $index: $query\n" if
$DEBUG ge 1;
}
else { &warn_log_exit('INSERT_FAILED_2',"QUERY=\"$query\":
RIGA=\"$riga\""); }
}
close(FF);
$dbh->disconnect() or &error_log_exit('DB_CLOSE',"$DBI::errstr");
log_syslog('info','Session to DB closed...');
#################################################
###
### SUB
###
#################################################
sub log_syslog($$) {
my ($tipo,$mess)=@_;
openlog($0, 'cons,pid', 'user');
syslog($tipo,$mess);
closelog();
};
sub warn_log_exit($$) {
local ($in,$err)=@_;
local $tme;
$time=strftime("%Y/%m/%d %H:%M:%S",localtime);
print STDOUT "$time WARN:$in:$err\n" if $DEBUG ge 1;
open(ERRMSG,">>$errmsg") || die "Error on WRITE on $errmsg:
$!\n";
print ERRMSG "$time WARN:$in:$err \n";
close(ERRMSG);
};
sub error_log_exit($$) {
local ($in,$err)=@_;
local $tme;
$time=strftime("%Y/%m/%d %H:%M:%S",localtime);
print STDOUT "$time ERROR:$in:$err\n" if $DEBUG ge 1;
open(ERRMSG,">>$errmsg") || die "Error on WRITE on $errmsg:
$!\n";
print ERRMSG "$time ERROR:$in:$err \n";
close(ERRMSG);
exit 1;
};
########################################################################
#############
#EOF
########################################################################
#############
(2)
########################################################################
############
#!/usr/bin/perl
#
# SYSLOG_DB_INSERT
# allow_ip_table_db.pl
# v.1.1
#
# Script to implement ALLOW TO INSERT INTO DB MYSQL
#
# description: Wizard for allow IP to write into DB
#
# by _/CaT\_:
# Script Author:
# GDM & _/CaT\_ cat_roger22@hotmail.com
########################################################################
##############
$|=1;
use DBI;
#use strict;
use Sys::Syslog;
use POSIX qw(strftime);
sub log_syslog($$);
sub warn_log_exit($$);
sub error_log_exit($$);
&log_syslog('info',"|--------- Start $0 ---------|");
$ENV{'DBI_DSN'}='xxxxxx';
$ENV{'DBI_USER'}='xxxxx';
$ENV{'DBI_PASS'}='xxxxx';
$ENV{'DBI_TABLE_SYSLOG'}='xxx'; $ENV{'ERR_MSG'}='/path/err_DB_msg.log';
my $DB_SB='mysql';
###
### Debug mode = 1
###
my $DEBUG = 1;
my $test_dsn = $ENV{'DBI_DSN'} || die "DSN not set\n";
my $test_user = $ENV{'DBI_USER'} || die "User not set\n";
my $test_password = $ENV{'DBI_PASS'} || die "Password not set\n";
my $table = $ENV{'DBI_TABLE_SYSLOG'} || die "Table not set\n";
my $errmsg = $ENV{'ERR_MSG'} || die "File ERR_MSG not set\n";
###############################################
###
### IMPORTANT!!!
### File load IP allowed to write into DB
###
###############################################
my $file_in='/path/xxxxxxxx.txt';
my $DATA_SOURCE='dbi:mysql:'.$test_dsn.';mysql_compression=1';
my $dbh;
my $sth;
my $riga;
my $BaseTable='main_allow_syslog';
$dbh = DBI->connect($DATA_SOURCE, $test_user, $test_password,
{RaiseError => 1,PrintError => 2,AutoCommit =>1}) or die "Can\'t
connect: $DBI::errstr\n";
&log_syslog('info','Connect to DB...Done');
##############################################
### Make base table
##############################################
###
###
+---------+--------------+------+-----+---------------------+-------+
### | Field | Type | Null | Key | Default |
Extra |
###
+---------+--------------+------+-----+---------------------+-------+
### | id | varchar(15) | | PRI | |
|
### | ip | varchar(15) | | | |
|
### | name | varchar(48) | | | |
|
### | state | int(1) | | | 0 |
|
### | c_time | datetime | | | 0000-00-00 00:00:00 |
|
### | comment | varchar(126) | YES | | NULL |
|
###
+---------+--------------+------+-----+---------------------+-------+
###
my @tables;
my $found=0;
my $query;
@tables = map{ $_ =~ s/^.*\.//; $_ } $dbh->tables();
while($table=<@tables> and $found == 0 ) { if ($table eq $BaseTable) {
$found=1; } }
if ( $found ne 1 ) {
$query="CREATE TABLE $BaseTable(
id varchar(15) PRIMARY KEY NOT NULL,
ip varchar(15) NOT NULL,
name varchar(48) NOT NULL,
state int(1) NOT NULL,
c_time DATETIME NOT NULL,
m_time DATETIME NOT NULL,
comment varchar(126)
)";
$dbh->do($query);
&warn_log_exit("CREATION TABLE","$query");
}
#####################################################
### Load tables
#####################################################
my $exe;
my $sth;
my $id;
my $priority;
my $riga;
my $ip;
my $state;
my $name;
my @comment=();
my $todo;
open(FF,"<$file_in") || &error_log_exit("FILE READ","$!");
while($riga=<FF>) {
chomp($riga);
($todo,$ip,$name,$state,@comment)=split(/\s+/,$riga);
###
### CHECK SYNTAX
###
next if ( $riga =~ /^#|^$/ ) ;
&error_log_exit('SYNTAX ERROR READ_1',"IP errato") if ( $ip !~
/^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/ ) ;
&error_log_exit('SYNTAX ERROR READ_2',"state errato") if (
$state !~ /0|1/ ) ;
&error_log_exit('SYNTAX ERROR READ_2',"Name errato") if ($name
!~ /\w/) ;
###
### NOTE!!!
###
### The coma -.- in the IP is traslate in Underscore - _ -
###
$iptraslate=$ip;
$iptraslate=~ s/\./_/g;
###
### make query for update $BaseTable
###
$ctime=strftime("%Y/%m/%d %H:%M:%S",localtime);
print STDOUT "IpTraslate -> $iptraslate date -> $ctime todo ->
$todo\n" if $DEBUG ge 1;
if ( $todo eq 'ADD' ) {
&ADD(*BaseTable,*iptraslate,*ip,*name,*state,*ctime,*comment); }
elsif ( $todo eq 'DISABLE' ) {
&DISABLE(*BaseTable,*iptraslate,*ip,*ctime); }
elsif ( $todo eq 'ENABLE' ) {
&ENABLE(*BaseTable,*iptraslate,*ip,*ctime); }
elsif ( $todo eq 'REMOVE' ) {
&REMOVE(*BaseTable,*iptraslate,*ip) }
}
close(FF);
$dbh->disconnect() or die "ERROR on close: $DBI::errstr\n";
log_syslog('info','Session to DB closed...');
#################################################
###
### SUB
###
#################################################
sub log_syslog($$) {
my ($tipo,$mess)=@_;
openlog($0, 'cons,pid', 'user');
syslog($tipo,$mess);
closelog();
};
sub warn_log_exit($$) {
my ($in,$err)=@_;
print STDOUT "WARN:$in:$err\n" if $DEBUG ge 1;
open(ERRMSG,">>$errmsg") || die "Error on WRITE on $errmsg:
$!\n";
print ERRMSG "WARN:$in:$err \n";
close(ERRMSG);
};
sub error_log_exit($$) {
my ($in,$err)=@_;
print STDOUT "ERROR:$in:$err\n" if $DEBUG ge 1;
open(ERRMSG,">>$errmsg") || die "Error on WRITE on $errmsg:
$!\n";
print ERRMSG "ERROR:$in:$err \n";
close(ERRMSG);
exit 1;
};
sub ADD($$$$$$) {
local
(*BaseTable,*iptraslate,*ip,*name,*state,*ctime,*comment)=@_;
$query="INSERT INTO $BaseTable
(id,ip,name,state,c_time,m_time,comment) VALUES
(\'$iptraslate\',\'$ip\',\'$name\',\'$state\',\'$ctime\',\'$ctime\',\'@c
omment\')";
print STDOUT "Query=$query\n";
$sth=$dbh->prepare($query) ||
&error_log_exit("$query",$dbh->errstr);
$exe=$sth->execute() || &error_log_exit('EXEC',$dbh->errstr);
#########################################################
### Make new Table for IP
#########################################################
###
###
+----------+--------------+------+-----+---------------------+-------+
### | Field | Type | Null | Key | Default
| Extra |
###
+----------+--------------+------+-----+---------------------+-------+
### | date | datetime | | | 0000-00-00
00:00:00 | |
### | facility | int(2) | | | 0
| |
### | level | int(2) | | | 0
| |
### | tag | varchar(4) | | |
| |
### | program | varchar(64) | | |
| |
### | msg | varchar(255) | | |
| |
###
+----------+--------------+------+-----+---------------------+-------+
###
$query="CREATE TABLE $iptraslate(
date DATETIME NOT NULL,
facility int(2) NOT NULL,
level int(2) NOT NULL,
tag varchar(4) NOT NULL,
program varchar(64) NOT NULL,
msg varchar(255) NOT NULL
)";
$dbh->do($query);
&warn_log_exit("CREATION TABLE $ip","$query");
};
sub DISABLE($$$$) {
local (*BaseTable,*iptraslate,*ip,*ctime)=@_;
$query="UPDATE $BaseTable SET state = \'0\' , m_time =
\'$ctime\' WHERE id = \'$iptraslate\' AND ip = \'$ip\'";
print STDOUT "Query=$query\n";
$sth=$dbh->prepare($query) ||
&error_log_exit("$query",$dbh->errstr);
$exe=$sth->execute() || &error_log_exit('EXEC',$dbh->errstr);
};
sub ENABLE($$$$) {
local (*BaseTable,*iptraslate,*ip,*ctime)=@_;
$query="UPDATE $BaseTable SET state = \'1\' , m_time =
\'$ctime\' WHERE id = \'$iptraslate\' AND ip = \'$ip\'";
print STDOUT "Query=$query\n";
$sth=$dbh->prepare($query) ||
&error_log_exit("$query",$dbh->errstr);
$exe=$sth->execute() || &error_log_exit('EXEC',$dbh->errstr);
};
########################################################################
############
#EOF
########################################################################
############
(3)
########################################################################
##############
####
#### File for wizard the IP allowed to write into DB
####
#### ADD -> Aggiunge tabelle
#### DISABLE -> Disabilita la scrittura sulla tabella
#### ENABLE -> riabilita la tabella
####
#### IMPORTANT - after the insert clean the entry or comment
####
#### Exaple:
####
+-----------+-----------------+-----------+-----------+-----------+
#### | Func | IP | Hostname
| State | Comment |
####
+-----------+-----------------+-----------+-----------+-----------+
#### |ADD |192.168.69.254 |pippo |1
|honeypot |
####
+-----------+-----------------+-----------+-----------+-----------+
ADD 192.168.69.254 pippo 1 honeypot
#ADD 192.168.70.57 pippone 1 WS di GDM
########################################################################
################
#EOF
########################################################################
################
(4)
########################################################################
############
# SQL script for load the tables: facility and level
########################################################################
############
drop table facility;
drop table level;
create table facility
(
id int(2),
facility varchar(16)
);
create table level
(
priority int(2),
level varchar(16)
);
insert into facility(id,facility) values ('0','kern');
insert into facility(id,facility) values ('1','user');
insert into facility(id,facility) values ('2','mail');
insert into facility(id,facility) values ('3','daemon');
insert into facility(id,facility) values ('4','auth');
insert into facility(id,facility) values ('5','syslog');
insert into facility(id,facility) values ('6','lpr');
insert into facility(id,facility) values ('7','news');
insert into facility(id,facility) values ('8','uucp');
insert into facility(id,facility) values ('9','cron');
insert into facility(id,facility) values ('10','local0');
insert into facility(id,facility) values ('11','local1');
insert into facility(id,facility) values ('12','local2');
insert into facility(id,facility) values ('13','local3');
insert into facility(id,facility) values ('14','local4');
insert into facility(id,facility) values ('15','local5');
insert into facility(id,facility) values ('16','local6');
insert into facility(id,facility) values ('17','local7');
insert into level(priority,level) values ('0','emerg');
insert into level(priority,level) values ('1','alert');
insert into level(priority,level) values ('2','crit');
insert into level(priority,level) values ('3','err');
insert into level(priority,level) values ('4','warning');
insert into level(priority,level) values ('5','notice');
insert into level(priority,level) values ('6','info');
insert into level(priority,level) values ('7','debug');
insert into level(priority,level) values ('8','none');
commit;
########################################################################
##############
#EOF
########################################################################
##############
(5)
########################################################################
########
#!/bin/bash
#
# Program: syslog_db_insert init script
#
# Description:
#
# chkconfig: 2345 12 88
# Description:
########################################################################
#########
########################################################################
########
# configuration
#
INIT_DB_PROG="/path/insertdb.pl" # Full path to daemon
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/script
INIT_DB=`basename "$INIT_DB_PROG"`
# Source Redhat function library.
#
. /etc/rc.d/init.d/functions
[ -x $INIT_DB_PROG ] || exit 0
RETVAL=0
umask 077
ulimit -c 0
# See how we were called.
case "$1" in
start)
echo -n "Starting $INIT_DB: "
nohup $INIT_DB_PROG &
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch "/var/lock/subsys/${INIT_DB}"
;;
stop)
echo -n "Stopping $INIT_DB: "
killproc $INIT_DB_PROG
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f "/var/lock/subsys/${INIT_DB}"
;;
status)
status $INIT_DB_PROG
RETVAL=$?
;;
restart|reload)
$0 stop
$0 start
RETVAL=$?
;;
*)
echo "Usage: $0 {start|stop|status|restart|reload}"
exit 1
esac
exit $RETVAL
######################################################################
#EOF
######################################################################
(6)
########################################################################
####
#LOG TO MYSQL
destination d_mysql {
pipe("/dev/mysql.pipe"
template("|<|$YEAR/$MONTH/$DAY $HOUR:$MIN:$SEC $HOST $FACILITY
$LEVEL $TAG $PROGRAM $MSG|>|\n"));
};
########################################################################
####
Cheers
_/CaT\_