Syslog Plugin Configuration Errors

General discussion about Plugins for Cacti

Moderators: Moderators, Developers

Post Reply
Author
Message
peterc1985
Posts: 1
Joined: Wed Sep 05, 2012 4:55 am

Syslog Plugin Configuration Errors

#1 Post by peterc1985 » Wed Sep 05, 2012 5:05 am

Hi folks,

I've installed the Syslog plugin on a Cacti installation we have here which has Weathermap humming along nicely.

The rSyslog logger is writing to a MySQL database called "Syslog" using the "sysloguser" user account:

mysql> select Message from SystemEvents;
"| Sep 5 10:59:13: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/17, changed state to up "

The Cacti tab however doesn't appear to be displaying these, with the following errors present in the Cacti log file:

Code: Select all

09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `Syslog`.`syslog_reports` WHERE enabled='on'"
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"DELETE FROM `Syslog`.`syslog_logs` WHERE logtime<'2012-08-07 08:40:02''
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"DELETE FROM `Syslog`.`syslog_statistics` WHERE insert_time<'2012-08-07 08:40:02''
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"DELETE FROM `Syslog`.`syslog_incoming` WHERE status=125'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"INSERT INTO `Syslog`.`syslog` (logtime, priority_id, facility_id, host_id, message) SELECT TIMESTAMP(`date`, `time`), priority_id, facility_id, host_id, message FROM (SELECT date, time, priority_id, facility_id, host_id, message FROM syslog_incoming AS si INNER JOIN syslog_facilities AS sf ON sf.facility=si.facility INNER JOIN syslog_priorities AS sp ON sp.priority=si.priority INNER JOIN syslog_hosts AS sh ON sh.host=si.host WHERE status=125) AS merge'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `Syslog`.`syslog_alert` WHERE enabled='on'"
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1146', SQL:"SELECT count(*) FROM `Syslog`.`syslog_incoming` WHERE status=125"
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1146', SQL:"SELECT * FROM `Syslog`.`syslog_remove` WHERE enabled='on'"
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"INSERT INTO `Syslog`.`syslog_statistics` (host_id, facility_id, priority_id, insert_time, records) SELECT host_id, facility_id, priority_id, NOW(), sum(records) AS records FROM (SELECT host_id, facility_id, priority_id, count(*) AS records FROM syslog_incoming AS si INNER JOIN syslog_facilities AS sf ON sf.facility=si.facility INNER JOIN syslog_priorities AS sp ON sp.priority=si.priority INNER JOIN syslog_hosts AS sh ON sh.host=si.host WHERE status=125 GROUP BY host_id, priority_id, facility_id) AS merge GROUP BY host_id, priority_id, facility_id'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"INSERT INTO `Syslog`.`syslog_host_facilities` (host_id, facility_id) SELECT host_id, facility_id FROM ((SELECT DISTINCT host, facility FROM `Syslog`.`syslog_incoming` WHERE status=125) AS s INNER JOIN `Syslog`.`syslog_hosts` AS sh ON s.host=sh.host INNER JOIN `Syslog`.`syslog_facilities` AS sf ON sf.facility=s.facility) ON DUPLICATE KEY UPDATE host_id=VALUES(host_id), last_updated=NOW()'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"INSERT INTO `Syslog`.`syslog_hosts` (host) SELECT DISTINCT host FROM `Syslog`.`syslog_incoming` WHERE status=125 ON DUPLICATE KEY UPDATE host=VALUES(host), last_updated=NOW()'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"INSERT INTO `Syslog`.`syslog_priorities` (priority) SELECT DISTINCT priority FROM `Syslog`.`syslog_incoming` ON DUPLICATE KEY UPDATE priority=VALUES(priority), last_updated=NOW()'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"INSERT INTO `Syslog`.`syslog_facilities` (facility) SELECT DISTINCT facility FROM `Syslog`.`syslog_incoming` ON DUPLICATE KEY UPDATE facility=VALUES(facility), last_updated=NOW()'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"UPDATE `Syslog`.`syslog_incoming` SET priority='warn' WHERE priority='warning''
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"UPDATE `Syslog`.`syslog_incoming` SET status=125 WHERE status=0'
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1146', SQL:"SELECT count(*) FROM `Syslog`.`syslog_incoming` WHERE status=125"
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"DELETE FROM `Syslog`.`syslog_removed` WHERE logtime < '2012-08-07''
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1146', SQL:"DELETE FROM `Syslog`.`syslog` WHERE logtime < '2012-08-07''
09/06/2012 08:40:02 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1146', SQL:"SHOW CREATE TABLE `Syslog`.`syslog`"
Contents of /usr/share/cacti/plugins/syslog config.php file:

Code: Select all

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 = 'sysloguser';
        $syslogdb_password = '4q45upp0rT';
        $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';


#/* field in the incomming table */
#$syslog_incoming_config['dateField']          = 'ReceivedAt';
#$syslog_incoming_config['timeField']          = 'ReceivedAt';
#$syslog_incoming_config['priorityField']      = 'Priority';
#$syslog_incoming_config['facilityField']      = 'Facility';
#$syslog_incoming_config['hostField']          = 'FromHost';
#$syslog_incoming_config['textField']          = 'Message';
#$syslog_incoming_config['id']                 = 'ID';
The commented out config at the bottom was because I couldn't find the original field names ('message', 'seq' etc):

mysql> describe SystemEvents;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| CustomerID | bigint(20) | YES | | NULL | |
| ReceivedAt | datetime | YES | | NULL | |
| DeviceReportedTime | datetime | YES | | NULL | |
| Facility | smallint(6) | YES | | NULL | |
| Priority | smallint(6) | YES | | NULL | |
| FromHost | varchar(60) | YES | | NULL | |
| Message | text | YES | | NULL | |
| NTSeverity | int(11) | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
| EventSource | varchar(60) | YES | | NULL | |
| EventUser | varchar(60) | YES | | NULL | |
| EventCategory | int(11) | YES | | NULL | |
| EventID | int(11) | YES | | NULL | |
| EventBinaryData | text | YES | | NULL | |
| MaxAvailable | int(11) | YES | | NULL | |
| CurrUsage | int(11) | YES | | NULL | |
| MinUsage | int(11) | YES | | NULL | |
| MaxUsage | int(11) | YES | | NULL | |
| InfoUnitID | int(11) | YES | | NULL | |
| SysLogTag | varchar(60) | YES | | NULL | |
| EventLogType | varchar(60) | YES | | NULL | |
| GenericFileName | varchar(60) | YES | | NULL | |
| SystemID | int(11) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)


I attempted this also with Syslog-ng, but I couldn't get that to write to the database :-s

Any light that could be shed on this would be greatly appreciated.

Cheers guys,

Peter

itkroplis
Posts: 17
Joined: Sun Jun 04, 2017 6:25 am

Re: Syslog Plugin Configuration Errors

#2 Post by itkroplis » Sun Oct 29, 2017 4:19 am

OLd CACTI+syslog server (Debian8+MySql 5.5) all work well!

mysql> use syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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 |
+------------------------+
13 rows in set (0.00 sec)

==========================================
New Test Cacti+Syslog (Debian9+Mysql_5.7)

mysql> use syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+------------------------+
| Tables_in_syslog |
+------------------------+
| syslog_alert |
| syslog_facilities |
| syslog_host_facilities |
| syslog_hosts |
| syslog_incoming |
| syslog_priorities |
| syslog_programs |
| syslog_remove |
| syslog_reports |
| syslog_statistics |
+------------------------+
10 rows in set (0.00 sec)
===========================================
Some tables are missing!

I backup syslog DB from Old goods syslog server and restore to New test syslog server. After this, work well!
incomprehensible that disturbs!

Post Reply