Filter on program name is not correct, same about hostname

[arno-st]

Hi,
latest dev version and cacti 1.2.27.

On console -> syslog Settings -> Alert Rules.
I try to make a filter for an alert based on a program id, who actually is a name But the syntax of the SQL query is expecting a number not a string, and this give me this error:

Note, we could not process all your actions. Details are below.

The SQL Syntax Entered is invalid. Please correct your SQL.

The Pre-processed SQL is:

SELECT * FROM `syslog_test`.`syslog_incoming` WHERE `program` = mariadbd AND `status` = 100100

The issue is the missing quote on mariadb (who is my filter name).

It's the same about the hostname, where you expect a host_id, instead of the hostname (hostname or description will be better, than a host_id).

Note, we could not process all your actions. Details are below.

The SQL Syntax Entered is invalid. Please correct your SQL.

The Pre-processed SQL is:

SELECT * FROM `syslog_test`.`syslog_incoming` WHERE `host_id` = lslmysp11 AND `status` = 100100

[jdcoats]

there is no host_id in syslog_incoming use host column

[arno-st]

Good catch
So it has to be review, because the table syslog is with program_id and host_id
should be the same to avoid any confusion.

[arno-st]

Any planed evolution on that ?

[bmfmancini]

I dont think we would add a host_id in the incoming table as that is the processing table
@TheWitness what do you think ?

[TheWitness]

The host_id would make it faster than it is today. Today, you can zoom to syslog, but it reliability is in question. The algo can be improved.

[TheWitness]

In the syslog_hosts table. Is that where it is presently?

[arno-st]

In syslog_hosts, yes the host_id is present.

But the main problem is that on the alert rules, that I display on my first message, is wrong.
It's trying to create a SQL query with a field host_id that doesn't exit.

But also on the same query, it's missing the ' (single cote) for the user field host_id and program.
so the query is looking for a number and not a string.

The query is:
SELECT * FROM syslog_test.syslog_incoming WHERE host_id = lslmysp11 AND status = 100100

should be:
SELECT * FROM syslog_test.syslog_incoming WHERE host_id = 'lslmysp11' AND status = 100100
And of curse the field host_id is wrong it should be host

And when using the field program, the query is the same, missing the (single cote) the query is: SELECT * FROM syslog_test.syslog_incomingWHEREprogram= mariadbd ANDstatus` = 100100

and should be (look near the mariadb):
SELECT * FROM syslog_test.syslog_incoming WHERE program = mariadbd AND status = 100100

Hope that is clear for you.

[TheWitness]

Yea, the table structure is:

MariaDB [cacti]> desc syslog_incoming;
+-------------+---------------------+------+-----+---------------------+----------------+
| Field       | Type                | Null | Key | Default             | Extra          |
+-------------+---------------------+------+-----+---------------------+----------------+
| facility_id | int(10) unsigned    | YES  |     | NULL                |                |
| priority_id | int(10) unsigned    | YES  |     | NULL                |                |
| program     | varchar(40)         | YES  | MUL | NULL                |                |
| logtime     | timestamp           | NO   |     | 0000-00-00 00:00:00 |                |
| host        | varchar(64)         | YES  |     | NULL                |                |
| message     | varchar(2048)       | NO   |     |                     |                |
| seq         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| status      | tinyint(4)          | NO   | MUL | 0                   |                |
+-------------+---------------------+------+-----+---------------------+----------------+
8 rows in set (0.001 sec)