When filtering, syslog incorrectly thinks the Cacti `hosts` table does not exist

[stevemorgan83]

I'm using the latest CentOS 8, Cacti 1.2.8 and Syslog 2.5.

cacti.log

2019/12/30 17:05:50 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog.php[101]:syslog_messages(), /plugins/syslog/syslog.php[1534]:syslog_filter(), /plugins/syslog/syslog.php[1327]:syslog_db_fetch_assoc(), /plugins/syslog/database.php[106]:db_fetch_assoc(), /lib/database.php[452]:db_fetch_assoc_prepared(), /lib/database.php[466]:db_execute_prepared())
2019/12/30 17:05:50 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'syslog.host' doesn't exist
2019/12/30 17:05:48 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog.php[101]:syslog_messages(), /plugins/syslog/syslog.php[1534]:syslog_filter(), /plugins/syslog/syslog.php[1327]:syslog_db_fetch_assoc(), /plugins/syslog/database.php[106]:db_fetch_assoc(), /lib/database.php[452]:db_fetch_assoc_prepared(), /lib/database.php[466]:db_execute_prepared())
2019/12/30 17:05:48 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'syslog.host' doesn't exist
2019/12/30 17:05:44 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog.php[101]:syslog_messages(), /plugins/syslog/syslog.php[1534]:syslog_filter(), /plugins/syslog/syslog.php[1327]:syslog_db_fetch_assoc(), /plugins/syslog/database.php[106]:db_fetch_assoc(), /lib/database.php[452]:db_fetch_assoc_prepared(), /lib/database.php[466]:db_execute_prepared())
2019/12/30 17:05:44 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'syslog.host' doesn't exist

mysql:

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 |
+------------------------+
13 rows in set (0.000 sec)

MariaDB [syslog]> select * from syslog_hosts;
+---------+---------------------------+---------------------+
| host_id | host | last_updated |
+---------+---------------------------+---------------------+
| 3 | 10.254.243.19 | 2019-12-30 16:54:04 |
| 6 | 10.254.243.20 | 2019-12-30 16:54:04 |
| 1 | 10.254.243.45 | 2019-12-30 16:54:04 |
| 2 | 10.254.243.49 | 2019-12-30 16:54:04 |
| 4 | Upper-Building-Back- | 2019-12-30 16:49:49 |
| 5 | work | 2019-12-30 16:54:04 |
+---------+---------------------------+---------------------+
6 rows in set (0.000 sec)

MariaDB [syslog]> select * from syslog;
+-------------+-------------+------------+---------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----+
| facility_id | priority_id | program_id | host_id | logtime | message | seq |
+-------------+-------------+------------+---------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----+
| 21 | 6 | 1 | 6 | 0000-00-00 00:00:00 | - - state_table_event; table=NTP; Event=Clock Updated; | 294 |
| 21 | 6 | 1 | 1 | 0000-00-00 00:00:00 | - - state_table_event; table=NTP; Event=Clock Updated; | 295 |

[stevemorgan83]

I cloned the latest syslog develop branch and I get the same error.

When I run syslog_process.php in debug it looks like its deleting the logs because of how old they are.

[root@work cacti]# sudo -u apache /usr/bin/php /var/www/html/cacti/plugins/syslog/syslog_process.php --debug
SYSLOG: Syslog Table is NOT Partitioned
SYSLOG: Deleted 4, Syslog Message(s) (older than 2018-12-31)
SYSLOG: Unique ID = 26
SYSLOG: Found 47, New Message(s) to process
SYSLOG: Found 0, Removal Rule(s) to process
SYSLOG: Found 0, Alert Rules to process
SYSLOG: Moved 47, Message(s) to the 'syslog' table
SYSLOG: Deleted 47, Already Processed Message(s) from incoming
SYSLOG: Deleted 0, Syslog alarm log Record(s)
SYSLOG: Deleted 0, Syslog Host Record(s)
SYSLOG: Deleted 0, Old programs from programs table
SYSLOG: Deleted 0, Syslog Host/Facility Record(s)
SYSLOG: Processing Reports...
SYSLOG: We have 0 Reports in the database
SYSLOG: Finished processing Reports...
12/31/2019 01:16:22 - SYSTEM SYSLOG STATS: Time:0.02 Deletes:4 Incoming:47 Removes:0 XFers:47 Alerts:0 Alarms:0 Reports:0

MariaDB [syslog]> select * from syslog_incoming;
+-------------+-------------+---------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+--------+
| facility_id | priority_id | program | logtime | host | message | seq | status |
+-------------+-------------+---------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+--------+
| 10 | 5 | sudo | 0000-00-00 00:00:00 | work | root : TTY=pts/0 ; PWD=/var/www/html/cacti ; USER=apache ; COMMAND=/usr/bin/php /var/www/html/cacti/plugins/syslog/syslog_process.php --debug | 1185 | 0 |
| 10 | 7 | sudo | 0000-00-00 00:00:00 | work | pam_systemd(sudo:session): Cannot create session: Already running in a session or user slice | 1186 | 0 |
| 10 | 6 | sudo | 0000-00-00 00:00:00 | work | pam_unix(sudo:session): session opened for user apache by (uid=0) | 1187 | 0 |
| 10 | 6 | sudo | 0000-00-00 00:00:00 | work | pam_unix(sudo:session): session closed for user apache | 1188 | 0 |
+-------------+-------------+---------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+--------+
4 rows in set (0.000 sec)

If I install syslog with the option to hold onto my logs indefinitely and go to filter I can see the logs if I select a custom time span starting from "0000-00-00 00:00:00". Also, the devices doesn't show any hosts under it. I'm guessing because of the missing table. What am I missing?

