Some issues with Syslog

[n0xmad]

Hi All!

I'm currently setting up a cacti syslog server, however im having a few issues when the syslog plugin is moving the data from the syslog_incoming to syslog_programs and syslog_host_facilities.

As mentioned before, when i send logs from for examply my routers - i can see the logs hitting the server while using a TCP dump.
The Syslog_incoming table is getting filled but, when the syslog poller procceses im getting these errors:

Latest logging from Cacti:
2021-10-26 09:50:14 - SYSTEM SYSLOG STATS: Time:0.03 Deletes:0 Incoming:9 Removes:0 XFers:0 Alerts:0 Alarms:0 Reports:0
2021-10-26 09:50:14 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php[236]:syslog_db_execute(), /plugins/syslog/database.php[57]:db_execute(), /lib/database.php[238]:db_execute_prepared())
2021-10-26 09:50:14 - CMDPHP ERROR: A DB Exec Failed!, Error: Column 'facility_id' cannot be null
2021-10-26 09:50:14 - DBCALL ERROR: A DB Exec Failed!, Error: 1048, SQL: 'INSERT INTO `syslog`.`syslog_host_facilities` (host_id, facility_id) SELECT host_id, facility_id FROM ((SELECT DISTINCT host, facility_id FROM `syslog`.`syslog_incoming` WHERE status=10) AS s INNER JOIN `syslog`.`syslog_hosts` AS sh ON s.host=sh.host) ON DUPLICATE KEY UPDATE host_id=VALUES(host_id), last_updated=NOW()'
2021-10-26 09:50:14 - MAINT Cacti Log Rotation - TIMECHECK Ran: 2021-10-26 00:00:43, Now: 2021-10-26 09:50:14, Next: 2021-10-26 23:59:59
2021-10-26 09:50:14 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php[221]:syslog_db_execute(), /plugins/syslog/database.php[57]:db_execute(), /lib/database.php[238]:db_execute_prepared())
2021-10-26 09:50:14 - CMDPHP ERROR: A DB Exec Failed!, Error: Column 'program' cannot be null
2021-10-26 09:50:14 - DBCALL ERROR: A DB Exec Failed!, Error: 1048, SQL: 'INSERT INTO `syslog`.`syslog_programs` (program, last_updated) SELECT DISTINCT program, NOW() FROM `syslog`.`syslog_incoming` WHERE status=10 ON DUPLICATE KEY UPDATE program=VALUES(program), last_updated=VALUES(last_updated)'

My Syslog-NG config:
@version: 3.25
@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);
dns_cache(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 { udp(ip(127.0.0.1) port(3306)); };
source s_net {
tcp();
udp();
};
########################
# Destinations
########################
# First some standard logfile
#



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$");
};

# we are using udp, and this is a collector for net traffic only
#
source s_all {
udp();
# internal();
# unix-stream("/dev/log");
# file("/proc/kmsg" log_prefix("kernel: "));
};

destination d_mysql {
sql(type(mysql)
host("localhost") username("USEREDITEDFORPOSTING") password("PASWORDEDITEDFORPOSTING")
database("syslog")
table("syslog_incoming")
columns("facility", "priority", "date", "time", "host", "message")
values("$FACILITY", "$PRIORITY", "$YEAR-$MONTH-$DAY", "$HOUR:$MIN:$SEC", "$HOST_FROM", "$MSG")
indexes("facility", "priority", "date", "time", "host", "msg"));
};

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


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.info"); };
destination d_mailwarn { file("/var/log/mail.warn"); };
destination d_mailerr { file("/var/log/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(`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) 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/*.conf"




Im getting a bit lost where to look to be honest.
Why does it enter into incoming, but while being parsed into Syslog_facility or syslog_programs it can't?
Where can i see the functions of parsing from incoming to the other tables?

[TheWitness]

Did you ever get this worked out? I'm using rsyslog and no issues. I've not used syslog-ng for a long time. It's a great tool, and I've had the opportunity to interact with the owner/author over the years, but no time to double back and re-certify the integration sadly.