Availability Report - SQL Statement

Hi everybody,

currently I’m trying to build an SQL Statement / View / Function that could show the following information:

Hostname, Servicename, % Ok, % Warning Handled, % Warning Unhandeld, % Critical Handled, Critical Unhandeld, % Unknown Handled, % Unknown Unhandled
MyHost, CPU, 75, 10, 0, 0, 10, 5, 0

For a day or a month or a given timeframe or something.

What I did so far:

Get all the statechanges of a specific timeframe:

select 
   icinga_statehistory.state_time,
   icinga_hosts.display_name as host,
   icinga_services.display_name as service,
   icinga_statehistory.state,
   icinga_statehistory.last_state,
   icinga_statehistory.object_id
from
   icinga_hostgroups,
   icinga_hostgroup_members,
   icinga_hosts,
   icinga_services,
   icinga_statehistory
where 
 	        icinga_statehistory.state_time > "2018-08-05 00:00:00"
and 	icinga_statehistory.state_time < "2018-08-06 00:00:00"
and     icinga_hostgroups.hostgroup_id = icinga_hostgroup_members.hostgroup_id
and     icinga_hostgroup_members.host_object_id = icinga_services.host_object_id
and     icinga_hostgroup_members.host_object_id = icinga_hosts.host_object_id
and     ( icinga_statehistory.object_id = icinga_hostgroup_members.hostgroup_member_id
   or     icinga_statehistory.object_id = icinga_services.service_object_id
        )

Get all the Downtimes for that time:

select 
    scheduled_start_time,
    scheduled_end_time,
    object_id
from 
    icinga_downtimehistory
where
    scheduled_end_time > "2018-08-05 00:00:00"
 and scheduled_start_time < "2018-08-06 00:00:00"

Getting the Acklowedgements shouldn’t be a big deal either.

Of cause I tried using the icinga_availability function in the SQL schema but it only shows the percentage of OK state and not the other information. Does anybody has implemented such a report in his Icinga2 Instance?

Thank you for any ideas and hints!

1 Like

i’m using sql + grafana for GUI based reports.

https://monitoring-portal.org/t/how-do-you-do-availability-reports-with-icingaweb2/3285/3?u=josy1024