Skip to content

Membuat Syslog server dengan mysql

June 12, 2008

Alhamdulillah, akhirnya bisa buat log server, aman buat sementara nih, maklum lg dikejar-kejar si bos.
setelah googling saya nemu artikel yg bagus,
ada edit sedikit dari saya, berikut caranya :

1. Install syslog-ng, phpmyadmin, dan mysql-server

apt-get install syslog-ng syslog-summary

apt-get install phpmyadmin

apt-get install mysql-server

2. tambahkan file di /etc/syslog-ng/syslog-ng.

vi /etc/syslog-ng/syslog-ng

source net { udp(); };
destination d_mysql {
pipe(“/tmp/mysql.pipe”
template(“INSERT INTO logs (host, facility, priority, level, tag, date,
time, program, msg) VALUES ( ‘$HOST’, ‘$FACILITY’, ‘$PRIORITY’, ‘$LEVEL’,’$TAG’,
‘$YEAR-$MONTH-$DAY’, ‘$HOUR:$MIN:$SEC’, ‘$PROGRAM’, ‘$MSG’ );\n”) template-escape(yes));
};
log { source(net); destination(d_mysql); };

3. Restart syslog-ng

/etc/init.d/syslog-ng restart

4. buat fifo pipe untuk syslog-ng

mkfifo /tmp/mysql.pipe

5. Buat database syslogdb di mysql dengan perintah SQL berikut:

CREATE DATABASE syslogdb;
USE syslogdb;
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,
date date default NULL,
time time default NULL,
program varchar(15) default NULL,
msg text,
seq int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) TYPE=MyISAM;

(yg belum tau caranya, tinggal ketik http://localhost/phpmyadmin, setelah login, pilih database > SQL kemudian paste script diatas di kolom “Run SQL query/queries on server localhost”)

6. Buat script /etc/syslog-ng/rc.syslog-ng-to-myqsl

vi /etc/syslog-ng/rc.syslog-ng-to-myqsl

#!/bin/bash
if [ -e /tmp/mysql.pipe ];
then
while [ -e /tmp/mysql.pipe ]
do
mysql -u root –password=’bac2bas6′ syslogdb < /tmp/mysql.pipe
done
else
mkfifo /tmp/mysql.pipe;
fi;

7. chmod 750 /etc/syslog-ng/rc.syslog-ng-to-mysql

berfungsi agar hanya root yang bisa meng-eksekusi program bash ini.

8. restart syslog-ng

/etc/init.d/syslog-ng restart

9. jalankan program bash yang telah dibuat,

/etc/syslog-ng/rc.syslog-ng-to-myqsl
notes : masukkan command diiatas di /etc/rc.local agar bisa running setiap kali direboot

10. cek di phpmyadmin (via browser) untuk melihat report syslog.

setting di client

1. Router Cisco

conf t
!
logging <ip_address_server syslog-ng>
!
logging facility local7
!
logging trap informational <level_of_debugging>
!
logging on
!
end

kemudian simpan konfigurasi

2. Linux server

Tambahakan baris berikut di file /etc/syslog-ng/syslog-ng.conf disisi klien

#######

destination loghost {tcp(“<ip_address_server syslog>” port(514));};
log { source(s_all); destination(loghost); };

#######

Thanks buat Om Harijanto Pribadi (PT. data Utama Dinamika) dan Bung Abdul(PT.Sistelindo)

From → Linux

Leave a Comment

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: