Import or Load History and Trend Data

[ivrhall]

I've searched all over but haven't really found any methods described for doing this. Or at least none described with sufficient detail to be useful. I have had Zabbix up and running for several weeks now. I have tweaked host items and triggers, added a few graphs, etc. I would like to make this my primary performance monitoring tool. However, in order to do so I need historical data.

I have data for the hosts I need most in other formats. But what I really need to be able to do is load this data into the trend and history tables so that I can use the Zabbix frontend to graph it for me. I found only one thread that describes anything similar, but that thread died after discussing the fact the history data was used for the most recent 24 hours and trend data for anything older.

Does anyone have any experience loading data into history and trends? Any method documents or scripts that I might leverage to make this happen? Any help would be appreciated.

[ivrhall]

Re:Importing/loading trend data

So is there no way to do this? No one has ever migrated to Zabbix from another tool and wanted to preserve their data? I can understand the absence of a tool for this purpose - the need must not be very great. But I find it hard to belive no one has ever had to do this before. Any ideas? Any help?

[alixen]

Hi,

I have never had to import data in Zabbix; however, I have dealt with data extraction from history and trends.
Here is some basic information on which I base my extractions, it may help you:

1. Historical data
There are 5 tables depending on the type of item:

Type            Table
----------      ----------------
float           history
integer         history_uint
string          history_str
text            history_text
log             history_log

All these tables have 3 fields:
itemid : id of item as defined in items table
clock : time associated to data in Unix timestamp format (seconds since 01/01/1970 00:00)
value : data value

This is a short example of historical data extraction:

select i.itemid,
       clock,
       value
  from hosts h,
       items i,
       history hy
 where i.hostid=h.hostid
   and hy.itemid=i.itemid
   and h.host='myhost'
   and i.description='CPU load'
 limit 10;
+--------+------------+--------+
| itemid | clock      | value  |
+--------+------------+--------+
|  19571 | 1267432685 | 1.0500 |
|  19571 | 1267432985 | 0.9500 |
|  19571 | 1267433285 | 1.0600 |
|  19571 | 1267433586 | 0.7800 |
|  19571 | 1267433886 | 0.5600 |
|  19571 | 1267434186 | 1.9900 |
|  19571 | 1267434485 | 1.5100 |
|  19571 | 1267434786 | 0.3700 |
|  19571 | 1267435085 | 0.5500 |
|  19571 | 1267435386 | 1.4000 |
+--------+------------+--------+
10 rows in set (0.00 sec)

2. Trends
There is one table 'trends'.
It contains one record per item, per hour.
It has 6 fields:
itemid : id of item as defined in items table
clock : time associated to data in Unix timestamp format (seconds since 01/01/1970 00:00)
num : number of historical data used to compute trend = number of values during this hour
value_min : min. value during this hour
value_avg : average value during this hour
value_max : max. value during this hour

This is a short example of trend extraction:

select i.itemid,
       from_unixtime(clock),
       t.num,
       t.value_min,
       t.value_avg,
       t.value_max
  from hosts h,
       items i,
       trends t
 where i.hostid=h.hostid
   and t.itemid=i.itemid
   and h.host='myhost'
   and i.description='CPU load'
 order by clock desc
 limit 10;
+--------+----------------------+-----+-----------+-----------+-----------+
| itemid | from_unixtime(clock) | num | value_min | value_avg | value_max |
+--------+----------------------+-----+-----------+-----------+-----------+
|  19571 | 2010-03-31 10:00:00  |  12 |    0.6400 |    1.6033 |    3.2400 |
|  19571 | 2010-03-31 09:00:00  |  12 |    0.3700 |    1.0092 |    2.0200 |
|  19571 | 2010-03-31 08:00:00  |  12 |    0.2800 |    0.9067 |    2.6000 |
|  19571 | 2010-03-31 07:00:00  |  12 |    0.1200 |    0.8250 |    1.7100 |
|  19571 | 2010-03-31 06:00:00  |  12 |    0.0300 |    0.4567 |    1.2700 |
|  19571 | 2010-03-31 05:00:00  |  12 |    0.0900 |    0.9100 |    2.1800 |
|  19571 | 2010-03-31 04:00:00  |  12 |    0.0400 |    0.5200 |    1.8700 |
|  19571 | 2010-03-31 03:00:00  |  12 |    0.2800 |    1.1008 |    2.5800 |
|  19571 | 2010-03-31 02:00:00  |  12 |    0.0600 |    0.6783 |    2.1300 |
|  19571 | 2010-03-31 01:00:00  |  12 |    0.2200 |    0.9392 |    2.5600 |
+--------+----------------------+-----+-----------+-----------+-----------+
10 rows in set (0.08 sec)

