NGINX and Syslog-NG

For the test I use Syslog-NG version 3.3 and Nginx 1.6 as setup in my previous post.

From version 1.7 Nginx has built in support for sysloging but since my version is 1.6 I use this technique found somewhere on the net with my modifications.

Setup is simple just add the following lines to the syslog-ng.conf file:

source nginx_error_var { program("tail -F -n0 /var/log/nginx/error.log" program_override(nginx)); };
log {
 source(nginx_error_var);
 destination(d_mysql);
 };

and restart syslog-ng (service syslog-ng restart)

The only downside is that some lines might be missed but it works great and does what I want it to do.

 

 

 

Cisco ASA – setup logging to Syslog-ng

Assuming that SyslogNG is configured and running then the setup is quick and easy:

Cisco ASA config:

1. Enable logging:

logging enable 
 logging timestamp

2. Send messages to our sylog server:

logging trap notifications
 logging facility 21 
 logging device-id hostname 
 logging host inside IP.ADD.RE.SS udp 514

available trap levels:

{1 | alerts}—Immediate action needed
{2 | critical}—Critical conditions
{3 | errors}—Error conditions
{4 | warnings}—Warning conditions
{5 | notifications}—Normal but significant conditions
{6 | informational}—Informational messages
{7 | debugging}— Debugging messages

3. Optional – setup NTP

ntp server 192.5.41.41 source outside 
ntp server 192.5.41.40 source outside prefer

 

 Syslog-ng config:

open /etc/syslog-ng/syslog-ng.conf

and add the following lines:

source s_net {
       udp(ip(192.168.1.60) port(514));
       tcp(ip(192.168.1.60) port(51400));
};

and

log {
  source(s_net);
  destination(d_mysql);
};

then restart the syslog-ng service:

service syslog-ng restart

 

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