IcingaDB MySQL Database - host_state and service_state Tables Out of Sync?


I am testing out switching from Icinga IDO over to IcingaDB. Previously, with the Icinga IDO, we would execute queries for host and service state directly against the MySQL backend. With IcingaDB’s updated architecture and new backend relational database schema, we are trying to replicate the queries for the new IcingaDB database.

In particular, we have queries that look for hosts that have not been updated in the last 15 minutes, and services that have not been updated in the last 15 minutes.

In the Icinga IDO database, the query relied on the icinga_servicestatus and icinga_hoststatus tables, using the status_update_time column to filter for hosts and services.

Attempting to do a similar query against the icingadb database with the service_state and host_state tables using the last_update column to filter yields unexpected results. It appears that the last_update column only updates on initial load of the icinga2 service.

Is this behavior expected by design or is this a bug?

Give as much information as you can, e.g.

  • Icinga DB Web version (System - About): 1.0.1
  • Icinga Web 2 version (System - About): 2.11.1
  • Icinga 2 version (icinga2 --version): r2.13.5-1
  • Icinga DB version (icingadb --version): v1.0.0
  • PHP version used (php --version): 7.3.29 (for icinga web)
  • Server operating system and version: rhel 7.6

Icinga DB intentionally doesn’t update these rows for every check result processed but rather only on state changes so that you can filter for that on the SQL level. To get a full and up-to-date version of the data, Icinga DB Web then enriches this with the latest information from Redis.

1 Like

Thank you for the quick reply @jbrost!

How does icingadb-web apply filters for hosts and services against Redis? I see in Redis that host and service state are stored as hashes in icinga:host:state and icinga:service:state. Does Icingadb-web pull back all of the host states and service states and filter at the application level for search filters done on the web interface?

My goal is to get a list of host or services that have not been updated after 15 minutes by querying redis directly, but I’m not sure how to do that without RedisJson/RedisSearch. It seems that I would have to query for all the keys/values of icinga:host:state or icinga:service:state and parse the responses as json at the application level.

Not at all. Volatile information (what’s in Redis), like last_update, can’t be reliably filtered with Icinga DB Web. However, it does use volatile information to enrich what’s shown as result. Of course, this may occasionally lead to results not matching the filter.

If you really need the result to be 100% correct all the time, then this is your only chance.

1 Like

Would an API call like the following be accurate to retrieve all services that have not been checked within the last 15 minutes?


where 1667397957000 is the current unix time - 15 minutes