Deleting old data in MariaDB/InfluxDB for Icinga2/Grafana

Hi all,

I was just going through deleting some old data (development stuff, older devices that don’t exist anymore, etc.) from my Icinga2 DB (MariaDB) and was wondering if there was a “best practice” for doing this as there doesn’t seem to be mentions of DB management in the docs, other than the optimization/tuning stuff. So far, I’ve just been removing hosts from their relevant tables (icinga_hosts, icinga_objects, and icinga_endpoints, icinga_endpointstatus where applicable), but is there perhaps a different, better way I should be doing this?

I made a simple script to speed up this process:

USE icinga;

DELETE FROM icinga_hosts where alias = (@host); 
DELETE FROM icinga_objects where name1 = (@host);
DELETE FROM icinga_endpoints where identity = (@host); 
DELETE FROM icinga_endpointstatus where identity = (@host);

where @host is supplied in calling the script.

This question stems from my desire to delete old hosts/services from showing up in Grafana (which uses InfluxDB) because I’m under the impression that even if I delete data from InfluxDB, the same data is being resent from the Icinga2 DB, though now I’m not sure that I’m deleting data from InfluxDB correctly – if someone has insight on that too, it would be very helpful!

Here are the commands I tried for the InfluxDB data deletion:

DELETE FROM "<host>"
DROP SERIES FROM "<host>"
DROP MEASUREMENT "<host>"

then I restart influxd and influxdb, but the data seems to remain in my dashboards.

Thanks!

For influxdb i use this (you have to use the correct db first):

DELETE WHERE hostname = 'icinga2wk01.blubber.de'

or if you want to delete only a service from a specific host

DELETE WHERE hostname = 'icinga2wk01.blubber.de' AND service = 'Test Service'
2 Likes

Great, that works!

To clarify, I don’t need to delete worry about deleting the same stuff from the Icinga2 DB (MariaDB)? I thought that the InfluxDBWriter would just write anything it reads from the Icinga2 DB into InfluxDB, or is it only new data that it writes?

The docs say this for the InfluxDBWriter object:

Once there are new metrics available, Icinga 2 will directly write them to the defined InfluxDB HTTP API.

but I just want to clarify.

I never touched my MariaDB, but i clean old stuff with the ido-mysql object, more information here and a cronjob every weekend to optimze all tables.
IF you are doubtful, just make a backup :slight_smile:

1 Like

I think this is quite old… but I hope you can help me with two issues I have with my OMD/InfluxDB setup…

Issue 1:
Many of my checks are storage volumes with usage etc. information… one check does many volumes, so I get many graphs which I can select in Thruk… but the volumes are somewhat dynamic, so some volumes are deleted, and therefore not updated any longer… so is there a way to do a script that queries the InfluxDB and finds objects that has not been updated for say a month, and then deletes them or maybe renames them so that they are not shown in the Thruk view?

Issue 2:
Picture the same as above, but instead, the volumes are sometimes renamed… and Nagflux sees this as a new volume… so I end up with two graphs for the same volumes… is there a way to merge or rename an object so that it shows up as one graph again?

Any help is very welcome :slight_smile: