[SYSLOG] latest source - develop branch - SQL expression errors

[anarkia1976]

Hi @TheWitness ,

I tried to use the "sql expression" function in the "match type" section but unfortunately when an alarm arrives and the rules are activated, a series of PHP errors are encountered and the "syslog plugin" is disabled by informing the cacti administrator.

Example: SELECT * FROM syslog_incoming WHERE message REGEXP 'EXAMPLE';

2022-03-28 09:52:29 - CMDPHP ERRORS DETECTED - DISABLING PLUGIN 'syslog'
2022-03-28 09:52:29 - CMDPHP PHP ERROR Backtrace: (CactiShutdownHandler())
2022-03-28 09:52:29 - ERROR PHP ERROR in Plugin 'syslog': Uncaught Error: Call to undefined function syslog_db_fetch_assoc() in /var/www/html/cacti/plugins/syslog/syslog.php:1051 Stack trace: #0 /var/www/html/cacti/plugins/syslog/syslog.php(1721): get_syslog_messages() #1 /var/www/html/cacti/plugins/syslog/syslog.php(102): syslog_messages() #2 {main} thrown in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1051
2022-03-28 09:52:29 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[1019]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: syslogdb_default in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1019
2022-03-28 09:52:29 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[1018]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: syslogdb_default in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1018
2022-03-28 09:52:29 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[1014]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: syslogdb_default in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1014
2022-03-28 09:52:29 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[1013]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP NOTICE in Plugin 'syslog': Undefined variable: syslogdb_default in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1013
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[867]:include(), /plugins/syslog/syslog.php[867]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': include(): Failed opening 'SYSLOG_CONFIG' for inclusion (include_path='.:/usr/share/php') in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 867
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[867]:include(), /plugins/syslog/syslog.php[867]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': include(SYSLOG_CONFIG): failed to open stream: No such file or directory in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 867
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1721]:get_syslog_messages(), /plugins/syslog/syslog.php[867]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': Use of undefined constant SYSLOG_CONFIG - assumed 'SYSLOG_CONFIG' (this will throw an Error in a future version of PHP) in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 867
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1700]:include(), /plugins/syslog/syslog.php[1700]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': include(): Failed opening 'SYSLOG_CONFIG' for inclusion (include_path='.:/usr/share/php') in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1700
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1700]:include(), /plugins/syslog/syslog.php[1700]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': include(SYSLOG_CONFIG): failed to open stream: No such file or directory in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1700
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[102]:syslog_messages(), /plugins/syslog/syslog.php[1700]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': Use of undefined constant SYSLOG_CONFIG - assumed 'SYSLOG_CONFIG' (this will throw an Error in a future version of PHP) in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 1700
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[37]:include(), /plugins/syslog/syslog.php[37]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': include(): Failed opening 'SYSLOG_CONFIG' for inclusion (include_path='.:/usr/share/php') in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 37
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[37]:include(), /plugins/syslog/syslog.php[37]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': include(SYSLOG_CONFIG): failed to open stream: No such file or directory in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 37
2022-03-28 09:52:29 - CMDPHP PHP ERROR WARNING Backtrace: (/plugins/syslog/syslog.php[37]:CactiErrorHandler())
2022-03-28 09:52:29 - ERROR PHP WARNING in Plugin 'syslog': Use of undefined constant SYSLOG_CONFIG - assumed 'SYSLOG_CONFIG' (this will throw an Error in a future version of PHP) in file: /var/www/html/cacti/plugins/syslog/syslog.php on line: 37

thanks a lot

[TheWitness]

Making a slight change. Keep reporting.

[anarkia1976]

Hi @TheWitness , a lot of errors are fixed. I have a new one:

2022-03-28  15:05:30 - CMDPHP SQL Backtrace:   (/plugins/syslog/syslog_process.php[192]:syslog_process_alerts(),  /plugins/syslog/functions.php[1174]:syslog_process_alert(),  /plugins/syslog/functions.php[1258]:syslog_db_fetch_assoc_prepared(),  /plugins/syslog/database.php[167]:db_fetch_assoc_prepared(),  /lib/database.php[613]:db_execute_prepared())
2022-03-28 15:05:30 - CMDPHP ERROR: A DB Row Failed!, Error: You  have an error in your SQL syntax; check the manual that corresponds to  your MariaDB server version for the right syntax to use near ';) AND  `status` = '44'' at line 1

The syntax used for message match string is correct? SELECT * FROM syslog_incoming WHERE message REGEXP 'EXAMPLE';

thanks a lot

[TheWitness]

I'm going to put the SQL in the debug output.

[TheWitness]

Take a fresh copy first. I did some updates today.

[anarkia1976]

hi @TheWitness , unfortunally when i configure a rule with "sql expression" i have these errors and the rule isn't processed:

