IcingaDB data is not up to date with the data displayed in Icingaweb

vutuong · July 25, 2024, 8:13am

I have IcingaDB running, but now I need to read and filter some data from service_state in IcingaDB. I realized that the data is not up to date with the data displayed in Icingaweb when using this query:
SELECT t1.name AS host_name, t1.address, t2.name AS servicename, t3.hard_state, FROM_UNIXTIME(t3.last_update/1000) AS last_update_readable FROM host t1 JOIN service t2 ON t1.id = t2.host_id JOIN service_state t3 ON t2.id = t3.service_id WHERE t2.name = 'Red_Alarm' limit 3;
I noticed that the last_update time is much older compared to the time shown on the web interface. Why is this happening? What is the sync interval between IcingaDB and Redis? The data in Redis seems to be up to date compared to the web but because the key is hash so I can not query to redis with filter that I want.

moreamazingnick · July 25, 2024, 8:56am

the reason for this is, that the most recent non hard state change information is stored in the redis server only.

The sql database hold the hard state relevant for “reboot consistant” sla calculation.

It is quite easy to implement something like this query in php as an icingaweb2 module.
Inside icingeweb2 you can easyily combine the redis data with the sql data.

github.com

RISE-GmbH/icingaweb2-module-checkreports/blob/d1a1ff68dbe3d69aa8cb73a43f85afbe316c2cf7/library/Checkreports/ProvidedHook/Reporting/Icingadb/ServiceCheckReport.php#L62


      
          protected function fetchChecks($config, $filter = null)
          {
          
              $query = Service::on($this->getDb())->with([
                  'state',
                  'icon_image',
                  'host',
                  'host.state'
              ]);
          
              $query
                  ->setResultSetClass(VolatileStateResults::class); #to get redis data
              if ($filter !== null) {
                  $query->filter(Filter::all(
                      Filter::any($filter)
                  ));
              }
          
              $query->resetOrderBy()->orderBy('host.display_name')->orderBy('display_name');
          
              $this->applyRestrictions($query);