Stage one – install php-syslog-ng
Install syslog-ng then download and extract the web console:
apt-get install syslog-ng ttf-mscorefonts-installer
wget ftp://ftp.uwsg.indiana.edu/linux/gentoo/distfiles/php-syslog-ng-2.9.8m.tar.gz tar -xvf php-syslog-ng-2.9.8m.tar.gz
Create folder called phpsyslog then extract and copy extracted folders (scripts html upgrades) to that folder.
Prepare MySQL user and db:
CREATE DATABASE syslog; CREATE USER 'phpsyslogng'@'localhost' IDENTIFIED BY 'password'; grant all privileges on phpsyslogng.* to phpsyslogng@localhost with grant option; FLUSH TABLES; exit
Now open: html://localhost/phpsyslog/html/install:
apply permissions
chown -R web.web ./phpsyslog/*
and refresh page – all should now be green, click next:
on the next page enter your mysql bits:
For me the installation fails at this stage. My workaround is to edit the dbsetup.sql file (in phpsyslog-ng/html/install/sql folder) so it looks like this:
CREATE TABLE logs ( id bigint unsigned NOT NULL AUTO_INCREMENT, host varchar(128) 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 unsigned NOT NULL default '0', counter int(11) NOT NULL default '1', fo datetime default NULL, lo datetime default NULL, PRIMARY KEY (id), KEY datetime (datetime), KEY sequence (seq), KEY priority (priority), KEY facility (facility), KEY program (program), KEY host (host) ); CREATE TABLE users ( username varchar(32) default NULL, pwhash char(40) default NULL, sessionid char(32) default NULL, exptime datetime default NULL, PRIMARY KEY (username) ); CREATE TABLE search_cache ( tablename varchar(32) DEFAULT NULL, type ENUM('HOST','FACILITY','PROGRAM','LPD'), value varchar(128) DEFAULT NULL, updatetime datetime DEFAULT NULL, INDEX type_name (type, tablename) ); CREATE TABLE user_access ( username varchar(32) DEFAULT NULL, actionname varchar(32) DEFAULT NULL, access ENUM('TRUE','FALSE'), INDEX user_action (username, actionname) ); INSERT INTO user_access VALUES ('admin','add_user','TRUE'),('admin','edit_user','TRUE'), ('admin','reload_cache','TRUE'),('admin','edit_acl','TRUE'),('admin','add_server','TRUE'), ('admin','chg_auth','TRUE'),('admin','del_server','TRUE'); CREATE TABLE actions ( actionname varchar(32) NOT NULL, actiondescr varchar(64) DEFAULT NULL, defaultaccess ENUM('TRUE','FALSE'), PRIMARY KEY (actionname) ); -- -- Table structure for table cemdb -- CREATE TABLE cemdb ( id int(5) unsigned NOT NULL auto_increment, name varchar(128) NOT NULL default '', message text, explanation text, action text, datetime datetime default NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) COMMENT='Cisco Error Message Database'; INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('add_user', 'Add users', 'TRUE'); INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('edit_user', 'Edit users (delete and change password)', 'TRUE'); INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('reload_cache', 'Reload search cache', 'TRUE'); INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('edit_acl', 'Edit access control settings', 'TRUE');
Then go step back, fill in the SQL bits again and click Next again and it should go through to:
Confirm the site URL etc.:
Next import CEMDB data ito the syslog DB:
Wait for it to finish then edit the config.php file: (in your phpsyslog-ng folder in html/config folder) and make sure that all parameters are set as you want them to be:
Do not forget to remove the installation folder!
When everything is done you should see a login prompt when you got to your set URL:
Right now there is no data being logged so you will not see much when you login.
Next stage is to setup syslog-ng to log data to created MySQL DB
Open /etc/syslog-ng/syslog-ng.conf add or uncomment this bit:
# move logs from /var/log/syslog to MySQL destination d_mysql { pipe("/var/log/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, datetime, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PROGRAM', '$MSG');\n") template-escape(yes)); }; log { source(s_src); destination(d_mysql); };
that will create the MySQL destination and redirect all sources (s_src) to MySQL
now we need to create the /var/log/mysql.pipe
in /etc/syslog-ng folder create file called syslog2mysql.sh then paste this:
#!/bin/bash if [ ! -e /var/log/mysql.pipe ] then mkfifo /var/log/mysql.pipe fi while [ -e /var/log/mysql.pipe ] do mysql -u USERNAME --password=PASSWORD DBNAME< /var/log/mysql.pipe >/dev/null
where:
USERNAME – is your DB username
PASSWORD – is your DB password
DBNAME – is your DB name
now make the script executable:
chmod 755 /etc/syslog-ng/syslog2mysql.sh
restart syslog-ng:
service syslog-ng restart
fire up the script:
/etc/syslog-ng/syslog2myslq.sh &
and login to your php-syslog-ng console and you should see something like that:
Now create a service to start this when system boots and start the service:
service syslog2mysql
13 comments
Hey, great tutorial. However, I am getting stuck at the Next stage is to setup syslog-ng to log data to created MySQL DB’ part. Here is what I get when I add the config you have listed:
rtroiano@nc6230:/etc/syslog-ng$ sudo service syslog-ng restart
* Stopping system logging syslog-ng [ OK ]
* Starting system logging syslog-ng Error parsing affile, syntax error, unexpected LL_IDENTIFIER, expecting ‘)’ in /etc/syslog-ng/syslog-ng.conf at line 162, column 292:
template(“INSERT INTO logs
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
syslog-ng documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
mailing list: https://lists.balabit.hu/mailman/listinfo/syslog-ng
[fail]
I have tried all sorts of things to properly close the template statement in the config, but no luck so far. Any ideas? I am pretty sure its going to end up being something really stupid on my end. If you could email me, that would be awesome. Thank you!
Hi Richie086,
Can you upload somewhere your /etc/syslog-ng/syslog-ng.conf file and I’ll have a look.
http://paste.ubuntu.com/13207882/
thanks for the quick reply!
btw, here is the output of sudo service syslog-ng restart with your configs
http://paste.ubuntu.com/13208236/
thanks again for the quick reply.
Richie
OK try this – replace lines 160-164 in your /etc/syslog-ng/syslog-ng.conf with this:
destination d_mysql {
pipe(“/var/log/mysql.pipe”
template(“INSERT INTO logs
(host, facility, priority, level, tag, datetime, program, msg)
VALUES ( ‘$HOST’, ‘$FACILITY’, ‘$PRIORITY’, ‘$LEVEL’, ‘$TAG’, ‘$YEAR-$M$’);\n”) template-escape(yes));
};
Hmm, still the same sort of issue with the service not being able to start
Here is the new config with your changes made
http://paste.ubuntu.com/13210100/
Here is the error output
http://paste.ubuntu.com/13210321/
One thing I was thinking of is, are we using the same version of syslog-ng? At the top of the config file it says I am running v3.5.
I tried reformatting your suggested updated config and no matter what it always points to some part of the template(“INSERT INTO logs statement not closing or ending properly. With the config you posted above, can you sucessfully restart syslog-ng without errors? If I need to downgrade the version of syslog-ng I have installed I can do that. I am trying to set php-syslog-ng up for a testing network I have at home, so I can change package versions if that will make the config work.
Thanks again!
OK, I copied and pasted your config onto my test box made some changes and that config works for me:
http://pastebin.com/54iuNK8L
Script is incorrect to fix it use this:
#!/bin/bash
if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u root –password=predator1 syslog/dev/null
done
exit 0
And here is syntax error
Linux Debian Jessie:
ERROR 1064 (42000) at line 19: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘msg)
VALUES ( ‘debian’, ‘authpriv’, ‘info’, ‘info’, ’56’, ‘2016-04-17 17:50:18′,’ at line 1
Next error is in:
/etc/syslog-ng/syslog2myslq.sh &
replace it
on /etc/syslog-ng/syslog2mysql.sh &
And many more errors in this tutorial… But working for me after correct many things.
P.s
Guide is very inaccurate ;/
Well the script you have posted is in fact incorrect and to be precise this line makes absolutely no sense –> “mysql -u root –password=predator1 syslog/dev/null”.
I realize that instructions posted above may not work for everybody but consider that this software (PHP-SYSLOG-NG) is end of life.
Also in this blog I assume that whoever reads my scribles have some linux knowledge – they are not intended for linux newbs.
I am glad you have got it running in the end.
Yep, you are right now im using loganalyzer, because is easier to use it and configurate. PHP-SYSLOG-NG have to many problems with datebase ;/
Linux debian jessie instruction:
To create the /var/log/mysql.pipe we need command (its fix this error” WARNING: you are using the pipe driver, underlying file is not a FIFO, it should be used by file(); filename=’/var/log/mysql.pipe'”) :
mkfifo /var/log/mysql.pipe
If somebody have problem just create database users:
For example:
mysql -u root -p
SET PASSWORD FOR syslogfeeder@localhost = PASSWORD ('syslogfeederpassword');
SET PASSWORD FOR syslogadmin@localhost = PASSWORD ('syslogadminpassword');
Its comand for replace this service syslog2mysql because not working…
crontab -e -u root
And add for reboot
@reboot root /etc/syslog-ng/syslog2mysql.sh >> /var/log/mysql.log 2>&1
Correct script:
#!/bin/bash
if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do mysql -u syslogfeeder --password=syslogfeederpassword syslog /dev/null
done
Its working perfect for me now 😉
below given link is dead.. would you please upload the php-syslog-ng
ftp://ftp.uwsg.indiana.edu/linux/gentoo/distfiles/php-syslog-ng-2.9.8m.tar.gz
That product is now dead – I suggest using something else for example: http://loganalyzer.adiscon.com/