Here is my rsyslog.d/cacti.conf

$ModLoad ommysql
$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, logtime, host, message)
values (%syslogfacility%, %syslogpriority%, '%programname%', '%timegenerated%', '%HOSTNAME%', TRIM('%msg%'))", SQL
. >localhost,syslog,cacti,password;cacti_syslog

[TheWitness]

Do, the following; switch to the plugins directory and run the following command:

grep -r syslog_hosts_where *

See if you find anything in another plugin. You might have a plugin that wrote a custom hook against some of the old table names and it simply needs updating.

There is no where in the current plugin where there is a table reference to 'syslog.host'. So, it' has to be another plugin.

[stevemorgan83]

[root@work plugins]# grep -r syslog_hosts_where *
syslog/syslog.php: $hosts_where = api_plugin_hook_function('syslog_hosts_where', $hosts_where);

This is a fresh install of Cacti 1.2.8. The first thing I did after installation was install syslog from dev branch. It looks like I have two issues. The rsyslog template for mysql is not putting the correct time stamp in the database and the second being the syslog.host reference. Do you know what's wrong with the database showing "0000-00-00 00:00:00"? Also, do you think the reference to the unknown table may have something to do with the devices not being populated under devices?

I was able to get the dates to populate correctly in the mysql database by using the following template for rsyslog (8.37.0)

$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, logtime, host, message) values (%syslogfacility%, %syslogpriority%, '%programname%', '%$year%-%$month%-%$day% %timegenerated:12:19:date-rfc3339%', '%HOSTNAME%', TRIM('%msg%'))", SQL

[netniV]

Actually, I think the problem here is that the hosts table isn't prefixed with the $database_default and therefore is using the syslog database that you have created separately for syslog data. Therefore, I've applied a fix based upon that.

[stevemorgan83]

The devices filter is now working properly. When running the syslog_process in debug it does give me a few notices.

[root@work cacti]# sudo -u apache /usr/bin/php /var/www/html/cacti/plugins/syslog/syslog_process.php --debug
SYSLOG: Syslog Table is NOT Partitioned
PHP Notice: Undefined variable: retention in /var/www/html/cacti/plugins/syslog/functions.php on line 85
PHP Notice: Undefined variable: retention in /var/www/html/cacti/plugins/syslog/functions.php on line 90
PHP Notice: Undefined variable: retention in /var/www/html/cacti/plugins/syslog/functions.php on line 96
SYSLOG: Deleted 0, Syslog Message(s) (older than )
SYSLOG: Unique ID = 87
SYSLOG: Found 136, New Message(s) to process
SYSLOG: Found 0, Removal Rule(s) to process
SYSLOG: Found 0, Alert Rules to process
SYSLOG: Moved 136, Message(s) to the 'syslog' table
SYSLOG: Deleted 136, Already Processed Message(s) from incoming
SYSLOG: Deleted 0, Syslog alarm log Record(s)
SYSLOG: Deleted 0, Syslog Host Record(s)
SYSLOG: Deleted 0, Old programs from programs table
SYSLOG: Deleted 0, Syslog Host/Facility Record(s)
SYSLOG: Processing Reports...
SYSLOG: We have 0 Reports in the database
SYSLOG: Finished processing Reports...
01/02/2020 11:20:32 - SYSTEM SYSLOG STATS: Time:0.06 Deletes:0 Incoming:136 Removes:0 XFers:136 Alerts:0 Alarms:0 Reports:0

Cacti log now showing:

01/02/2020 11:27:24 - CMDPHP ERROR: A DB Cell Failed!, Error: Table 'cacti.syslog_hosts' doesn't exist
01/02/2020 11:27:24 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog.php[101]:syslog_messages(), /plugins/syslog/syslog.php[1532]:get_syslog_messages(), /plugins/syslog/syslog.php[837]:sql_hosts_where(), /plugins/syslog/functions.php[591]:db_fetch_cell_prepared(), /lib/database.php[378]:db_execute_prepared())
01/02/2020 11:27:24 - CMDPHP ERROR: A DB Cell Failed!, Error: Table 'cacti.syslog_hosts' doesn't exist
01/02/2020 11:26:24 - SYSTEM SYSLOG STATS: Time:0.11 Deletes:0 Incoming:169 Removes:0 XFers:169 Alerts:0 Alarms:0 Reports:0
01/02/2020 11:26:24 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[158]:syslog_traditional_manage(), /plugins/syslog/functions.php[96]:CactiErrorHandler())
01/02/2020 11:26:24 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: retention in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 96
01/02/2020 11:26:24 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[158]:syslog_traditional_manage(), /plugins/syslog/functions.php[90]:CactiErrorHandler())
01/02/2020 11:26:24 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: retention in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 90
01/02/2020 11:26:24 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[158]:syslog_traditional_manage(), /plugins/syslog/functions.php[85]:CactiErrorHandler())
01/02/2020 11:26:24 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: retention in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 85

[netniV]

Can you log those as two new errors, since the first one has now been resolved it appears.

[jotate-git]

it appears the issue isnt resolved, i updated today; deleted syslog plugin dir, complete removal of plugin, and reinstall. I am running syslog in its own independent database.

2020/02/22 10:55:48 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog.php[101]:syslog_messages(), /plugins/syslog/syslog.php[1532]:syslog_filter(), /plugins/syslog/syslog.php[1326]:syslog_db_fetch_assoc(), /plugins/syslog/database.php[106]:db_fetch_assoc(), /lib/database.php[452]:db_fetch_assoc_prepared(), /lib/database.php[466]:db_execute_prepared())

2020/02/22 10:55:48 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'syslog.host' doesn't exist