Pulling Raw Data From Zabbix Database

[rrm74001]

Are there plan for developing an easy way to extract raw data from the Zabbix database? For example, I have developed some scripts that utilize the Zabbix API to extract raw data from the Zabbix database, and in this script I accept as parameters: the host name, the desired start time, and the desired end time. It then returns to me a CSV file of all items that are associated with that host within the specified time window, and then I can then easily parse it and import into Excel.

However, I suspect that even small changes to the Zabbix API or Zabbix source will break my scripts. Will support for this be built-in to Zabbix in the future?

[nick0909]

Why do you think the API would change? I thought that was the point of the API..

Do you mind posting an example of your script? I am hoping to do what sounds like the same thing, I want to get the availability information for hosts and be able to dump that in to an excel file that our marketing team and use to compare with their website stats. They don't really have an interest in going through that zabbix web interface for this data.

[clahti]

+1

I would also be interested in seeing your script, I have a similar request from management.

Thanks!

[steveboyson]

The API changed from 2.0 to 2.2, at least regarding the fields "lastvalue" and "lastclock": they are no longer in the items table.

What you have:
- items table
important fields: itemid, value_type, hostid
- hosts table
important fields: hostid, status

Depending on items.value_type, you will find the lastvalue in either history, history_text, history_str, history_uint or history_log

my %tables = (
        0 => 'history',
        1 => 'history_str',
        2 => 'history_log',
        3 => 'history_uint',
        4 => 'history_text',
        );

In history, you will find fields "value" and "clock", where the record with the highest "clock" value is the latest item value.
So:
(in pseudo code)

(itemid,value_type) = select h.name, i.itemid, i.value_type from items i, hosts h where name = "whatever your item is called" and h.hostid = i.hostid and h.name = "your-host";

and

(value, clock) = select value, clock from tables[value_type] hist where itemid = hist.itemid order by hist.clock DESC limit 1;

The first SQL will return the itemid and the value_type. You need the later to know which history* table is to be queried in the next SQL statement.
Then you select the correct history* table and feed the previously acquired itemid in. You sort "descending" (newest entry first) and limit the output records to 1 (giving only the most recent record).

Then format the output according your needs (comma or semikolon or tab separated etc.)

Finish.

[mushero]

We think the API is totally useless for real data extraction, especially across host or items or template or anything - and we DEARLY miss the loss of lastvalue/clock in the DB - we actually added triggers to put the lastvalue/clock back in - works great at 1,000 NVPS and makes all our code work again ! Very easy.

We often need to compare values across a template or find a set of values across many servers, like all Java instances running as root, or all CPU % over 90%, etc. This is totally impossible both in the API and in the new DB structure - horrid for real use at scale and in complex systems.

So we have lots of complex SQL queries to extract things in groups of servers, for various template IDs, clusters of attributes and comparisons, etc. In most cases we only care about lastvalue for this as history is great for graphs, but not for this real-time analysis or reporting such as find all bash binary versions that are ShellShocked, or any time we want to aggregate, search, or compare across many/most/all of our servers, templates, which is often.

[havok123]

Can you tell me what API call did you use to get the raw data from the databse

Hey,

I was wondering, could you tell me what API call did you use to get the raw data from the database?

I want to get some of the latest data for which I am monitoring, through the API call. Also does it give you data for a particular machine or do you get all the data (data for all the machines) that the Item(or say host) is monitoring?

It would be a lot of help to me.

Thank you.

[mushero]

havok123,

Not sure what API to use as we don't really pull data via the API, since we use SQL directly to the DB, though for latest data we use the trigger system we mentioned in the post to re-create the pre-2.0 data structure, which is the only way to get data across many hosts, templates, etc.

It's a bit tricky to get from a 2.x system as you need to know the data type to know the right history table to get data from, then sorted and limited - a mess, really, in our opinion, that makes it very hard to get data from the system.

[x1alpha76]

We had the same problem as mushero, the (my)SQL script here will create a new table with the triggers to populate the table with the latest values.

[kloczek]

We had the same problem as mushero, the (my)SQL script here will create a new table with the triggers to populate the table with the latest values.

May I ask you guys what exactly you are doing that you need straight access to raw zabbix data?
I'm asking because pretty sure that in many such cases it is possible to implement some functionalities without such access.

[steveboyson]

We use it for generating HTML tables out of the database and display them as "plain text".

https://owncloud.netzwerk-aktiv.com/owncloud/index.php/s/5haFoAqIdmicbyY

[kloczek]

We use it for generating HTML tables out of the database and display them as "plain text".

https://owncloud.netzwerk-aktiv.com/...haFoAqIdmicbyY

Seems like in this exactly case you can use mapping exact values to zabbix metrics present those data over inventory.
I know that zabbix inventory have big limitations but IMO it would be better to cooperate with zabbix dev team on providing enough interface for presenting hardware state like in attached image.

[mushero]

We had the same problem as mushero, the (my)SQL script here will create a new table with the triggers to populate the table with the latest values.

Note we just added the old 'lastvalue' field back in the item table and populate that via triggers.

On each history table, we have a trigger that says:

CREATE DEFINER=`xxx`@`xxx` TRIGGER `history_uint_AINS` AFTER INSERT ON `history_uint` FOR EACH ROW
UPDATE items SET lastvalue = NEW.value, lastclock = NEW.clock WHERE items.itemid = NEW.itemid

Works great, fast, flawless and lets us use all our old SQL - super easy to get any current value any time, across any set of items, templates, hosts, values, etc.

Note we are on SSD disks which helps with all the updates as we are at 1000 NVPS so that's 1K updates/second to the item table due to this. On SSD we don't even notice it.

[mushero]

May I ask you guys what exactly you are doing that you need straight access to raw zabbix data?
I'm asking because pretty sure that in many such cases it is possible to implement some functionalities without such access.

Yes, it's simple - we need to get item last data across a wide range of groups of items, usually from a single templated item and a server group.

For example yesterday we were pulling kernel versions of all production servers, like the following SQL - this is CRITICAL for us to find things, check versions, validate configs and much more.

We'll often add an item into our master templates for some/most/all hosts and then extract data this way.

Usually we'll add in a JOIN to a hostgroup, or add more filters such as a specific kernel (lastvalue LIKE "%2.6.%") and so on.

SELECT host, lastvalue
FROM items i JOIN hosts h ON i.hostid = h.hostid
WHERE i.status = 0
AND h.status = 0
AND i.templateid = 24173
ORDER BY host

[kloczek]

Yes, it's simple - we need to get item last data across a wide range of groups of items, usually from a single templated item and a server group.

For example yesterday we were pulling kernel versions of all production servers, like the following SQL - this is CRITICAL for us to find things, check versions, validate configs and much more.

We'll often add an item into our master templates for some/most/all hosts and then extract data this way.

Usually we'll add in a JOIN to a hostgroup, or add more filters such as a specific kernel (lastvalue LIKE "%2.6.%") and so on.

SELECT host, lastvalue
FROM items i JOIN hosts h ON i.hostid = h.hostid
WHERE i.status = 0
AND h.status = 0
AND i.templateid = 24173
ORDER BY host

Looks like in this case zabbix inventory should be used as well.

If you will map for example metrics with system.uname[] to "OS" inventory (in item setup you can find dropdown list with "Populates host inventory field" name) and if all hosts will have in inventory settings "automatic" on checking in inventory over web interface <your.zabbix.srv>/hostinventoriesoverview.php?form_refresh=1&groupid =0&groupby=os you will have what exactly you want without doing those queries on sql backend

Try this

[kloczek]

Looks like in this case zabbix inventory should be used as well.

If you will map for example metrics with system.uname[] to "OS" inventory

Maybe in this case system.uname[] is not the best because it produces string with host name as well. However mapping to "OS" something like system.run[uname -vr] should be OK