Home > World Of ICT > Syslog-ng base on Mysql – solusi untuk log system tersentral

Syslog-ng base on Mysql – solusi untuk log system tersentral


Kejadian hacking beberapa waktu lalu cukup membuat anak-anak kerepotan dimana sylog local diserver banyak dihapus oleh pelaku jadi repot untuk tracking transaksi pada waktu tertentu, oleh karena itu baru niat banget untuk membangun syslog server tersentral (Dodol Ahhh) untuk memudahkan pemantauan, searching searching akhirnya dipilihlah syslog-ng karena sudah support forward data ke Mysql agar mudah diolah.

unila-log-gtw# cd /usr/ports/sysutils/syslog-ng
unila-inherent-gtw# make && make install
===>   syslog-ng-1.6.12_1 depends on file: /usr/local/bin/libnet11-config - found
===>  Configuring for syslog-ng-1.6.12_1
checking for a BSD-compatible install... /usr/bin/install -c -o root -g wheel
checking whether build environment is sane... ^C===>  Script "configure" failed unexpectedly.
Please report the problem to lme@FreeBSD.org [maintainer] and attach the
"/usr/ports/sysutils/syslog-ng/work/syslog-ng-1.6.12/config.log" including
the output of the failure of your make command. Also, it might be a good idea
to provide an overview of all packages installed on your system (e.g. an `ls
/var/db/pkg`).

unila-log-gtw#

OK sampai disini tinggal menunggu proses instalasi selesai dan akan menghasilkan library dan file configurasi , untuk file konfigurasi tersimpan pada direktori

unila-log-gt# ll /usr/local/etc/syslog-ng/
total 24
-r--r--r--    1 root     wheel        6022 Jul 27 12:39 syslog-ng.conf
-r--r--r--    1 root     wheel        5564 Jul 27 10:46 syslog-ng.conf.sample
unila-log-gtw#

Buat file syslog-ng dengan perintah berikut

unila-log-gtw# mkfifo /var/log/mysql.pipe

Selanjutnya buat database mysql ;

unila-inherent-gt# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> create database syslog;
Query OK, 1 row affected (0.00 sec)

