First, I love this plugin! on to the question:
I was using an older version of the plugin just fine, but then I found the newer one with the alerts and removal stuff. With the sql file that comes with the plugin, no syslogs go into the database. I edited the sql file and got it so that the syslogs go into the syslog_incoming table (I can see it in MySQL Administrator), but it doesn't go into the syslog table (or logs table because thats how I had it before, but it shouldn't matter right?).
Here's my config.php:
Code: Select all
<?php
$haloedb_type = "mysql";
$haloedb_default = "syslog";
$haloedb_hostname = "localhost";
$haloedb_username = "syslogadmin";
$haloedb_password = "****";
$haloe_config["rows_per_page"] = "30";
// $haloe_config["host_rows"] = "25";
// Integrate with Graph View Timespan Selector. If false, keep seperate timespan settings
$haloe_config["graphtime"] = true;
// Display timespan selector or not [ only used if $haloe_config["graphtime"]=false ]
$haloe_config["timespan_sel"] = true;
// Field Mappings, adjust to match the haloe table columns in use
$haloe_config["haloeTable"] = "syslog";
//$haloe_config["haloeTable"] = "syslog_incoming";
// $haloe_config["haloeTable"] = "logs";
$haloe_config["incomingTable"] = "syslog_incoming";
$haloe_config["removeTable"] = "syslog_remove";
$haloe_config["alertTable"] = "syslog_alert";
$haloe_config["dateField"] = "date";
$haloe_config["timeField"] = "time";
$haloe_config["priorityField"] = "priority";
$haloe_config["levelField"] = "level";
$haloe_config["tagField"] = "tag";
$haloe_config["facilityField"] = "facility";
$haloe_config["hostField"] = "host";
$haloe_config["programField"] = "program";
$haloe_config["textField"] = "msg";
$haloe_config["id"] = "seq";
code snipped
?>
My syslog.sql that I edited:
Code: Select all
--
-- Table structure for table `syslog`
--
CREATE TABLE syslog (
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,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY date (date),
KEY time (time),
KEY priority (priority),
KEY facility (facility)
) ENGINE=MyISAM;
-- --------------------------------------------------------
--
-- Table structure for table `syslog_alert`
--
CREATE TABLE syslog_alert (
id int(10) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
`type` varchar(16) NOT NULL default '',
message text NOT NULL,
`user` varchar(32) NOT NULL default '',
`date` int(16) NOT NULL default '0',
email text NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
-- --------------------------------------------------------
--
-- Table structure for table `syslog_incoming`
--
CREATE TABLE syslog_incoming (
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,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`status` tinyint(4) NOT NULL default '0',
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) ENGINE=MyISAM;
-- --------------------------------------------------------
--
-- Table structure for table `syslog_remove`
--
CREATE TABLE syslog_remove (
id int(10) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
`type` varchar(16) NOT NULL default '',
message text NOT NULL,
`user` varchar(32) NOT NULL default '',
`date` int(16) NOT NULL default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;
My syslog-ng.conf:
Code: Select all
options {
# use_fqdn(yes);
# use_dns(yes);
# dns_cache(yes);
keep_hostname(yes);
long_hostnames(off);
sync(1);
log_fifo_size(1024);
};
source src { unix-stream("/dev/log"); internal(); };
source net {
udp();
tcp();
};
destination d_mysql {
pipe("/tmp/mysql.pipe"
template("INSERT INTO syslog_incoming (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); };
My syslog_mysql.sh:
Code: Select all
#!/bin/bash
if [ -e /tmp/mysql.pipe ]; then
while [ -e /tmp/mysql.pipe ]
do
mysql -u syslogadmin --password=**** syslog < /tmp/mysql.pipe
done
else
mkfifo /tmp/mysql.pipe
fi
I get this from my cacti log, this takes the syslog from the incoming to the syslog table right?:
Code: Select all
02/22/2006 08:50:48 PM - THOLD: Poller[0] Checking Thresholds
02/22/2006 08:50:47 PM - CMDPHP: Poller[0] ERROR: SQL Exec Failed "UPDATE syslog_incoming set status=1"
02/22/2006 08:50:47 PM - SYSTEM STATS: Time:46.3422 Method:cmd.php Processes:10 Threads:N/A Hosts:20 HostsPerProcess:2 DataSources:81 RRDsProcessed:76
Ubuntu 5.10 Breezy
Cacti 0.8.6h
MySQL 4.1.12
PHP 5
syslog-ng 1.6.8
Thanks!
I've noticed one thing that I'd like to point out. That is, the time between the actual event on a device and the time it is recorded into the syslog database is about 2 - 3 minutes exactly. I'm not sure if this is my device creating the delay or the Cacti box. I will do more research on this item. So, if you need some help just drop a line. Thank you egarnel for your quick reply and suggestions; they worked!
