Cacti v1.1.38 Syslog Plugin 2.1 DB Issue

[fiznuckle]

Ever since my upgrade to v1.1.38 I have not been able to retrieve my syslog messages from the rsyslog database.

I keep receiving these errors:

09/10/2018 17:46:14 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog.php: 98 syslog_messages)(/plugins/syslog/syslog.php: 1327 get_syslog_messages)(/plugins/syslog/syslog.php: 804 syslog_db_fetch_assoc)(/plugins/syslog/database.php: 101 db_fetch_assoc)(/lib/database.php: 361 db_fetch_assoc_prepared)(/lib/database.php: 402 cacti_debug_backtrace)
09/10/2018 17:46:14 - DBCALL ERROR: SQL Assoc Failed!, Error: Table 'syslog.syslog_programs' doesn't exist
09/10/2018 17:46:14 - DBCALL ERROR: SQL Assoc Failed!, Error:1146, SQL:'(SELECT syslog.*, syslog_programs.program, 'main' AS mtype FROM `syslog`.`syslog` AS syslog LEFT JOIN `syslog`.`syslog_programs` ON syslog.program_id=syslog_programs.program_id WHERE logtime BETWEEN '2018-09-09 17:46' AND '2018-09-10 17:46' ) UNION (SELECT syslog.*, syslog_programs.program, 'remove' AS mtype FROM `syslog`.`syslog_removed` AS syslog LEFT JOIN `syslog`.`syslog_programs` ON syslog.program_id=syslog_programs.program_id WHERE logtime BETWEEN '2018-09-09 17:46' AND '2018-09-10 17:46') ORDER BY `logtime` DESC LIMIT 0,30'
09/10/2018 17:45:19 - SYSTEM SYSLOG STATS:Time:0.02 Deletes:0 Incoming:223 Removes:0 XFers:223 Alerts:0 Alarms:0 Reports:0
09/10/2018 17:45:19 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php: 516 syslog_db_execute)(/plugins/syslog/database.php: 52 db_execute)(/lib/database.php: 136 db_execute_prepared)(/lib/database.php: 209 cacti_debug_backtrace)
09/10/2018 17:45:19 - CMDPHP ERROR: A DB Exec Failed!, Error: Table 'syslog.syslog_programs' doesn't exist
09/10/2018 17:45:19 - DBCALL ERROR: A DB Exec Failed!, Error:1146, SQL:'DELETE FROM `syslog`.`syslog_programs` WHERE program_id NOT IN(SELECT DISTINCT program_id FROM `syslog`.`syslog` UNION SELECT DISTINCT program_id FROM syslog_removed)'
09/10/2018 17:45:19 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php: 504 syslog_db_execute)(/plugins/syslog/database.php: 52 db_execute)(/lib/database.php: 136 db_execute_prepared)(/lib/database.php: 209 cacti_debug_backtrace)
09/10/2018 17:45:19 - CMDPHP ERROR: A DB Exec Failed!, Error: Table 'syslog.syslog_programs' doesn't exist
09/10/2018 17:45:19 - DBCALL ERROR: A DB Exec Failed!, Error:1146, SQL:'DELETE FROM `syslog`.`syslog_programs` WHERE last_updated<'2018-08-11 17:45:19''
09/10/2018 17:45:19 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php: 469 syslog_db_execute)(/plugins/syslog/database.php: 52 db_execute)(/lib/database.php: 136 db_execute_prepared)(/lib/database.php: 209 cacti_debug_backtrace)
09/10/2018 17:45:19 - CMDPHP ERROR: A DB Exec Failed!, Error: Table 'syslog.syslog_programs' doesn't exist
09/10/2018 17:45:19 - DBCALL ERROR: A DB Exec Failed!, Error:1146, SQL:'INSERT INTO `syslog`.`syslog` (logtime, priority_id, facility_id, program_id, host_id, message) SELECT TIMESTAMP(`date`, `time`), priority_id, facility_id, program_id, host_id, message FROM (SELECT date, time, priority_id, facility_id, program_id, host_id, message FROM syslog_incoming AS si INNER JOIN syslog_hosts AS sh ON sh.host=si.host INNER JOIN syslog_programs AS sp ON sp.program=si.program WHERE status=60) AS merge'
09/10/2018 17:45:19 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php: 230 syslog_db_execute)(/plugins/syslog/database.php: 52 db_execute)(/lib/database.php: 136 db_execute_prepared)(/lib/database.php: 209 cacti_debug_backtrace)
09/10/2018 17:45:19 - CMDPHP ERROR: A DB Exec Failed!, Error: Table 'syslog.syslog_programs' doesn't exist
09/10/2018 17:45:19 - DBCALL ERROR: A DB Exec Failed!, Error:1146, SQL:'INSERT INTO `syslog`.`syslog_statistics` (host_id, facility_id, priority_id, program_id, insert_time, records) SELECT host_id, facility_id, priority_id, program_id, NOW(), SUM(records) AS records FROM (SELECT host_id, facility_id, priority_id, program_id, COUNT(*) AS records FROM syslog_incoming AS si INNER JOIN syslog_hosts AS sh ON sh.host=si.host INNER JOIN syslog_programs AS sp ON sp.program=si.program WHERE status=60 GROUP BY host_id, priority_id, facility_id, program_id) AS merge GROUP BY host_id, priority_id, facility_id, program_id'
09/10/2018 17:45:19 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php: 216 syslog_db_execute)(/plugins/syslog/database.php: 52 db_execute)(/lib/database.php: 136 db_execute_prepared)(/lib/database.php: 209 cacti_debug_backtrace)
09/10/2018 17:45:19 - CMDPHP ERROR: A DB Exec Failed!, Error: Unknown column 'facility_id' in 'field list'
09/10/2018 17:45:19 - DBCALL ERROR: A DB Exec Failed!, Error:1054, 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=60) 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()'
09/10/2018 17:45:19 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php: 205 syslog_db_execute)(/plugins/syslog/database.php: 52 db_execute)(/lib/database.php: 136 db_execute_prepared)(/lib/database.php: 209 cacti_debug_backtrace)
09/10/2018 17:45:19 - CMDPHP ERROR: A DB Exec Failed!, Error: Table 'syslog.syslog_programs' doesn't exist
09/10/2018 17:45:19 - DBCALL ERROR: A DB Exec Failed!, Error:1146, SQL:'INSERT INTO `syslog`.`syslog_programs` (program) SELECT DISTINCT program FROM `syslog`.`syslog_incoming` WHERE status=60 ON DUPLICATE KEY UPDATE program=VALUES(program), last_updated=NOW()'


