Cleaning Icingaweb2 Service Check history

Icinga Web
, ,
1

Hello Community,

how can i delete history entries older than x for all service check results?

Icinga Web 2 Version 2.7.0
Icinga2 r2.10.5-1

greetings

2

Hi,

the monitoring module uses the IDO database resource, and as such, you need to look into the DB schema for such operations. Prior before deleting anything, create a backup first.

There’s also the possibility to define the cleanup attribute for the IDO feature as well, but this may become a problem with huge sets of data blocking normal operations.

In terms of a manual cleanup, you’ll need several things:

  • The object_id from icinga_objects where name1=hostname name2=servicename
  • The datetime column which should be compared against a unix timestamp / datetime
  • The affected tables like icinga_statehistory, icinga_downtimehistory, icinga_notifications

Objects

Hosts

MariaDB [icinga]> select * from icinga_objects o where o.objecttype_id=1 and o.name1='master1';
+-----------+-------------+---------------+---------+-------+-----------+
| object_id | instance_id | objecttype_id | name1   | name2 | is_active |
+-----------+-------------+---------------+---------+-------+-----------+
|       331 |           1 |             1 | master1 | NULL  |         0 |
+-----------+-------------+---------------+---------+-------+-----------+
1 row in set (0.001 sec)

Services

MariaDB [icinga]> select * from icinga_objects o where o.objecttype_id=2 and o.name1='master1' and o.name2='disk';
+-----------+-------------+---------------+---------+-------+-----------+
| object_id | instance_id | objecttype_id | name1   | name2 | is_active |
+-----------+-------------+---------------+---------+-------+-----------+
|       334 |           1 |             2 | master1 | disk  |         0 |
+-----------+-------------+---------------+---------+-------+-----------+
1 row in set (0.004 sec)

Date as timestamp

Get a Unix timestamp.

MariaDB [icinga]> SELECT UNIX_TIMESTAMP('2019-08-31 00:00:00') AS res;
+------------+
| res        |
+------------+
| 1567202400 |
+------------+
1 row in set (0.000 sec)

Datetime column

Here’s a list in the code of the table names and their respective column used by the IDO feature cleanup.

First, select all the entries to see whether the delete query would actually delete something.

In this example, the object_id is used from the example above without any joins (not possible with delete later).

MariaDB [icinga]> select * from icinga_statehistory where object_id=334 and state_time < FROM_UNIXTIME(1567202400) limit 10;

Based on that, you can build a delete statement.

MariaDB [icinga]> delete from icinga_statehistory where object_id=334 and state_time < FROM_UNIXTIME(1567202400) limit 10;

Conclusio

This requires queries for all the historical tables in the schema. With the knowledge above, one can build custom cleanup script run e.g. every day via a cron, or on demand.

Cheers,
Michael

3

hello Michael,

thanks for the great explanation!
it works for a single host and check right?
i used it and i deleted the service history for one host.
can i clear the service history for all hosts where i use this service?

4

If you first select all the matching object ids and then pass this into the DELETE query, sure.

AFAIK that’s done like

DELETE FROM bla WHERE .... object_id IN (1, 2, 3)

Maybe also possible with a combined sub select delete query, but that’s something I’m not an expert in.

Cheers,
Michael

5

okay i will try! Thanks :slight_smile:

6

it works very well!
i used this to delete all older downtimes too

select downtimehistory_id from icinga_downtimehistory;
select * from icinga_downtimehistory where downtimehistory_id IN (124, 778, 1275, 1913…)
delete from icinga_downtimehistory where downtimehistory_id IN (124, 778, 1275, 1913…)

1 Like
7

Would it be safe to do this for a lot of arbitrary object_ids? Because I have a large database that is just too slow and contains too much history that isn’t needed.