Syslog issue cacti 0.8.8a

[vincent82]

Hello to all,

I installed cacti 0.8.8a on a ubuntu 12.04 workstation with plugins thold, weathermap etc.... and all is working fine.

However, I'm trying to use the syslog plugin after to have installed syslog-ng but at this time it's not working and i hope someone could help me.

I followed exactly the link: http://www.administration.mark-gadi.fr/ ... gin-syslog to uses syslog.

However, i get errors like :

"ERROR at line 1: Unknown command '\\'"
"ERROR at line 661: Unknown command '\\'"

etc whith differents number of line
and
"ERROR 1064 (42000) at line 1 : You have an error in your SQL syntax; check the manual tthat corresponds to your MySQL server version for the right syntax to use near 'host facility, priority, dat, time, message) VALUES ( 'IPsyslogclient', 'auth', 'at line 1"
"ERROR 1136 (21S01) at line 1215: Column count doesn't match value count at row 1"

Please find also my syslog-ng.conf file:

@version: 3.3
@include "scl.conf"

# Syslog-ng configuration file, compatible with default Debian syslogd
# installation.

# First, set some global options.
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
owner("root"); group("adm"); perm(0640); stats_freq(0);
bad_hostname("^gconfd$");
};

########################
# Sources
########################
# This is the default behavior of sysklogd package
# Logs may come from unix stream, but not from another machine.
#
source s_src {
system();
internal();
};

# If you wish to get logs from remote machine you should uncomment
# this and comment the above source line.
#
#source s_net { tcp(ip(127.0.0.1) port(1000) authentication(required) encrypt(allow)); };

########################
# Destinations
########################
# First some standard logfile
#
destination d_auth { file("/var/log/auth.log"); };
destination d_cron { file("/var/log/cron.log"); };
destination d_daemon { file("/var/log/daemon.log"); };
destination d_kern { file("/var/log/kern.log"); };
destination d_lpr { file("/var/log/lpr.log"); };
destination d_mail { file("/var/log/mail.log"); };
destination d_syslog { file("/var/log/syslog"); };
destination d_user { file("/var/log/user.log"); };
destination d_uucp { file("/var/log/uucp.log"); };

# This files are the log come from the mail subsystem.
#
destination d_mailinfo { file("/var/log/mail/mail.info"); };
destination d_mailwarn { file("/var/log/mail/mail.warn"); };
destination d_mailerr { file("/var/log/mail/mail.err"); };

# Logging for INN news system
#
destination d_newscrit { file("/var/log/news/news.crit"); };
destination d_newserr { file("/var/log/news/news.err"); };
destination d_newsnotice { file("/var/log/news/news.notice"); };

# Some `catch-all' logfiles.
#
destination d_debug { file("/var/log/debug"); };
destination d_error { file("/var/log/error"); };
destination d_messages { file("/var/log/messages"); };

# The root's console.
#
destination d_console { usertty("root"); };

# Virtual console.
#
destination d_console_all { file("/dev/tty10"); };

# The named pipe /dev/xconsole is for the nsole' utility. To use it,
# you must invoke nsole' with the -file' option:
#
# $ xconsole -file /dev/xconsole [...]
#
destination d_xconsole { pipe("/dev/xconsole"); };

# Send the messages to an other host
#
#destination d_net { tcp("127.0.0.1" port(1000) authentication(on) encrypt(on) log_fifo_size(1000)); };

# Debian only
destination d_ppp { file("/var/log/ppp.log"); };

########################
# Filters
########################
# Here's come the filter options. With this rules, we can set which
# message go where.

filter f_dbg { level(debug); };
filter f_info { level(info); };
filter f_notice { level(notice); };
filter f_warn { level(warn); };
filter f_err { level(err); };
filter f_crit { level(crit .. emerg); };

filter f_debug { level(debug) and not facility(auth, authpriv, news, mail); };
filter f_error { level(err .. emerg) ; };
filter f_messages { level(info,notice,warn) and
not facility(auth,authpriv,cron,daemon,mail,news); };

filter f_auth { facility(auth, authpriv) and not filter(f_debug); };
filter f_cron { facility(cron) and not filter(f_debug); };
filter f_daemon { facility(daemon) and not filter(f_debug); };
filter f_kern { facility(kern) and not filter(f_debug); };
filter f_lpr { facility(lpr) and not filter(f_debug); };
filter f_local { facility(local0, local1, local3, local4, local5,
local6, local7) and not filter(f_debug); };
filter f_mail { facility(mail) and not filter(f_debug); };
filter f_news { facility(news) and not filter(f_debug); };
filter f_syslog3 { not facility(auth, authpriv, mail) and not filter(f_debug); };
filter f_user { facility(user) and not filter(f_debug); };
filter f_uucp { facility(uucp) and not filter(f_debug); };