mysql> use syslog;
Database changed
mysql> CREATE TABLE logs ( host varchar(32) default NULL, facility varchar(10) default NULL, priority varchar(10) default NULL, level varchar(10) default NULL, tag varchar(10) default NULL, datetime datetime default NULL, program varchar(15) default NULL, msg text, seq bigint(20) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY program (program), KEY datetime (datetime), KEY priority (priority), KEY facility (facility) ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql>


Setelah selesai membuat database selanjutnya buat file konfigurasi syslog-ng agar support mysql , contoh disaya sbb;

#
# This sample configuration file is essentially equilivent to the stock
# FreeBSD /etc/syslog.conf file.
#
# Additional changes for network Syslog capability
#
# options
#
options { long_hostnames(off);
	  sync(0);
	  use_dns(yes);
	  use_fqdn(no); };

#
# sources
#
source src { unix-dgram("/var/run/log");
             unix-dgram("/var/run/logpriv" perm(0600));
             internal(); file("/dev/klog"); };

source netsrc { udp(ip("0.0.0.0") port(514));
                tcp(ip("0.0.0.0") port(514)); };

#
# destinations
#
destination messages { file("/var/log/messages"); };
destination security { file("/var/log/security"); };
destination authlog { file("/var/log/auth.log"); };
destination maillog { file("/var/log/maillog"); };
destination lpd-errs { file("/var/log/lpd-errs"); };
destination xferlog { file("/var/log/xferlog"); };
destination cron { file("/var/log/cron"); };
destination debuglog { file("/var/log/debug.log"); };
destination consolelog { file("/var/log/console.log"); };
destination all { file("/var/log/all.log"); };
destination newscrit { file("/var/log/news/news.crit"); };
destination newserr { file("/var/log/news/news.err"); };
destination newsnotice { file("/var/log/news/news.notice"); };
destination slip { file("/var/log/slip.log"); };
destination ppp { file("/var/log/ppp.log"); };
destination console { file("/dev/console"); };
destination allusers { usertty("*"); };
#destination loghost { udp("loghost" port(514)); };
# CISCO Destinations...
destination netlog { file("/var/log/network/$HOST/$YEAR$MONTH$DAY.log" owner(root) group(wheel) perm(0644) create_dirs(yes)); };

destination netsql
                {
                program("/usr/local/bin/mysql --user=root --password=xxxx  syslog < /var/log/mysql.pipe");
                pipe ("/var/log/mysql.pipe"
                template ("INSERT INTO syslog.logs (host, facility, priority, level, tag, datetime, program, msg) VALUES ('$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$ISODATE', '$PROGRAM', '$MESSAGE' );\n")
                template_escape(yes));
                };

#
# log facility filters
#
filter f_auth { facility(auth); };
filter f_authpriv { facility(authpriv); };
filter f_not_authpriv { not facility(authpriv); };
filter f_console { facility(console); };
filter f_cron { facility(cron); };
filter f_daemon { facility(daemon); };
filter f_ftp { facility(ftp); };
filter f_kern { facility(kern); };
filter f_lpr { facility(lpr); };
filter f_mail { facility(mail); };
filter f_news { facility(news); };
filter f_security { facility(security); };
filter f_user { facility(user); };
filter f_uucp { facility(uucp); };
filter f_local0 { facility(local0); };
filter f_local1 { facility(local1); };
filter f_local2 { facility(local2); };
filter f_local3 { facility(local3); };
filter f_local4 { facility(local4); };
filter f_local5 { facility(local5); };
filter f_local6 { facility(local6); };
filter f_local7 { facility(local7); };

#
# log level filters
#
filter f_emerg { level(emerg); };
filter f_alert { level(alert..emerg); };
filter f_crit { level(crit..emerg); };
filter f_err { level(err..emerg); };
filter f_warning { level(warning..emerg); };
filter f_notice { level(notice..emerg); };
filter f_info { level(info..emerg); };
filter f_debug { level(debug..emerg); };
filter f_is_debug { level(debug); };

#
# program filters
#
filter f_ppp { program("ppp"); };
filter f_slip { program("startslip"); };

#
# host filters
#

# CISCO Filters
filter f_netswitch001 {host("10.1.5.1"); };
filter f_netswitch002 {host("10.1.5.2"); };
filter f_netswitch003 {host("10.1.5.3"); };
filter f_netswitch004 {host("10.1.5.4"); };
filter f_netswitch005 {host("172.16.4.1"); };
filter f_netrouter001 {host("10.1.5.9"); };
filter f_netrouter002 {host("172.16.4.2"); };
filter f_netserver001 {host("server1.example.com"); };
filter f_netserver002 {host("server2.example.com"); };
#
# *.err;kern.warning;auth.notice;mail.crit		/dev/console
#
log { source(src); filter(f_err); destination(console); };
log { source(src); filter(f_kern); filter(f_warning); destination(console); };
log { source(src); filter(f_auth); filter(f_notice); destination(console); };
log { source(src); filter(f_mail); filter(f_crit); destination(console); };

#
# *.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err	/var/log/messages
#
log { source(src); filter(f_notice); filter(f_not_authpriv); destination(messages); };
log { source(src); filter(f_kern); filter(f_debug); destination(messages); };
log { source(src); filter(f_lpr); filter(f_info); destination(messages); };
log { source(src); filter(f_mail); filter(f_crit); destination(messages); };
log { source(src); filter(f_news); filter(f_err); destination(messages); };

#
# security.*						/var/log/security
#
log { source(src); filter(f_security); destination(security); };

#
# auth.info;authpriv.info				/var/log/auth.log
log { source(src); filter(f_auth); filter(f_info); destination(authlog); };
log { source(src); filter(f_authpriv); filter(f_info); destination(authlog); };

#
# mail.info						/var/log/maillog
#
log { source(src); filter(f_mail); filter(f_info); destination(maillog); };

#
# lpr.info						/var/log/lpd-errs
#
log { source(src); filter(f_lpr); filter(f_info); destination(lpd-errs); };

#
# ftp.info						/var/log/xferlog
#
log { source(src); filter(f_ftp); filter(f_info); destination(xferlog); }; 

#
# cron.*						/var/log/cron
#
log { source(src); filter(f_cron); destination(cron); };

log { source(src); destination(all); };

#
# *.=debug						/var/log/debug.log
#
log { source(src); filter(f_is_debug); destination(debuglog); };

#
# *.emerg						*
#
log { source(src); filter(f_emerg); destination(allusers); };

#
# !startslip
# *.*							/var/log/slip.log
#
log { source(src); filter(f_slip); destination(slip); };

#
# !ppp
# *.*							/var/log/ppp.log
#
log { source(src); filter(f_ppp); destination(ppp); };

#
# CISCO Program Filters
#
log { source(netsrc); destination(netlog); };
log { source(netsrc); destination(netsql); };

Bagian terpenting adalah 2 baris terakhir yang berfungsi source netsrc dari semua subnet akan diarahkan ke netsql;

Sebelum menjalankan service syslog-ng ubah parameter startup mysql dan syslog-ng dengan urutan start awal adalah mysql selanjutnya syslog-ng dengan cara sbb

unila-log-gtw# cat /etc/rc.conf | grep syslog
syslogd_enable="NO"
syslog_ng_enable="YES"
syslogd_program="/usr/local/sbin/syslog-ng"
syslogd_flags=""
unila-inherent-gtw#

unila-log-gtw# ll /usr/local/etc/rc.d/
-r-xr-xr-x    1 root     wheel        2216 Jul 27 10:50 010.mysql-server
-r-xr-xr-x    1 root     wheel         953 Jul 27 10:46 020.syslog-ng
Finally jalankan service syslog-ng
unila-inherent-gtw# /usr/local/etc/rc.d/020.syslog-ng start
syslog_ng already running? (pid=925).
unila-inherent-gtw#

OK sampai disini beres, tinggal disetiap server arahkan lognya ke server syslog ini jika berhasil maka database akan merecord setiap ada log yg dikirim ke system,

Contoh SBB.

Categories: World Of ICT
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: