0

I'm trying to generate a report for the Icinga2 Database.

I want to see the state times per day per host per service.

I have the following data structure:

Columns:

State_Time > The time where the state of an monitoring object changed from A to B

State > The state the object has now (A, B..)

Last_State > The state that the object had before the current time

Hostname > The name of the host object

Servicename > The name of the service object

I want the report to look something like:

host  | service | day | State A % | State B %
a.b.c | srvxyz  |1.1.1|        70 |        30

So that I basically can see how the availability was in percent.

From what I think somehow I need to:

  • Group By Hostname, Group by Servicename, Group by The Date of the DateTime Field
  • Calculate and summarize the time for each event to the previous event in dependency to the state

What I have so far is:

select
    sum(state_time_usec),
    max(name1),
    max(name2),
    max(date(state_time)),
    max(state)
from
    icinga_statehistory
    inner join icinga_objects on icinga_objects.object_id = icinga_statehistory.object_id

group by icinga_objects.object_id,date(state_time),state

order by state_time desc

But I dont know how to start with the calculation of the previous row. Can you point me into a direction or do you have any ideas? Thank you!

Duffkess
  • 38
  • 6

1 Answers1

0

You can create a rank for your table, like:

@curRank := @curRank + 1 AS rank

And inner join to the same table with

@curRank := @curRank + 2 AS rank

So you will have the previous value. With this very table you will be able to calculate what you need.

ecp
  • 319
  • 1
  • 6
  • 18
  • Hi ecp, thank you for the good idea. I must confess, I've never used ranks and also no local variables in my query. So I'm not quite sure where to place this inside the query? I think the first one is in the select statement but how is the join working? Or do I need to have a subquery with these in the select statemenet and then join the subquery to my table? Thanks for you support! – Duffkess Apr 30 '18 at 07:12
  • Here you have more information: https://stackoverflow.com/questions/3333665/rank-function-in-mysql – ecp Apr 30 '18 at 09:12