Meticulously Planned

So I've been toying with building a simple log searching web-app. There are plenty of services to monitor and when something goes wrong finding a solution must be speedy.

There are a few tutorials out there to get you rolling on logging syslog-ng to PostgreSQL. However, they are quite basic and are pretty much limited to a single table. Since I would like to have relationships between at minimum logs and the host that sent them, I wrote a little bit of code to make this possible.

So lets start by getting our database setup correctly. I'm assuming you know how to create users, databases, and secure them.

CREATE TABLE logs_host (
id integer NOT NULL,
name character varying(200) NOT NULL,
label character varying(200),
created_at timestamp with time zone NOT NULL,
modified_at timestamp with time zone NOT NULL
);

CREATE SEQUENCE logs_host_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

CREATE TABLE logs_host (
id integer NOT NULL,
name character varying(200) NOT NULL,
label character varying(200),
created_at timestamp with time zone NOT NULL,
modified_at timestamp with time zone NOT NULL
);

CREATE SEQUENCE logs_log_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

Now lets create a PLPGSQL based function for PostgreSQL which will handle building our foreign-key relationships and inserting data into the database.

CREATE OR REPLACE FUNCTION syslog(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, text) RETURNS boolean AS
'
DECLARE

_HOST ALIAS FOR $1;
_FACILITY ALIAS FOR $2;
_PRIORITY ALIAS FOR $3;
_LEVEL ALIAS FOR $4;
_TAG ALIAS FOR $5;
_YEAR ALIAS FOR $6;
_MONTH ALIAS FOR $7;
_DAY ALIAS FOR $8;
_HOUR ALIAS FOR $9;
_MIN ALIAS FOR $10;
_SEC ALIAS FOR $11;
_PROGRAM ALIAS FOR $12;
_MSG ALIAS FOR $13;
hostid integer;

BEGIN

SELECT INTO hostid id FROM logs_host WHERE name = _HOST;

IF NOT FOUND THEN
SELECT INTO hostid nextval(''logs_host_id_seq'');
INSERT INTO logs_host (id, name, created_at, modified_at) VALUES (hostid, _HOST, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
END IF;

IF hostid IS NULL THEN
RAISE EXCEPTION ''There was an error locating or creating a host entry for (%)'', _HOST;
END IF;

INSERT INTO logs_log (host_id, received_at, sent_at, facility, priority, "level", tag, program, text)
VALUES (hostid, CURRENT_TIMESTAMP,
(_YEAR || ''-'' || _MONTH || ''-'' || _DAY || '' '' || _HOUR || '':'' || _MIN || '':'' || _SEC || '' -0:00'')::timestamptz,
_FACILITY, _PRIORITY, _LEVEL, _TAG, _PROGRAM, _MSG);

RETURN ''t'';

END;
' LANGUAGE 'plpgsql';

You may need to perform

CREATE LANGUAGE 'plpgsql';

inside your database beforehand so the function gets created correctly.

You might notice that the database tables are aptly named to be used with Django ;-)


Next lets configure our syslog-ng. I'm assuming you are running Ubuntu where the default all-inclusive 'source' is named s_all. Add the following to /etc/syslog-ng/syslog-ng.conf.

destination d_postgres {
pipe("/tmp/pgsql.pipe"
template("SELECT syslog('$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR', '$MONTH', '$DAY', '$HOUR', '$MIN', '$SEC', '$PROGRAM', '$MSG');\n")
template-escape(yes)
);
};

# log to postgresql
log {
source(s_all);
destination(d_postgres);
};

Last, lets create a temporary fifo pipe to PostgreSQL. You would want to secure this in a production environment.

mkfifo /tmp/pgsql.pipe
psql -U postgres -h localhost syslog < /tmp/pgsql.pipe

Now, restart syslog-ng!

/etc/init.d/syslog-ng restart