The following two tabs change content below.
Hi, I have written and developed this site to share my experience and ideas with other colleagues. I also started to prepare interview questions and answers for job seekers. I hope it will help you a lot.

As I given basic understanding of zabbix intallation and frontend setup in my previous post. In this Post I am giving the query details to get latest data of host items from zabbix database by host id or group id. Before to go into the details I want to give some tables understanding which we are going to use in below query to get latest data.

Table : ITEMS

This table contain the all information of item like item name, description, hostid etc. Erlier this table was itself containing the latest value of item but now in the latest version of zabbix they just change DB schema and now latest data are storing into HISTORY tables. basically we have 5 history tables based on item value type.

zabbix_history_tables

Most of you I think already aware about Item value_type property if not let me explain to you in short. On zabbix host as we know we are monitoring multiple items and every item have different type of information as mention sown below in screenshot: in programming term we can say data type.

type_of_information

So zabbix provided diffrent table for every type of value.Below is the mapping of table with type of information.

TYPE OF INFORMATION HISTORY TABLE NAME
Numeric (unsigned) HISTORY_UINT
Numeric (float) HISTORY
Character HISTORY_STR
Log HISTORY_LOG
Text HISTORY_TEXT

I think now you are clear about the value type and table mapping.

In the ITEMS table there is one column value_type based on that we can get data from appropriate history table as mention below in query.

Get latest data for single Item:

 SELECT i.name, 
(case when (i.value_type = 0)
 then
 (select history.value from history where (history.itemid = i.itemid) order by history.clock desc limit 1)

when (i.value_type = 1)
 then
 (select history_str.value from history_str where (history_str.itemid = i.itemid) order by history_str.clock desc limit 1)


when (i.value_type = 3)
 then
 (select history_uint.value from history_uint where (history_uint.itemid = i.itemid) order by history_uint.clock desc limit 1)
 
end) as latest_value
 from items i where i.itemid = <<ITEM_ID>>

Get latest data of all items under specified host:

 SELECT h.host, i.name, 
(case when (i.value_type = 0)
 then
 (select history.value from history where (history.itemid = i.itemid) order by history.clock desc limit 1)

when (i.value_type = 1)
 then
 (select history_str.value from history_str where (history_str.itemid = i.itemid) order by history_str.clock desc limit 1)


when (i.value_type = 3)
 then
 (select history_uint.value from history_uint where (history_uint.itemid = i.itemid) order by history_uint.clock desc limit 1)
 
end) as latest_value
 from items i , hosts h, hosts_groups hg
 where i.hostid = hg.hostid and hg.hostid = h.hostid
 and hg.hostid = <<hostid>>

 

Get latest data of all host items which are coming under specified host group:

SELECT h.host, i.name, 
(case when (i.value_type = 0)
 then
 (select history.value from history where (history.itemid = i.itemid) order by history.clock desc limit 1)

when (i.value_type = 1)
 then
 (select history_str.value from history_str where (history_str.itemid = i.itemid) order by history_str.clock desc limit 1)


when (i.value_type = 3)
 then
 (select history_uint.value from history_uint where (history_uint.itemid = i.itemid) order by history_uint.clock desc limit 1)
 
end) as latest_value
 from items i , hosts h, hosts_groups hg
 where i.hostid = hg.hostid and hg.hostid = h.hostid
 and hg.groupid = 15
  

Thats all if you like please share your comments

42 total views, 1 views today

Leave a Reply

Your email address will not be published. Required fields are marked *