2022-03-29 10:50:40 - CMDPHP SQL Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1258]:syslog_db_fetch_assoc_prepared(), /plugins/syslog/database.php[167]:db_fetch_assoc_prepared(), /lib/database.php[613]:db_execute_prepared())
2022-03-29 10:50:40 - CMDPHP ERROR: A DB Row Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';) AND `status` = '22'' at line 1
2022-03-29 10:50:40 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1373]:CactiErrorHandler())
2022-03-29 10:50:40 - ERROR PHP NOTICE in Plugin 'syslog': Undefined index: message in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 1373
2022-03-29 10:50:40 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1373]:CactiErrorHandler())
2022-03-29 10:50:40 - ERROR PHP NOTICE in Plugin 'syslog': Undefined index: message in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 1373
2022-03-29 10:50:40 - MAILER INFO: Mail successfully sent via Sendmail from 'cacti.soc.monitoring <cacti.soc.monitoring@fwse.it>', to 'stefano.villa@fastweb.it', cc '', and took 0.01 seconds, Subject 'Event Alert - FORTIGATE - HA - FAILURE'
2022-03-29 10:50:40 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1373]:CactiErrorHandler())
2022-03-29 10:50:40 - ERROR PHP NOTICE in Plugin 'syslog': Undefined index: message in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 1373
2022-03-29 10:50:40 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1373]:CactiErrorHandler())
2022-03-29 10:50:40 - ERROR PHP NOTICE in Plugin 'syslog': Undefined index: message in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 1373
2022-03-29 10:50:40 - MAILER INFO: Mail successfully sent via Sendmail from 'cacti.soc.monitoring <cacti.soc.monitoring@fwse.it>', to 'stefano.villa@fastweb.it', cc '', and took 0.01 seconds, Subject 'Event Alert - FORTIGATE - CONSERVE MODE'
2022-03-29 10:50:40 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1373]:CactiErrorHandler())
2022-03-29 10:50:40 - ERROR PHP NOTICE in Plugin 'syslog': Undefined index: message in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 1373
2022-03-29 10:50:40 - CMDPHP PHP ERROR NOTICE Backtrace: (/plugins/syslog/syslog_process.php[193]:syslog_process_alerts(), /plugins/syslog/functions.php[1174]:syslog_process_alert(), /plugins/syslog/functions.php[1373]:CactiErrorHandler())
2022-03-29 10:50:40 - ERROR PHP NOTICE in Plugin 'syslog': Undefined index: message in file: /var/www/html/cacti/plugins/syslog/functions.php on line: 1373

thanks a lot

[TheWitness]

Yea, I'm doing some of this blind. Thanks for testing.

[TheWitness]

Try again. This one latest one should be resolved.

[TheWitness]

@anarkia1976, Also note that MySQL/MariaDB regular expressions don't exactly follow Perl regular expressions. Several operators and shortcuts are not supported. Maybe someone can help MySQL/MariaDB update them. If found that the lack of support for say character or string shortcuts being missing a bit annoying. Things like \s, \w, \d, etc. that were added apparently in Perl 5.18 or so are missing.

[anarkia1976]

@anarkia1976, Also note that MySQL/MariaDB regular expressions don't exactly follow Perl regular expressions. Several operators and shortcuts are not supported. Maybe someone can help MySQL/MariaDB update them. If found that the lack of support for say character or string shortcuts being missing a bit annoying. Things like \s, \w, \d, etc. that were added apparently in Perl 5.18 or so are missing.

Hi @TheWitness my msql regexp is very simple like this: SELECT * FROM syslog_incoming WHERE message REGEXP 'EXAMPLE'; do you have suggestions about syntax? thanks a lot

[TheWitness]

What is the exact regex in the form?

[anarkia1976]

SELECT * FROM syslog_incoming WHERE message REGEXP 'EXAMPLE';

What is the exact regex in the form?

@TheWitness i have found the culprint and the correct syntax:

SQL Match - Alert Rule: message regexp 'REALE|WIFI'

Now it is working without erros, matched both and alert via mail is working now.

thanks a lot

[TheWitness]

Yea, we have some regex test logic, but it's for a more recent version of the Perl Regex than what MySQL/MariaDB supports, so it would thing that your Regex is good when in fact, they won't support it. So, you have to search around.

So, in the form, did you quote the string, or leave the quote off?

[anarkia1976]

Yea, we have some regex test logic, but it's for a more recent version of the Perl Regex than what MySQL/MariaDB supports, so it would thing that your Regex is good when in fact, they won't support it. So, you have to search around.

So, in the form, did you quote the string, or leave the quote off?

This is exactly the string put in the alert rule
message regexp 'HOME|ROUTER|WIFI' and message regexp 'fgTrapHaMemberDown'

it's working a like a charm. thanks a lot @TheWitness

[TheWitness]

Okay, that's perfect, what I was expecting. I've added a Feature Request to provide immediate feedback when these types of filters are created BTW.

[TheWitness]

I just read this. Interesting. I'll have to give some thought at to how to accomplish this.

https://jira.mariadb.org/browse/MDEV-18523?jql=text%20~%20%22regexp%22