Install and setup Syslog-ng with PHP-SYSLG-NG front end

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:

syslogng1

apply permissions

chown -R web.web ./phpsyslog/*

and refresh page – all should now be green, click next:

syslogng2

on the next page enter your mysql bits:

phpsyslog3For 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:
phpsyslog4Confirm the site URL etc.:

phpsyslog5

phpsyslog6 Next import CEMDB data ito the syslog DB:

phpsyslog7phpsyslog8Wait 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:

phpsyslog9aDo not forget to remove the installation folder!

When everything is done you should see a login prompt when you got to your set URL:

phpsyslog10Right 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:

phpsyslog11

Now create a service to start this when system boots and start the service:

service syslog2mysql

 

 

0

Leave a Reply

Your email address will not be published. Required fields are marked *

4 × 1 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

13 comments

  1. Richie086 says:
    08/11/2015 at 6:58 AM

    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!

    Reply
    • AlfPi says:
      08/11/2015 at 8:27 AM

      Hi Richie086,

      Can you upload somewhere your /etc/syslog-ng/syslog-ng.conf file and I’ll have a look.

      Reply
      • Richie086 says:
        09/11/2015 at 9:25 AM

        http://paste.ubuntu.com/13207882/

        thanks for the quick reply!

        Reply
      • Richie086 says:
        09/11/2015 at 11:08 AM

        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

        Reply
        • AlfPi says:
          09/11/2015 at 12:37 PM

          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));
          };

          Reply
          • Richie086 says:
            09/11/2015 at 6:56 PM

            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!

          • AlfPi says:
            10/11/2015 at 10:08 AM

            OK, I copied and pasted your config onto my test box made some changes and that config works for me:
            http://pastebin.com/54iuNK8L

  2. SlimShady says:
    17/04/2016 at 4:54 PM

    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 ;/

    Reply
    • AlfPi says:
      17/04/2016 at 6:02 PM

      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.

      Reply
      • SlimShady says:
        18/04/2016 at 3:54 PM

        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 ;/

        Reply
  3. SlimShady says:
    18/04/2016 at 6:25 PM

    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 😉

    Reply
  4. ahad says:
    20/08/2018 at 8:31 PM

    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

    Reply