[syslog-ng]logging into database
Michael Earls
Michael.Earls@chmcc.org
Fri, 16 Aug 2002 14:10:53 -0400
This is a MIME message. If you are reading this text, you may want to
consider changing to a mail reader or gateway that understands how to
properly handle MIME multipart messages.
--=_1F437257.75147AE8
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
I have not had any trouble logging messages to the mysql database with
this option set in syslog-ng. The one thing I have noticed from pple
asking for help is that this example only explains how to setup
syslog-ng to log from udp/external source, and not log internal
messages.
Bazi * What is the correct syntax " template-escape or
template_escape"
Thanks
Michael Earls
http://www.vermeer.org
>>> mdodson@coremetrics.com 08/16/02 12:56PM >>>
I noticed one thing. In the following section -- "template-escape"
should be "template_escape"
"
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));"
On Fri, 2002-08-16 at 06:49, hicham rahimi wrote:
>
> hi,
>
> i want to setup logging syslog-ng'messages into a
> mysql database ,in order to make the messages
> organization more powerful.
>
> i have tested an exemple given in www.vermeer.com
> but it doesn't work.
> if any one have tested it , tell me please if it
> contain a bug or not
> thanks
> hicham;
>
> here the exemple:
>
> Centralized syslog-ng to mysql database
>
>
> by Michael Earls
>
> To established a centralized location for syslog-ng
> collection in order to facilitate:
>
> Immediate log reporting (both real time and summary
> reporting)
> Long term log storage (for archival and for possible
> later analysis).
> Tools used:
> syslog-ng -- template() destinations are only
> supported by the 1.5.x releases.
> mysql
> My Own PhP Files
> phpmyedit
> phpmyadmin
> Syslog-ng/Pipe Scripts
> MySql Replication
>
>
> Demo https://daffy.chmcc.org/syslog//
> This site is still in testing phase
>
>
> Demo-files The files I used for the Demo -- NEW DEMO
> FILE --
>
> To setup syslog-ng to log to a mysql database. This
> assumes that you have installed and setup syslog-ng
> and mysql.
>
> Edit the syslog-ng.conf file
> This tells syslog-ng to pipe to a fifo template
>
> Add the following lines --
>
> ## Log syslog-ng
> to mysql database
> ##
>
> 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);
> };
>
> Then comment out this line --
>
> # This is the
> default behavior of sysklogd package
> # Logs may
> come from unix stream, but not from another machine.
> #
> #source src {
> unix-dgram("/dev/log"); internal(); };
>
> Then uncomment out this line --
>
> # If you wish
> to get logs from remote machine you should uncomment
> # this and
> comment the above source line.
> #
> source src {
> unix-dgram("/etc/log/log"); internal(); };
> source net {
> udp(); };
>
>
>
> Create the database for syslog-ng --
>
> Create a file called syslog.sql and paste this
> below, this will create the database " syslog" and
> table " logs" in mysql.
>
> #
> # Table structure for table
> `logs`
> #
>
> CREATE DATABASE syslog;
>
> USE syslog;
>
> 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;
>
> Run this command
>
> mysql -u root -p < syslog.sql
>
> This will install the database into mysql.
>
> Create a fifo pipe file --
> This is the file that syslog-ng will store
> records before writing to the database.
>
>
> mkfifo /tmp/mysql.pipe
>
> You need to restart syslog-ng --
>
> /etc/init.d/syslog-ng stop # Stop
> syslog-ng
> /etc/ini.d/syslog-ng start # Start
> syslog-ng
>
> Run this command to pipe the file mysql.pipe to mysql
> database
>
> You need to create a script that will check to make
> sure this command is running and restart if stopped.
> Syslog-ng/Pipe scripts
>
> When this file is started it will hang, You
> need to create a script and have it run on startup.
>
> mysql -u root --password=passwd
> syslog < /tmp/mysql.pipe
>
>
>
>
>
--------------------------------------------------------------------------------
>
>
> This is a mini how-to and is in the second
> draft::Thanks again to everyone..
>
> Michael
>
> Any questions just email me
> --Michael@michaelearls.com--
>
> Created Jan 18, 2002
> Last Updated May 18, 2002
>
>
>
> ___________________________________________________________
> Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
> Yahoo! Mail : http://fr.mail.yahoo.com
>
> _______________________________________________
> syslog-ng maillist - syslog-ng@lists.balabit.hu
> https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Frequently asked questions at
http://www.campin.net/syslog-ng/faq.html
_______________________________________________
syslog-ng maillist - syslog-ng@lists.balabit.hu
https://lists.balabit.hu/mailman/listinfo/syslog-ng
Frequently asked questions at http://www.campin.net/syslog-ng/faq.html
--=_1F437257.75147AE8
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2600.0" name=GENERATOR></HEAD>
<BODY style="MARGIN-TOP: 2px; FONT: 8pt Tahoma; MARGIN-LEFT: 2px">
<DIV><FONT face=Arial size=3>I have not had any trouble logging messages to the
mysql database with this option set in syslog-ng. The one thing I have noticed
from pple asking for help is that this example only explains how to setup
syslog-ng to log from udp/external source, and not log internal messages.
</FONT></DIV>
<DIV><FONT face=Arial size=3></FONT> </DIV>
<DIV><FONT face=Arial size=3>Bazi — What is the correct syntax " template-escape
or template_escape"</FONT></DIV>
<DIV><FONT face=Arial size=3></FONT> </DIV>
<DIV><FONT face=Arial size=3>Thanks</FONT></DIV>
<DIV><FONT face=Arial size=3>Michael Earls</FONT></DIV>
<DIV><FONT face=Arial size=3></FONT> </DIV>
<DIV><FONT face=Arial size=3><A
href="http://www.vermeer.org">http://www.vermeer.org</A></FONT></DIV>
<DIV><BR></DIV>
<DIV> </DIV>
<DIV><BR>>>> mdodson@coremetrics.com 08/16/02 12:56PM
>>><BR><BR>I noticed one thing. In the following section --
"template-escape"<BR>should be "template_escape" <BR><BR>"<BR>d_mysql
{<BR>>
<BR>>
pipe("/tmp/mysql.pipe"<BR>>
<BR>> template("INSERT INTO logs (host, facility, priority,<BR>> level,
tag, date,
<BR>>
time,<BR>> program, msg) VALUES ( '$HOST', '$FACILITY',<BR>> '$PRIORITY',
'$LEVEL', '$TAG',
<BR>>
<BR>> '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM',<BR>> '$MSG'
);\n") template-escape(yes));"<BR><BR><BR><BR><BR><BR>On Fri, 2002-08-16 at
06:49, hicham rahimi wrote:<BR>> <BR>> hi,<BR>> <BR>> i
want to setup logging syslog-ng'messages into a<BR>> mysql database ,in order
to make the messages<BR>> organization more powerful.<BR>>
<BR>> i have tested an exemple given in <A
href="http://www.vermeer.com/">www.vermeer.com</A><BR>> but it doesn't
work.<BR>> if any one have tested it , tell me please if
it<BR>> contain a bug or not<BR>>
thanks<BR>> hicham;
<BR>> <BR>> here the
exemple:<BR>> <BR>> Centralized syslog-ng to mysql
database<BR>> <BR>> <BR>> by Michael Earls <BR>> <BR>> To
established a centralized location for syslog-ng<BR>> collection in order to
facilitate: <BR>> <BR>> Immediate log reporting (both real time and
summary<BR>> reporting) <BR>> Long term log storage (for archival and for
possible<BR>> later analysis). <BR>> Tools used: <BR>> syslog-ng
-- template() destinations are only<BR>> supported by the 1.5.x releases.
<BR>> mysql <BR>> My Own PhP Files <BR>> phpmyedit <BR>>
phpmyadmin <BR>> Syslog-ng/Pipe Scripts <BR>> MySql Replication
<BR>> <BR>> <BR>> Demo <A
href="https://daffy.chmcc.org/syslog//">https://daffy.chmcc.org/syslog//</A><BR>>
This site is still in testing phase<BR>> <BR>> <BR>> Demo-files The
files I used for the Demo -- NEW DEMO<BR>> FILE -- <BR>> <BR>> To setup
syslog-ng to log to a mysql database. This<BR>> assumes that you have
installed and setup syslog-ng<BR>> and mysql.<BR>> <BR>> Edit the
syslog-ng.conf file<BR>> This tells
syslog-ng to pipe to a fifo template<BR>> <BR>> Add the following lines
--<BR>>
<BR>>
## Log syslog-ng<BR>> to mysql database
<BR>>
##
<BR>>
<BR>>
destination<BR>> d_mysql
{<BR>>
<BR>>
pipe("/tmp/mysql.pipe"<BR>>
<BR>> template("INSERT INTO logs (host, facility, priority,<BR>> level,
tag, date,
<BR>>
time,<BR>> program, msg) VALUES ( '$HOST', '$FACILITY',<BR>> '$PRIORITY',
'$LEVEL', '$TAG',
<BR>>
<BR>> '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM',<BR>> '$MSG'
);\n")
template-escape(yes));<BR>>
};<BR>>
log {<BR>> source(net);
destination(d_mysql);<BR>>
};<BR>> <BR>> Then comment out this line --<BR>>
<BR>>
# This is the<BR>> default behavior of sysklogd
package<BR>>
# Logs may<BR>> come from unix stream, but not from another
machine.<BR>>
#<BR>>
#source src {<BR>> unix-dgram("/dev/log"); internal(); };<BR>> <BR>>
Then uncomment out this line --<BR>>
<BR>>
# If you wish<BR>> to get logs from remote machine you should
uncomment<BR>>
# this and<BR>> comment the above source
line.<BR>>
#<BR>>
source src {<BR>> unix-dgram("/etc/log/log"); internal();
};<BR>>
source net {<BR>> udp(); };<BR>> <BR>> <BR>> <BR>> Create
the database for syslog-ng
--<BR>>
<BR>> Create a file called
syslog.sql and paste this<BR>> below, this will create the database " syslog"
and<BR>> table " logs" in
mysql.<BR>>
<BR>>
#<BR>>
# Table structure for table<BR>>
`logs`<BR>>
#<BR>>
<BR>>
CREATE DATABASE syslog;<BR>>
<BR>>
USE syslog;<BR>>
<BR>>
CREATE TABLE logs
(<BR>>
host varchar(32) default
NULL,<BR>>
facility varchar(10) default<BR>>
NULL,<BR>>
priority varchar(10) default<BR>>
NULL,<BR>>
level varchar(10) default<BR>>
NULL,<BR>>
tag varchar(10) default
NULL,<BR>>
date date default
NULL,<BR>>
time time default
NULL,<BR>>
program varchar(15) default<BR>>
NULL,<BR>>
msg
text,<BR>>
seq int(10) unsigned NOT NULL<BR>>
auto_increment,<BR>>
PRIMARY KEY
(seq),<BR>>
KEY host
(host),<BR>>
KEY seq
(seq),<BR>>
KEY program
(program),<BR>>
KEY time
(time),<BR>>
KEY date
(date),<BR>>
KEY priority
(priority),<BR>>
KEY facility
(facility)<BR>>
) TYPE=MyISAM;<BR>> <BR>>
Run this command<BR>>
<BR>>
mysql -u root -p < syslog.sql<BR>> <BR>> This will install the database
into mysql.<BR>> <BR>> Create a fifo pipe file --
<BR>> This is the
file that syslog-ng will store<BR>> records before writing to the
database.
<BR>> <BR>>
<BR>>
mkfifo /tmp/mysql.pipe<BR>> <BR>> You need to restart syslog-ng
--<BR>>
<BR>>
/etc/init.d/syslog-ng stop # Stop<BR>>
syslog-ng<BR>>
/etc/ini.d/syslog-ng start # Start<BR>> syslog-ng<BR>>
<BR>> Run this command to pipe the file mysql.pipe to mysql<BR>>
database<BR>> <BR>> You need to create a script that will check to
make<BR>> sure this command is running and restart if stopped.<BR>>
Syslog-ng/Pipe scripts<BR>>
<BR>> When this file is
started it will hang, You<BR>> need to create a script and have it run
on startup.<BR>>
<BR>>
mysql -u root --password=passwd<BR>> syslog < /tmp/mysql.pipe<BR>>
<BR>> <BR>> <BR>> <BR>>
--------------------------------------------------------------------------------<BR>>
<BR>> <BR>> This is a mini how-to and is in the second<BR>>
draft::Thanks again to everyone..<BR>> <BR>> Michael<BR>> <BR>> Any
questions just email me <BR>> --Michael@michaelearls.com--<BR>> <BR>>
Created Jan 18, 2002<BR>> Last Updated May 18, 2002 <BR>> <BR>>
<BR>> <BR>>
___________________________________________________________<BR>> Do You
Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !<BR>> Yahoo! Mail :
<A href="http://fr.mail.yahoo.com/">http://fr.mail.yahoo.com</A><BR>>
<BR>> _______________________________________________<BR>> syslog-ng
maillist - syslog-ng@lists.balabit.hu<BR>> <A
href="https://lists.balabit.hu/mailman/listinfo/syslog-ng">https://lists.balabit.hu/mailman/listinfo/syslog-ng</A><BR>>
Frequently asked questions at <A
href="http://www.campin.net/syslog-ng/faq.html">http://www.campin.net/syslog-ng/faq.html</A><BR><BR><BR><BR>_______________________________________________<BR>syslog-ng
maillist - syslog-ng@lists.balabit.hu<BR><A
href="https://lists.balabit.hu/mailman/listinfo/syslog-ng">https://lists.balabit.hu/mailman/listinfo/syslog-ng</A><BR>Frequently
asked questions at <A
href="http://www.campin.net/syslog-ng/faq.html">http://www.campin.net/syslog-ng/faq.html</A><BR><BR></DIV></BODY></HTML>
--=_1F437257.75147AE8--