Here are my syslog tables:

MariaDB [syslog]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| syslog |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [syslog]> show tables;
+------------------------+
| Tables_in_syslog |
+------------------------+
| SystemEvents |
| SystemEventsProperties |
| syslog |
| syslog_alert |
| syslog_facilities |
| syslog_host_facilities |
| syslog_hosts |
| syslog_incoming |
| syslog_logs |
| syslog_priorities |
| syslog_remove |
| syslog_removed |
| syslog_reports |
| syslog_statistics |
+------------------------+
14 rows in set (0.00 sec)


And my DB is being populated:

MariaDB [syslog]> select * from syslog_incoming limit 1;
+----------+----------+------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------+
| facility | priority | date | time | host | message | seq | status |
+----------+----------+------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------+
| 1 | 5 | 2018-09-10 | 17:50:30 | 10.100.0.16 | <134>Sep 10 17:50:30 ta924e-msh.cdc.tul.ok VQM:EVENTS Monitoring session ended for 8472240222 to 9184937000, RTP=x.x.x.x:30362->172.30.253.10:11656, MOS (LQ/PQ)=4.20/4.45, loss=0 pkts, out-of-order=0 pkts, jitter=0 ms | 187247098 | 0 |
+----------+----------+------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------+
1 row in set (0.13 sec)


Here is my syslog/config.php:

*/

global $config, $database_type, $database_default, $database_hostname;
global $database_username, $database_password, $database_port;