filter f_cnews { level(notice, err, crit) and facility(news); };
filter f_cother { level(debug, info, notice, warn) or facility(daemon, mail); };

filter f_ppp { facility(local2) and not filter(f_debug); };
filter f_console { level(warn .. emerg); };

########################
# Log paths
########################
log { source(s_src); filter(f_auth); destination(d_auth); };
log { source(s_src); filter(f_cron); destination(d_cron); };
log { source(s_src); filter(f_daemon); destination(d_daemon); };
log { source(s_src); filter(f_kern); destination(d_kern); };
log { source(s_src); filter(f_lpr); destination(d_lpr); };
log { source(s_src); filter(f_syslog3); destination(d_syslog); };
log { source(s_src); filter(f_user); destination(d_user); };
log { source(s_src); filter(f_uucp); destination(d_uucp); };

log { source(s_src); filter(f_mail); destination(d_mail); };
#log { source(s_src); filter(f_mail); filter(f_info); destination(d_mailinfo); };
#log { source(s_src); filter(f_mail); filter(f_warn); destination(d_mailwarn); };
#log { source(s_src); filter(f_mail); filter(f_err); destination(d_mailerr); };

log { source(s_src); filter(f_news); filter(f_crit); destination(d_newscrit); };
log { source(s_src); filter(f_news); filter(f_err); destination(d_newserr); };
log { source(s_src); filter(f_news); filter(f_notice); destination(d_newsnotice); };
#log { source(s_src); filter(f_cnews); destination(d_console_all); };
#log { source(s_src); filter(f_cother); destination(d_console_all); };

#log { source(s_src); filter(f_ppp); destination(d_ppp); };

log { source(s_src); filter(f_debug); destination(d_debug); };
log { source(s_src); filter(f_error); destination(d_error); };
log { source(s_src); filter(f_messages); destination(d_messages); };

log { source(s_src); filter(f_console); destination(d_console_all);
destination(d_xconsole); };
log { source(s_src); filter(f_crit); destination(d_console); };

# All messages send to a remote site
#
#log { source(s_src); destination(d_net); };

###
# Include all config files in /etc/syslog-ng/conf.d/
###
@include "/etc/syslog-ng/conf.d/"

# we are using udp, and this is a collector for net traffic only
#
source net { udp(); };

destination d_mysql { pipe("/tmp/mysql.pipe" template("INSERT INTO syslog_incoming (host, facility, priority, date, time, message) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$MSG' );\n") template-escape(yes) ); };

log { source(net); destination(d_mysql); }; log { source(s_src); destination(d_mysql); };

and my config.php file:

* revert if you dont use the Cacti database */
$use_cacti_db = false;

if (!$use_cacti_db) {
$syslogdb_type = 'mysql';
$syslogdb_default = 'syslog';
$syslogdb_hostname = 'localhost';
$syslogdb_username = 'sysloguser';
$syslogdb_password = 'sysloguser';
$syslogdb_port = 3306;
}else{
$syslogdb_type = $database_type;
$syslogdb_default = $database_default;
$syslogdb_hostname = $database_hostname;
$syslogdb_username = $database_username;
$syslogdb_password = $database_password;
$syslogdb_port = $database_port;
}

/* field in the incomming table */
$syslog_incoming_config['dateField'] = 'date';
$syslog_incoming_config['timeField'] = 'time';
$syslog_incoming_config['priorityField'] = 'priority';
$syslog_incoming_config['facilityField'] = 'facility';
$syslog_incoming_config['hostField'] = 'host';
$syslog_incoming_config['textField'] = 'message';
$syslog_incoming_config['id'] = 'seq';

?>


Thanks in advance for your help.

Regards,

[cigamit]

From the last error, it seems like you forgot a comma between host and facility in this line. That would be the first thing I check.
destination d_mysql { pipe("/tmp/mysql.pipe" template("INSERT INTO syslog_incoming (host, facility, priority, date, time, message) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$MSG' );\n") template-escape(yes) ); };

[vincent82]

Hello Cigamit,

Thanks for your help.

After to have follow your recommandations, following errors disappeared:

"ERROR at line 1: Unknown command '\\'"
"ERROR at line 661: Unknown command '\\'"

However in the cacti log files, i get the following errors:

