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`"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';
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