SQL query to get the all hostname and all history of a specific item and hostgroup

[Molusco]

Hi,how I can get the host name and the history of a specific item and host group, I made a diagram to explain what I need, maybe someone has made thid before

[user1234]

did you fin a solution for this?

[saulosousa]

Hi,


I hope I can help with your query.
To get the information with an SQL query I had to use a SUBQUERY.


Please understand that in order to make the query possible, some changes may be necessary. Here are some notes about my infrastructure.

1. I had to use table "HISTORY_UINT" because in my infrastructure it stores the UPTIME data.
2. I made an example query to return only the hosts that contain the name "SWITCHES" in their host group. To get all the hosts just delete the WHERE clause of the query.
3. In the ITEMS table I had to search for key_ = 'sysuptime', but in your database it may have another name.

[query]

select h.hostid, h.host, hg.name,

(select a.value from history_uint a

join (select h1.hostid, ht1.itemid, max(ht1.clock) clock from hosts h1 join items i1 on (h1.hostid=i1.hostid)

join history_uint ht1 on (ht1.itemid=i1.itemid)

where i1.key_ = 'sysuptime' group by h1.hostid, ht1.itemid) b on (a.itemid=b.itemid and a.clock=b.clock)

where h.hostid=b.hostid

) uptime

from hosts h join hosts_groups hgs on h.hostid=hgs.hostid join hstgrp hg on hgs.groupid=hg.groupid

where hg.name like '%Switches%'

limit 10

[Vinicius Souza]

I have a contribution but I also wanted help.
Theis view

SELECT
`h`.`name` AS `hostname`,
`h`.`status` AS `hoststatus`,
`g`.`groupid` AS `groupname`,
`hi`.`alias` AS `hostalias`,
`ht`.`tag` AS `host_tag_tag`,
`ht`.`value` AS `host_tag_value`,
`dt`.`idescription` AS `itemdescription`,
`dt`.`ikey` AS `itemkey_`,
`dt`.`iname` AS `itemname`,
`dt`.`hsurl` AS `httpstepurl`,
`dt`.`hsname` AS `httpstepname`,
`dt`.`tdescription` AS `triggerdescription`,
`dt`.`tpriority` AS `triggerpriority`,
`dt`.`eventclock` AS `eventclock`,
`dt`.`eacknowledged` AS `eventacknowledged`,
`p`.`eventid` AS `problem_eventid`,
`p`.`r_eventid` AS `r_eventid`,
from_unixtime( `p`.`r_clock` ) AS `r_clock`,
CASE

WHEN `p`.`r_clock` IS NOT NULL
AND `p`.`r_eventid` IS NOT NULL THEN
'RESOLVIDO' ELSE 'NÃO RESOLVIDO'
END AS `problem_status`,
CASE
`p`.`severity`
WHEN 5 THEN
'Desastre'
WHEN 4 THEN
'Alto'
WHEN 3 THEN
'Média'
WHEN 2 THEN
'Aviso'
WHEN 1 THEN
'Informação' ELSE 'Desconhecido'
END AS `problemseverity_name`,
`p`.`name` AS `problem_name`,
`et`.`eventid` AS `event_tag_eventid`,
`et`.`tag` AS `event_tag_tag`,
`et`.`value` AS `event_tag_value`,
`et`.`eventtagid` AS `event_tag_eventtagid`,
`er`.`eventid` AS `recovery_eventid`,
`er`.`r_eventid` AS `recovery_r_eventid`,
`e`.`name` AS `event_name`,
`e`.`eventid` AS `event_eventid`
FROM
(((((((((
`hosts` `h`
JOIN `hosts_groups` `hg` ON ( `h`.`hostid` = `hg`.`hostid` ))
JOIN `hosts_groups` `g` ON ( `hg`.`groupid` = `g`.`groupid` ))
LEFT JOIN `host_inventory` `hi` ON ( `h`.`hostid` = `hi`.`hostid` ))
LEFT JOIN `host_tag` `ht` ON ( `h`.`hostid` = `ht`.`hostid` ))
LEFT JOIN (
SELECT
`i`.`hostid` AS `ihostid`,
`i`.`itemid` AS `iitemid`,
`i`.`description` AS `idescription`,
`i`.`key_` AS `ikey`,
`i`.`name` AS `iname`,
`hs`.`url` AS `hsurl`,
`hs`.`name` AS `hsname`,
`t`.`description` AS `tdescription`,
`t`.`priority` AS `tpriority`,
from_unixtime( `e`.`clock` ) AS `eventclock`,
`e`.`acknowledged` AS `eacknowledged`,
`e`.`eventid` AS `eventid`
FROM
((((
`events` `e`
LEFT JOIN ((
`items` `i`
LEFT JOIN `functions` `f` ON ( `i`.`itemid` = `f`.`itemid` ))
LEFT JOIN `triggers` `t` ON ( `f`.`triggerid` = `t`.`triggerid` )) ON ( `t`.`triggerid` = `e`.`objectid` ))
LEFT JOIN `httpstepitem` `hsi` ON ( `i`.`itemid` = `hsi`.`itemid` ))
LEFT JOIN `httpstep` `hs` ON ( `hsi`.`httpstepid` = `hs`.`httpstepid` ))
JOIN `problem` `p` ON ( `e`.`eventid` = `p`.`eventid` ))) `dt` ON ( `h`.`hostid` = `dt`.`ihostid` ))
LEFT JOIN `problem` `p` ON ( `dt`.`eventid` = `p`.`eventid` ))
LEFT JOIN `event_tag` `et` ON ( `dt`.`eventid` = `et`.`eventid` ))
LEFT JOIN `event_recovery` `er` ON ( `p`.`r_eventid` = `er`.`r_eventid` ))
LEFT JOIN `events` `e` ON ( `dt`.`eventid` = `e`.`eventid` ))
WHERE
`h`.`name` IN (
'HOST1',
'HOST2',
'HOST3')

but on another power bi page I wanted to bring the same zabbix page that has the history.

this page on power bi




so that in the table below the previous events do not display