06/13/2013 08:43:45 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT host_id, host FROM `syslog`.`syslog_hosts`"
06/13/2013 08:43:45 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `syslog`.`syslog_hosts` ORDER BY host"
06/13/2013 08:43:43 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT host_id, host FROM `syslog`.`syslog_hosts`"
06/13/2013 08:43:43 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `syslog`.`syslog_hosts` ORDER BY host"
06/13/2013 08:43:42 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT host_id, host FROM `syslog`.`syslog_hosts`"
06/13/2013 08:43:42 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `syslog`.`syslog_hosts` ORDER BY host"
06/13/2013 08:43:40 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT host_id, host FROM `syslog`.`syslog_hosts`"
06/13/2013 08:43:40 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `syslog`.`syslog_hosts` ORDER BY host"
06/13/2013 08:43:39 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1146', SQL:"SELECT count(*) FROM `syslog`.`syslog_logs` AS sl LEFT JOIN `syslog`.`syslog_facilities` AS sf ON sl.facility=sf.facility LEFT JOIN `syslog`.`syslog_priorities` AS sp ON sl.priority=sp.priority LEFT JOIN `syslog`.`syslog_hosts` AS sh ON sl.host=sh.host LEFT JOIN `syslog`.`syslog_alert` AS sa ON sl.alert_id=sa.id WHERE logtime BETWEEN '2013-06-13 08:13' AND '2013-06-13 08:43'"
06/13/2013 08:43:39 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `syslog`.`syslog_hosts` ORDER BY host"
06/13/2013 08:43:39 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT sl.*, sa.name, sa.severity FROM `syslog`.`syslog_logs` AS sl LEFT JOIN `syslog`.`syslog_facilities` AS sf ON sl.facility=sf.facility LEFT JOIN `syslog`.`syslog_priorities` AS sp ON sl.priority=sp.priority LEFT JOIN `syslog`.`syslog_hosts` AS sh ON sl.host=sh.host LEFT JOIN `syslog`.`syslog_alert` AS sa ON sl.alert_id=sa.id WHERE logtime BETWEEN '2013-06-13 08:13' AND '2013-06-13 08:43' ORDER BY logtime DESC LIMIT 0,30"
06/13/2013 08:43:39 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1146', SQL:"SELECT COUNT(*) FROM `syslog`.`syslog_statistics` AS ss LEFT JOIN `syslog`.`syslog_facilities` AS sf ON ss.facility_id=sf.facility_id LEFT JOIN `syslog`.`syslog_priorities` AS sp ON ss.priority_id=sp.priority_id LEFT JOIN `syslog`.`syslog_hosts` AS sh ON ss.host_id=sh.host_id GROUP BY sh.host, sf.facility, sp.priority"
06/13/2013 08:43:39 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT sh.host, sf.facility, sp.priority, sum(ss.records) AS records FROM `syslog`.`syslog_statistics` AS ss LEFT JOIN `syslog`.`syslog_facilities` AS sf ON ss.facility_id=sf.facility_id LEFT JOIN `syslog`.`syslog_priorities` AS sp ON ss.priority_id=sp.priority_id LEFT JOIN `syslog`.`syslog_hosts` AS sh ON ss.host_id=sh.host_id GROUP BY sh.host, sf.facility, sp.priority ORDER BY host DESC LIMIT 0,30"


I restarted service syslog-ng and mysql but without success

Let me know if you have an idea

Thanks

[cigamit]

Does the syslog Database exist? Did you import the syslog tables into it?

[vincent82]

Hello,

Yes i create the database syslog and imported the syslog.sql into.

mysql> show tables;
+------------------------+
| Tables_in_syslog |
+------------------------+
| syslog |
| syslog_alert |
| syslog_facilities |
| syslog_host_facilities |
| syslog_hosts |
| syslog_incoming |
| syslog_logs |
| syslog_priorities |
| syslog_remove |
| syslog_removed |
| syslog_reports |
| syslog_statistics |
+------------------------+
12 rows in set (0.00 sec)

How can i debug this issue?

Thanks

[vincent82]

Hi,

Any ideas about this issue?

Thx

[cigamit]

Seems like a permission issue, that the user cacti is connecting to the database as can not access the syslog database.

[vincent82]

Hello,

I checked permissions but it seems ok.

[superdust]

I also have problems configuring syslog-ng.conf
Using syslog-ng 2.0.9-4.2, Ubuntu 10.04LTS

I have tried all guides and sources that I can find, but get errors when restarting syslog-ng

Can anybody post a full working config file for this version of syslog-ng with cacti setup?
Would appreciate it very much

[vincent82]

Up....