/* 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 = 'rsyslog';
$syslogdb_password = 'rsyslogpass';
$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_id';
$syslog_incoming_config['facilityField'] = 'facility_id';
$syslog_incoming_config['hostField'] = 'host_id';
$syslog_incoming_config['textField'] = 'message';
$syslog_incoming_config['id'] = 'seq';

?>



Am I missing a table in my database possibly?

I appreciate your help!

[Pucho]

You're DB is actually missing that table you can see it on the logs. Not sure how did that happen if was there before

Code: Select all

 syslog_programs  

Code: Select all

MariaDB [syslog]> show tables;
+------------------------+
| Tables_in_syslog       |
+------------------------+
| syslog                 |
| syslog_alert           |
| syslog_facilities      |
| syslog_host_facilities |
| syslog_hosts           |
| syslog_incoming        |
| syslog_logs            |
| syslog_priorities      |
| syslog_programs        |
| syslog_remove          |
| syslog_removed         |
| syslog_reports         |
| syslog_statistics      |
+------------------------+

I guess you could re-install it, there is an option when you uninstall syslog plugin to keep the data I think.

BTW, where did these two tables come from? Something customized on your rsyslog template? This sort of indicates there has been some dark magic over it.

Code: Select all

| SystemEvents |
| SystemEventsProperties |

[fiznuckle]

I don't think table syslog_programs was ever there to be honest? I mean, I have an output of an older "show tables", before my upgrade and it wasn't there. Everything was working fine before? I believe I was running an older 8.8 version and can't remember the syslog version, before the upgrade.

Is it possible that was a new table for syslog version 2.1. Is there a script than can possibly recreate this table manually?

As far as tables | SystemEvents || SystemEventsProperties | are concerned, those were setup with rsyslog initially logging to the DB.

Thanks for your help!

[Pucho]

Hi,

No idea if it was a result of upgrading your cacti version or what.

You could look at https://github.com/Cacti/plugin_syslog/ ... /setup.php and search for syslog_programs. You should find the CREATE TABLE SQL statement.

Not sure of any other implications of creating this table manually.

I noticed the uninstall function does not include a drop for this table either.

This is the show create table of mine

Code: Select all

CREATE TABLE `syslog_programs` (
  `program_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `program` varchar(40) NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`program`),
  KEY `host_id` (`program_id`),
  KEY `last_updated` (`last_updated`)
) ENGINE=MyISAM COMMENT='Contains all programs currently in the syslog table';

[fiznuckle]

So I went ahead an uninstalled the plugin, tables, data, everything... reinstalled the plugin.

MariaDB [syslog]> show tables;
+------------------------+
| Tables_in_syslog |
+------------------------+
| SystemEvents |
| SystemEventsProperties |
| syslog |
| syslog_alert |
| syslog_facilities |
| syslog_host_facilities |
| syslog_hosts |
| syslog_incoming |
| syslog_logs |
| syslog_priorities |
| syslog_programs |
| syslog_remove |
| syslog_removed |
| syslog_reports |
| syslog_statistics |
+------------------------+
15 rows in set (0.00 sec)


syslog_programs table is at least in there now. The odd thing now is everything is going into my SystemEvents table?


MariaDB [syslog]> SELECT * FROM `SystemEvents` ORDER BY `ID` DESC limit 1;
+-----------+------------+---------------------+---------------------+----------+----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
| ID | CustomerID | ReceivedAt | DeviceReportedTime | Facility | Priority | FromHost | Message | NTSeverity | Importance | EventSource | EventUser | EventCategory | EventID | EventBinaryData | MaxAvailable | CurrUsage | MinUsage | MaxUsage | InfoUnitID | SysLogTag | EventLogType | GenericFileName | SystemID |
+-----------+------------+---------------------+---------------------+----------+----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
| 187282370 | NULL | 2018-09-11 17:08:28 | 2018-09-21 15:44:13 | 16 | 6 | ta924e-24x7.cdc.tul.ok | EVENTS Monitoring session ended for 01259825981 to 1921, RTP=192.168.1.2:10204->192.168.1.3:11668, MOS (LQ/PQ)=4.20/4.45, loss=0 pkts, out-of-order=0 pkts, jitter=0 ms | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | VQM: | NULL | NULL | NULL |
+-----------+------------+---------------------+---------------------+----------+----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
1 row in set (0.00 sec)

I am not receiving anything in my syslog_incoming table like I was before the uninstall of the plugin, which may be a reason why nothing is showing up in cacti syslog?


This error is also being logged in MariaDB and nothing is showing up in my cacti syslog.

| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | rsyslogd: | NULL | NULL | NULL |
| 187282183 | NULL | 2018-09-11 17:05:15 | 2018-09-11 17:05:15 | 5 | 3 | nms | db error (1054): Unknown column 'facility' in 'field list'
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | rsyslogd: | NULL | NULL | NULL |
| 187282182 | NULL | 2018-09-11 17:05:15 | 2018-09-11 17:05:15 | 5 | 3 | nms | db error (1054): Unknown column 'facility' in 'field list'
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | rsyslogd: | NULL | NULL | NULL |
| 187282181 | NULL | 2018-09-11 17:05:15 | 2018-09-11 17:05:15 | 5 | 3 | nms | db error (1054): Unknown column 'facility' in 'field list'
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | rsyslogd: | NULL | NULL | NULL |
| 187282180 | NULL | 2018-09-11 17:05:15 | 2018-09-11 17:05:15 | 5 | 3 | nms | db error (1054): Unknown column 'facility' in 'field list'


Thanks for your help!

[fiznuckle]

Okay, I don't know if this makes any sense, but I seemed to get syslog to function again, although different than what the Github Cacti / plugin_syslog link @ https://github.com/Cacti/plugin_syslog says to do??

Of course after uninstalling and reinstalling the plugin and clearing all my original errors because of the cacti 1.1.38 and syslog 2.1 upgrade, I also put in the cacti.conf file in my /etc/rsyslog.d directory, which is what the install said to do, but did nothing in my case? Now I don't know if this is because I am somehow using rsyslog with MariaDB differently, which I wouldn't think so, but what I did was copy the lines:

$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, date, time, host, message) \
values (%syslogfacility%, %syslogpriority%, '%programname%', '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', TRIM('%msg%'))", SQL

from the cacti.conf file I was supposed to use and pasted that directly into the /etc/rsyslog.conf file, while commenting out my old $template info. This is what mine looks like:

# Output logs for MariaDB rsyslog database
$ModLoad ommysql
#$template cacti_syslog,"INSERT INTO syslog_incoming(facility, priority, date, time, host, message) values (%syslogfacility%, %syslogpriority%, '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', '%msg%')", SQL
$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, date, time, host, message) \
values (%syslogfacility%, %syslogpriority%, '%programname%', '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', TRIM('%msg%'))", SQL
*.* >localhost,syslog,rsyslog,rsyslogpass;cacti_syslog
*.* :ommysql:127.0.0.1,syslog,rsyslog,rsyslogpass

Maybe, somebody can explain why putting the new $template config from the new cacti.conf file directly into the rsyslog.conf fixed my issue? Thanks for your help!

[netniV]

Because the field names are different for the MySQL statement.

[fiznuckle]

Yes, I understand the fields are different, but based on the install on Github, how come the cacti.conf file didn't work and I had to put the new $template fields in the rsyslog.conf? I would assume there was a reason for the cacti.conf file, otherwise the install would have just said to edit the fields in the rsyslog.conf .

If I did something wrong, whereby it is not seeing the new cacti.conf file and is still using the rsyslog.conf, then I just wanted to make things right.

I appreciate your help!

[netniV]

Likely hood is that the plugin is wrong. Open the issue including the details of your problem and fix on https://github.com/cacti/plugin_syslog/issues/

When I come to review the plugin after the beta is released for 1.2, we'll take a look. Please link back to this thread so we know where it came from too.

[Pucho]

It really sounds like you're missing

Code: Select all

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf

[fiznuckle]

Pucho,

Just letting you know I did confirm that indeed those lines were in my rsyslog.conf file.

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf