Jump to content
JasperReports Library 7.0 is now available ×

How does Jaspersoft calculate "downtimes" in "Availability report about a selected Host in given time"


delsner

Recommended Posts

Hi,

I'm searching for documentation about the Icinga/Reports/Hosts/"Availability report about a selected Host in a given time".

I do not understand how the data is evaluated, which tables are used and how is the "downtime" calculated?

I opend the report in Jaspersoft Studio and IReport to see the SQL-Queue but i dint find something usefull.

for example the queue from the "Availability report about a selected Host in given time"

Its a subreport :

select name1,  address,  service_object_id,  timestampdiff(second, $P{p_date_start}, $P{p_date_end}) as periodfrom icinga_objects,  icinga_hosts,  icinga_serviceswhere icinga_objects.object_id  = icinga_hosts.host_object_idand icinga_hosts.host_object_id = icinga_services.host_object_idand object_id                      = $P{p_host_object_id}[/code]

next is the queue from Mainreport:

select name1,  addressfrom icinga_objects,  icinga_hostswhere icinga_objects.object_id = icinga_hosts.host_object_idand icinga_hosts.config_type = 1and object_id                  = $P{p_host_object_id}[/code]

 

I just want to understand how Jaspersoft calculates the downtimes with the information from this few tabels. So I can make our own "availabiltyreport".

Best Regards Daniel

 

 

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

I found a sql query, which calculates the duration of the statechanges, fyi:

select sum(state_duration), state_one as state, state_two FROM (SELECT 0 AS state_duration, 0 AS state_one, 0 as state_two, @a:=statehistory_id as counter, 0 as last_time, 0 as last_state FROM icinga_statehistory WHERE instance_id = 1 AND state_type = 1 AND object_id = $P{p_service_object_id} AND state_time between $P{p_date_start} and $P{p_date_start} UNION -- -- select all statehistory entries from database and diff them with upcoming entry -- SELECT TIMESTAMPDIFF(SECOND, IF (sstart.state_time > $P{p_date_start} AND @A IS NULL OR sstart.state_time IS NULL, $P{p_date_start}, sstart.state_time), IF (send.state_time > $P{p_date_end} OR send.state_time IS NULL, $P{p_date_end}, send.state_time)) AS state_duration, IF(@A IS NULL, IFNULL( (SELECT state FROM icinga_statehistory WHERE instance_id = 1 AND state_type = 1 AND object_id = $P{p_service_object_id} AND state_time < $P{p_date_start} ORDER BY state_time DESC LIMIT 1 ), 0) , sstart.state) AS state_one, send.state AS state_two, @A:=sstart.statehistory_id AS counter, @B:=send.state_time AS last_time, @C:=send.state AS last_state FROM icinga_statehistory AS sstart INNER JOIN icinga_statehistory AS send ON send.statehistory_id = (SELECT MIN(statehistory_id) FROM icinga_statehistory WHERE statehistory_id > sstart.statehistory_id AND instance_id = 1 AND state_type = 1 AND object_id = $P{p_service_object_id} AND state_time > $P{p_date_start} ) WHERE sstart.instance_id = 1 AND sstart.state_type = 1 AND sstart.object_id = $P{p_service_object_id} AND sstart.state_time BETWEEN $P{p_date_start} AND $P{p_date_end} UNION -- -- if last existing database entry is smaller then given enddate we have to add the timediff with last state -- SELECT IF(@B < $P{p_date_end} AND @A IS NOT NULL, TIMESTAMPDIFF(SECOND,@B,$P{p_date_end}), 0) AS state_duration, @C AS state_one, 0 AS state_two, @A AS counter, 0 AS last_time, 0 AS last_state FROM DUAL UNION -- -- no entry found in database, simulate an entry for given period -- SELECT IF(@A IS NULL, TIMESTAMPDIFF(SECOND, $P{p_date_start},$P{p_date_end}), 0) AS state_duration, IFNULL( (SELECT state FROM icinga_statehistory WHERE instance_id = 1 AND state_type = 1 AND object_id = $P{p_service_object_id} AND state_time < $P{p_date_start} ORDER BY state_time DESC LIMIT 1 ), 0) AS state_one, 0 AS state_two, @A AS counter, 0 AS last_time, 0 AS last_state FROM DUAL UNION -- -- delete variable for next run to prevent errors regarding identical database connection -- SELECT IFNULL(@A:=NULL,0) AS state_duration, 0 AS state_one, 0 AS state_two, 0 AS counter, 0 AS last_time, 0 AS last_state FROM DUAL ) as state where state_duration > 0 GROUP BY state_one[/code]

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...