Syslog 2.1 Removal Rules Not Work

[optimuscream]

Hi,

I have cacti 1.1.28 and syslog 2.1 installed.

Syslog message and alert mail work well with SQL String Match Type.

Only problem is Removal Rules filter with SQL not really work.

I try to describe as best as I can below :

My first filter is :

Code: Select all

host='ip.add.re.ss'

Second filter is :

Code: Select all

host_id='622'

What puzzled me is the error message from clog :

Error first filter :

Code: Select all

2017/12/22 11:45:46 - CMDPHP ERROR: A DB Exec Failed!, Error: Unknown column 'host' in 'where clause'
2017/12/22 11:45:46 - DBCALL ERROR: A DB Exec Failed!, Error:1054, SQL:'DELETE FROM `syslognew`.`syslog` WHERE host='ip.add.re.ss'' 

Error second filter :

Code: Select all

2017/12/22 12:25:02 - CMDPHP ERROR: A DB Exec Failed!, Error: Unknown column 'host_id' in 'where clause'
2017/12/22 12:25:02 - DBCALL ERROR: A DB Exec Failed!, Error:1054, SQL:'DELETE FROM `syslognew`.`syslog_incoming` WHERE (host_id='622') AND status='83''

From the error message is clear that column host and host_id was searched from different tables.

host_id SHOULD BE in syslognew.syslog
host SHOULD BE in syslognew.syslog_incoming

and that how the error come from (I guess) because Removal Rules code search the column host and host_id in wrong tables.
I try to find the code in syslog_removal.php but I'm not a coder.

Or maybe my syslog tables is in wrong order ?
But as stated above , my syslog message log is working well and also email alerting.

Code: Select all

syslog_incoming
Column	Type	Comment
facility_id	int(10) unsigned NULL	 
priority_id	int(10) unsigned NULL	 
program	varchar(40) NULL	 
date	date NULL	 
time	time NULL	 
host	varchar(64) NULL	 
message	varchar(1024) []	 
seq	bigint(20) unsigned Auto Increment	 
status	tinyint(4) [0]	 
facility	text NULL	 
priority	text NULL

Code: Select all

syslog
Column	Type	Comment
facility_id	int(10) unsigned NULL	 
priority_id	int(10) unsigned NULL	 
program_id	int(10) unsigned NULL	 
host_id	int(10) unsigned NULL	 
logtime	datetime [0000-00-00 00:00:00]	 
message	varchar(1024) []	 
seq	bigint(20) unsigned Auto Increment	  

Thanks for any respons.

Regards

Update :

I made workaround and somehow it work now.
But syslog itself is very slow compared to old syslog. Query on page took around 15 - 30 seconds to show. I have a huge syslog message from two syslog servers.
But it works

[netniV]

Try enabling the MySQL slow query logs and it may tell you which queries are taking the time. It may be that a key is needed against one specific query.

[Pucho]

When you ran the installation process, did you select InnoDB and partitioned tables?

[netniV]

I'm confused by my previous response here given the first post. It seems out of line with the question entirely. I have never installed the syslog plugin so far so I'm going to have to do that and then take a look at the removal rules.

[netniV]

I've had a glance over the code and it looks to be right for the host_id. I did notice that the syslog plugin doesn't have a recent release tagged against it so I've now put one on it. Can you download that latest release and then see if the issues are resolved?

https://github.com/Cacti/plugin_syslog/ ... s/tag/v2.1

[optimuscream]

Hi,

Thanks for the response, haven't see this thread for long time.
I will try the udpate and report the result.

Best regards.

[optimuscream]

It works

Code: Select all

2018/04/05 16:05:38 - SYSTEM SYSLOG STATS:Time:35.74 Deletes:0 Incoming:10746 Removes:1848 XFers:0 Alerts:22 Alarms:48 Reports:0
2018/04/05 16:10:01 - SYSTEM STATS: Time:0.1204 Method:spine Processes:1 Threads:1 Hosts:0 HostsPerProcess:0 DataSources:0 RRDsProcessed:0

The log is clean now.

FYI I use rsyslog to capture the syslog packet.
the relevant part that need update (I use Ubuntu) :

/etc/rsyslog.d/cacti.conf

Code: Select all

$ModLoad imudp
$UDPServerRun 514
$ModLoad ommysql

$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, date, time, host, message) values (%syslogfacility%, %syslogpriority%, '%programname%', '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', TRIM('%msg%'))", SQL

*.* :ommysql:dbhost,dbname,dbuser,dbpass;cacti_syslog  <------- Here 

And don't forget to change the line in : cactidir/plugins/syslog/config.php

Code: Select all

/* revert if you dont use the Cacti database */
$use_cacti_db = false;

To false if use another host for database , I stuck a long time in this part.

Hope it helps other.
Regards