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
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;