Remove bogus value from databse

[gigatec]

Hi all,

yesterday I had a failover of my cisco firewall cluster resulting in a bogus 'traffic peak' of 600 GBit for one metering point recorded by Zabbix.

That wrong value kills the longterm chart (you only see the peak...), so is there any way to remove (or at least lower) the wrong value directly in the databse?

Thanks for any hints!
Stephan

[Rataplan626]

I think your only option is to remove it from the database manually. I'm no SQL expert but I use the following query for myself:

Select * from history where itemid = (SELECT items.itemid from items JOIN hosts on items.hostid = hosts.hostid where hosts.name = '<HOSTNAME>' and items.name = '<ITEMNAME>')

You could add: and history.clock > <some time> and history.clock < <some other value>
or something like >specific value you want to filter out. You could than delete the value or better, update it with lower value that doesn't 'corrupt' your graphs.

[bbs2web]

Remove bogus value from databse

This was extremely useful. We changed the SNMP OID from 'IF-MIB::ifInOctets.{#SNMPINDEX}' to 'IF-MIB::ifHCInOctets.{#SNMPINDEX}' to use 64 bit counters on the item prototypes entry associated with the interface discovery section of the general SNMP interface template. This resulted in the next value being recorded incorrectly (assumes a counter roll over).

The following are samples whereby we identified the invalid readings:

  SELECT * FROM history_uint WHERE itemid IN (SELECT items.itemid FROM items JOIN hosts ON items.hostid = hosts.hostid WHERE hosts.name='switch3.hosting.company.net' AND items.name LIKE '% traf. on int. $1' AND key_ LIKE '%Port 1 on Unit 1%') AND history_uint.clock > UNIX_TIMESTAMP("2016-04-04 12:50:00") AND history_uint.clock < UNIX_TIMESTAMP("2016-04-04 13:10:00");
  SELECT * FROM history_uint WHERE itemid IN (SELECT items.itemid FROM items JOIN hosts ON items.hostid = hosts.hostid WHERE items.name LIKE '% traf. on int. $1') AND history_uint.clock > UNIX_TIMESTAMP("2016-04-04 12:50:00") AND history_uint.clock < UNIX_TIMESTAMP("2016-04-04 13:10:00") AND history_uint.value > 500000000;
  SELECT * FROM history_uint WHERE itemid IN (SELECT items.itemid FROM items JOIN hosts ON items.hostid = hosts.hostid WHERE items.name LIKE '% traffic on interface $1') AND history_uint.clock > UNIX_TIMESTAMP("2016-04-04 12:50:00") AND history_uint.clock < UNIX_TIMESTAMP("2016-04-04 13:10:00") AND history_uint.value > 500000000;

We could then remove the invalid entries using the following commands:

  DELETE FROM history_uint WHERE itemid IN (SELECT items.itemid FROM items JOIN hosts ON items.hostid = hosts.hostid WHERE items.name LIKE '% traf. on int. $1') AND history_uint.clock > UNIX_TIMESTAMP("2016-04-04 12:50:00") AND history_uint.clock < UNIX_TIMESTAMP("2016-04-04 13:10:00") AND history_uint.value > 500000000;
  DELETE FROM history_uint WHERE itemid IN (SELECT items.itemid FROM items JOIN hosts ON items.hostid = hosts.hostid WHERE items.name LIKE '% traffic on interface $1') AND history_uint.clock > UNIX_TIMESTAMP("2016-04-04 12:50:00") AND history_uint.clock < UNIX_TIMESTAMP("2016-04-04 13:10:00") AND history_uint.value > 500000000;