Query to select max value

Question

I would like to extract some data from the icinga monitoring tool DB.

the tables:

icinga_objects

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| object_id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name1         | varchar(255)        | YES  | MUL |         |                |
| name2         | varchar(255)        | YES  | MUL | NULL    |                |
| is_active     | smallint(6)         | YES  |     | 0       |                |
...
+---------------+---------------------+------+-----+---------+----------------+

(for information name1 contains hostnames and name2 monitoring services)

icinga_statehistory

+-----------------------+---------------------+------+-----+---------+----------------+
| Field                 | Type                | Null | Key | Default | Extra          |
+-----------------------+---------------------+------+-----+---------+----------------+
| state_time            | timestamp           | YES  |     | NULL    |                |
| object_id             | bigint(20) unsigned | YES  | MUL | 0       |                |
| state                 | smallint(6)         | YES  |     | 0       |                |
| output                | text                | YES  |     | NULL    |                |
...
+-----------------------+---------------------+------+-----+---------+----------------+

I need to extract (I hope I'm clear enough): name1, name2, output and only the most recent state_time for each couple name1/name2 where object_id are common in both tables and name2 = 'xxx' and is_active = '1' and state = '0'

for exampmle, if icinga_objects contains:

object_id | name1    | name2    | is_active |
5         | groot    | os_info  | 1

and icinga_statehistory contains:

state_time          | object_id | state | output   |
2023-01-16 16:40:07 | 5         | 0     | RHEL 8.7 |
2023-01-14 12:47:52 | 5         | 0     | RHEL 8.7 |
2023-01-17 05:12:27 | 5         | 0     | RHEL 8.7 |

for the couple groot/os_info I want only one answer containing :

name1    | name2    | output   | state_time          |
groot    | os_info  | RHEL 8.7 | 2023-01-17 05:12:27 |

I tried to use inner join that way:

select name1, name2, output, state_time
from icinga_objects cs
inner join icinga_statehistory s on cs.object_id = s.object_id
where name2 = 'xxx' and is_active = '1' and state = '0'
GROUP BY name2, name1, state_time;

which seems ok but gives me more information than I need, I obtain all the recorded times for each couple name1/name2: I now need to only keep the maximum value of state_time for each couple name1/name2, unfortunately my sql knowledge is way to low to do that.

do you have any idea how to do that? Thanks for your help

Answer 1

For the sake of this answer I have assumed that object_id uniquely identifies a name1/name2 pair.

The MySQL < 8.0 method is to find the max(state_time) per object_id and join back to icinga_statehistory on both object_id and max(state_time) -

select max.name1, max.name2, st.output, st.state_time
from (
    select cs.object_id, cs.name1, cs.name2, max(s.state_time) max_state_time
    from icinga_objects cs
    inner join icinga_statehistory s on cs.object_id = s.object_id
    where cs.name2 = 'os_info' and cs.is_active = 1 and s.state = 0
    group by cs.object_id
) max
inner join icinga_statehistory st on max.object_id = st.object_id and max.max_state_time = st.state_time;

For MySQL >= 8.0 you can use the ROW_NUMBER() window function -

select name1, name2, output, state_time
from (
    select cs.name1, cs.name2, s.output, s.state_time, row_number() over (partition by cs.object_id order by s.state_time desc) rn
    from icinga_objects cs
    inner join icinga_statehistory s on cs.object_id = s.object_id
    where cs.name2 = 'os_info' and cs.is_active = 1 and s.state = 0    
) t
where rn = 1;