num is 12 because we have 12 values per hour.

Hope this helps
Alixen

[ivrhall]

Thanks

Thank you Alixen. That definitely helps, though I don't think I have everything I need to attempt this yet.

Do you, or does anyone know:

1) if I were to backfill the trend table with data for a specific itemid, would the frontend be able to graph that data?

2) If I were to backfill the history table(s), would the housekeeping routines be able to summarize that data and populate the trend table?

I'm really hoping I can save the data I have from other sources and make Zabbix my single source for this data.

[richlv]

i would actually suggest using zabbix sender for such a task.

prepare a space-delimited file, then run zabbix_sender against it. make sure to use at least version 1.8.2 - 1.8 introduced ability to specify timestamp for each record (this is what you will want to use), and 1.8.2 fixed a problem with hostnames that contained space when using input file.

of course, first test it on a small dataset, verify that everything works as expected.

[ivrhall]

Zabbix Sender to backfill data?

Do you have any instructions on how I might do this?

I did not think that time was one of the parameters zabbix_sender supported, but perhaps I'm mistaken. I certainly have no objection to doing it this way if possible. In fact, I would rather use a zabbix utility for doing the load, than manually altering MySQL tables.

Doh! That's because I'm using 1.8! This may have made the argument for the upgrade. Thanks!

[danrog]

ivrhall, just to let you know, we have done this for some legacy mrtg stuff and it works great!!!! Since it sounds like you want a lot of historical data, just make sure you have your item history set to the number days you want to have history for, otherwise housekeeper will clear out everything before that number.

[ivrhall]

Excellent. Thanks.

How far back did you go with your legacy data? Some of the data I am hoping to use if stored in rrdtool files (though not MRTG).

[danrog]

We only have a need for 365 days but you can go further back (you just have to worry about DB size).

Hopefully this makes sense. What we did was create the items we wanted to map the data too as a type "trapper" just for the import, then we changed the type to "Zabbix Agent" so the agent would process the data natively. We are running a custom UserParameter (tel.stats[0] or [1]) to actually collect the "live" data (its not OS level data the agent retrieves normally).

Since the timestamps (UNIX EPOC) used by both are the same, its just a matter of creating the file in the correct format.

Here is a sample script I used to convert the MRTG logs (not sure if we had it write to the log as well as the rrd data file or if it just did it on its own, check your data folder for .log files)

#!/bin/bash
scp host:/var/www/html/mrtg/tel*.log .
rm -rf import.trap
for i in `seq 1 7`; 
do 
 for x in 0 1; 
 do 
  cat tel0$i-$x.log | awk -v i=$i -v x=$x '{print "tel0"i".domain.com tel.stats["x"]", $1, $2}' >>import.trap; 
 done; 
done

Output:

tel01.domain.com tel.stats[0] 1267067100 4
tel01.domain.com tel.stats[0] 1267066800 4
tel01.domain.com tel.stats[0] 1267066500 4
tel01.domain.com tel.stats[0] 1267066200 4

Format:
ZabbixHost itemname timestamp value

Then the import was done with:
zabbix_sender -z zabbixserver -T -i import.trap

[ivrhall]

That's fantastic. Thank you so much. This is exactly what I need to get started on back-filling my data from my legacy sources.

[richlv]

...
Here is a sample script I used to convert the MRTG logs (not sure if we had it write to the log as well as the rrd data file or if it just did it on its own, check your data folder for .log files)
...

very nice. would you mind documenting this on the wiki ?

[danrog]

very nice. would you mind documenting this on the wiki ?

Will do. Where do you suggest?

[richlv]

Will do. Where do you suggest?

maybe somewhere like http://www.zabbix.com/wiki/scripts/import/mrtg ?