Query to select max value-CodePudding

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

CodePudding user response：

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;