zabbixで東京電力の過去分の値を入れる準備

これの続きです。

これで過去の分を入れるようなバッチを作るかなと 書いていましたが、いざ始めてみると事前調査が必要だったりしたのでまとめます。


http://www.tepco.co.jp/forecast/html/images/juyo-2011.csv
ここから2011年のデータを取得します。

注意:この作業を行うにあたって、心構えとしては

・運用中のzabbixが不安定になる覚悟をする。
・バックアップを取ったデータベースがなければ、過去データはすべて破棄する覚悟。
・作業を行った直後は問題がなかったとしても、特殊なデータや状況が発生した時に、データ破損してしまうことを受け入れる覚悟。

これらのことが許容できる環境の持ち主か、どうにもせっぱつまっていて、上記のリスクに比べたら相当に致命的な状況まで追い込まれている人じゃない限り、やらないほうが良い作業だと思っています。※技術力向上のためにやれるなら幸せかもですが。


■データの挿入先であるtrendsテーブルを調査
まずはテーブルを見つけました。trends テーブルとtrends_unit です。使い分けはどうなのか?何を入れるべきかを調べました。

 PUBLIC | trends                    | TABLE    | zabbix
 PUBLIC | trends_uint               | TABLE    | zabbix



■テーブル構造

 zabbix=# \d trends
                 TABLE "public.trends"
  Column   |     TYPE      |         Modifiers          
-----------+---------------+----------------------------
 itemid    | bigint        | NOT NULL DEFAULT 0::bigint
 clock     | INTEGER       | NOT NULL DEFAULT 0
 num       | INTEGER       | NOT NULL DEFAULT 0
 value_min | numeric(16,4) | NOT NULL DEFAULT 0.0000
 value_avg | numeric(16,4) | NOT NULL DEFAULT 0.0000
 value_max | numeric(16,4) | NOT NULL DEFAULT 0.0000
Indexes:
    "trends_pkey" PRIMARY KEY, btree (itemid, clock)
 
zabbix=# \d trends_uint
               TABLE "public.trends_uint"
  Column   |     TYPE      |          Modifiers          
-----------+---------------+-----------------------------
 itemid    | bigint        | NOT NULL DEFAULT 0::bigint
 clock     | INTEGER       | NOT NULL DEFAULT 0
 num       | INTEGER       | NOT NULL DEFAULT 0
 value_min | numeric(20,0) | NOT NULL DEFAULT 0::numeric
 value_avg | numeric(20,0) | NOT NULL DEFAULT 0::numeric
 value_max | numeric(20,0) | NOT NULL DEFAULT 0::numeric
Indexes:
    "trends_uint_pkey" PRIMARY KEY, btree (itemid, clock)



itemid はアイテムと紐づくところでしょうし、clockは1時間ごとの時間が入っているはずです。
value_*はそれぞれ、値が入っているところはグラフなどで見ていました。


不思議なのは、num です。これは何者?何に使うの?っていうことが分からないとバッチで入れるなんてできないと思いましたので調べました。

まずは、前回で作った電力使用量と使用率のアイテムから、どんな値が入っているかを調べてみます。

■現在のデータから対象のitemid を見つける。

itmes テーブルの構造です。僕はkey_には固定文言が入ると思ったのでlike で探してみました。

zabbix=# \d items
                                   TABLE "public.items"
        Column         |          TYPE          |                Modifiers                
-----------------------+------------------------+-----------------------------------------
 itemid                | bigint                 | NOT NULL DEFAULT 0::bigint
 TYPE                  | INTEGER                | NOT NULL DEFAULT 0
 snmp_community        | character varying(64)  | NOT NULL DEFAULT ''::character varying
 snmp_oid              | character varying(255) | NOT NULL DEFAULT ''::character varying
 snmp_port             | INTEGER                | NOT NULL DEFAULT 161
 hostid                | bigint                 | NOT NULL DEFAULT 0::bigint
 description           | character varying(255) | NOT NULL DEFAULT ''::character varying
 key_                  | character varying(255) | NOT NULL DEFAULT ''::character varying
 delay                 | INTEGER                | NOT NULL DEFAULT 0
 history               | INTEGER                | NOT NULL DEFAULT 90
 trends                | INTEGER                | NOT NULL DEFAULT 365
 lastvalue             | character varying(255) | 
 lastclock             | INTEGER                | 
 prevvalue             | character varying(255) | 
 status                | INTEGER                | NOT NULL DEFAULT 0
 value_type            | INTEGER                | NOT NULL DEFAULT 0
 trapper_hosts         | character varying(255) | NOT NULL DEFAULT ''::character varying
 units                 | character varying(10)  | NOT NULL DEFAULT ''::character varying
 multiplier            | INTEGER                | NOT NULL DEFAULT 0
 delta                 | INTEGER                | NOT NULL DEFAULT 0
 prevorgvalue          | character varying(255) | 
 snmpv3_securityname   | character varying(64)  | NOT NULL DEFAULT ''::character varying
 snmpv3_securitylevel  | INTEGER                | NOT NULL DEFAULT 0
 snmpv3_authpassphrase | character varying(64)  | NOT NULL DEFAULT ''::character varying
 snmpv3_privpassphrase | character varying(64)  | NOT NULL DEFAULT ''::character varying
 formula               | character varying(255) | NOT NULL DEFAULT '1'::character varying
 error                 | character varying(128) | NOT NULL DEFAULT ''::character varying
 lastlogsize           | INTEGER                | NOT NULL DEFAULT 0
 logtimefmt            | character varying(64)  | NOT NULL DEFAULT ''::character varying
 templateid            | bigint                 | NOT NULL DEFAULT 0::bigint
 valuemapid            | bigint                 | NOT NULL DEFAULT 0::bigint
 delay_flex            | character varying(255) | NOT NULL DEFAULT ''::character varying
 params                | text                   | NOT NULL DEFAULT ''::text
 ipmi_sensor           | character varying(128) | NOT NULL DEFAULT ''::character varying
 data_type             | INTEGER                | NOT NULL DEFAULT 0
 authtype              | INTEGER                | NOT NULL DEFAULT 0
 username              | character varying(64)  | NOT NULL DEFAULT ''::character varying
 password              | character varying(64)  | NOT NULL DEFAULT ''::character varying
 publickey             | character varying(64)  | NOT NULL DEFAULT ''::character varying
 privatekey            | character varying(64)  | NOT NULL DEFAULT ''::character varying
 mtime                 | INTEGER                | NOT NULL DEFAULT 0
Indexes:
    "items_pkey" PRIMARY KEY, btree (itemid)
    "items_1" UNIQUE, btree (hostid, key_)
    "items_3" btree (status)
    "items_4" btree (templateid)



WHERE key_ like ‘%denki%’ にて、アイテムを探しました。SQL叩かずに、Pgadminなど使って調べたほうが早いかもしれませんが、CUIでできるところまでやるのが癖になってしまっています。

zabbix=#  SELECT itemid,TYPE,key_,trends,units,data_type FROM items WHERE key_ LIKE '%denki%';
 itemid | TYPE |         key_         | trends | units | data_type 
--------+------+----------------------+--------+-------+-----------
  22497 |    0 | denki.VALUE[MIN,20]  |    365 | w     |         0
  22498 |    0 | denki.VALUE[rate,20] |    365 | %     |         0
(2 rows)



あっさりと見つかったのですが、22497(使用量)が入っているテーブルと、22498(使用率)が入っているテーブルは違いました。

zabbix=# SELECT * FROM trends WHERE itemid IN (22497,22498) LIMIT 5;
 itemid |   clock    | num | value_min | value_avg | value_max 
--------+------------+-----+-----------+-----------+-----------
  22498 | 1309870800 |  10 |   71.1900 |   72.2360 |   73.6000
  22498 | 1309867200 |   1 |   71.8500 |   71.8500 |   71.8500
  22498 | 1309874400 |  10 |   65.9000 |   68.3120 |   70.8200
  22498 | 1309878000 |   1 |   64.4200 |   64.4200 |   64.4200
  22498 | 1309881600 |  10 |   56.0000 |   57.2920 |   58.6800
(5 rows)



※電気使用量がない

zabbix=# SELECT * FROM trends WHERE itemid = 22497 LIMIT 5;
 itemid | clock | num | value_min | value_avg | value_max 
--------+-------+-----+-----------+-----------+-----------
(0 rows)





■trends_uintにあった。

zabbix=# SELECT * FROM trends_uint WHERE itemid = 22497 LIMIT 5;
 itemid |   clock    | num | value_min | value_avg | value_max 
--------+------------+-----+-----------+-----------+-----------
  22497 | 1309705200 |   2 |      3124 |      3138 |      3153
  22497 | 1309708800 |   9 |      2759 |      2842 |      2926
  22497 | 1309712400 |  11 |      2652 |      2701 |      2765
  22497 | 1309716000 |  11 |      2610 |      2628 |      2661
  22497 | 1309719600 |  11 |      2598 |      2610 |      2631
(5 rows)
 
※途中から倍数で調整したのでこんな形
  22497 | 1309816800 |  11 |        2896 |        3033 |        3163
  22497 | 1309820400 |  11 |        3171 |  6377275424 | 35300000000
  22497 | 1309824000 |  11 | 35640000000 | 37525454543 | 38930000000
  22497 | 1309827600 |  11 | 39300000000 | 40225454543 | 41030000000
  22497 | 1309831200 |  11 | 41080000000 | 41630909090 | 42060000000



■numってなんだろう?
ここで疑問にわいたのがnum というカラムの存在。下記のように要素をとってみると見事にバラバラ。

zabbix=# SELECT DISTINCT(num) FROM trends_uint WHERE itemid = 22497 ;
 num 
-----
   6
  14
  13
  12
   8
   1
   9
   2
   7
  10
  11
(11 rows)



どうも、11が一番数として多いみたいなんだけれど、どういうことなのか調べてみた。

zabbix=# SELECT * FROM trends_uint WHERE itemid = 22497 ORDER BY num LIMIT 1000;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max  
--------+------------+-----+-------------+-------------+-------------
  22497 | 1309964400 |   1 | 32430000000 | 32430000000 | 32430000000
  22497 | 1311001200 |   1 | 34480000000 | 34480000000 | 34480000000
  22497 | 1310914800 |   1 | 34060000000 | 34060000000 | 34060000000
  22497 | 1310482800 |   1 | 34380000000 | 34380000000 | 34380000000
  22497 | 1310396400 |   1 | 35040000000 | 35040000000 | 35040000000
  22497 | 1310050800 |   1 | 32070000000 | 32070000000 | 32070000000
  22497 | 1310223600 |   1 | 33750000000 | 33750000000 | 33750000000
  22497 | 1309878000 |   1 | 32920000000 | 32920000000 | 32920000000
  22497 | 1310828400 |   1 | 34100000000 | 34100000000 | 34100000000
  22497 | 1310742000 |   1 | 35110000000 | 35110000000 | 35110000000
  22497 | 1310137200 |   1 | 34010000000 | 34010000000 | 34010000000
  22497 | 1311087600 |   1 | 32810000000 | 32810000000 | 32810000000
  22497 | 1310655600 |   1 | 33910000000 | 33910000000 | 33910000000
  22497 | 1311174000 |   1 | 29820000000 | 29820000000 | 29820000000
  22497 | 1310569200 |   1 | 33990000000 | 33990000000 | 33990000000
  22497 | 1309957200 |   2 | 35770000000 | 35940000000 | 36110000000
  22497 | 1309705200 |   2 |        3124 |        3138 |        3153
  22497 | 1310310000 |   2 | 32530000000 | 32530000000 | 32530000000
  22497 | 1309791600 |   6 |        3365 |        3431 |        3509
  22497 | 1310839200 |   7 | 27350000000 | 27595714284 | 27790000000
  22497 | 1309932000 |   7 | 41850000000 | 42101428570 | 42450000000
  22497 | 1310832000 |   8 | 29840000000 | 30376249998 | 31070000000
  22497 | 1309881600 |   8 | 28560000000 | 29143749998 | 29930000000
  22497 | 1310313600 |   8 | 28400000000 | 28967499999 | 29700000000
  22497 | 1310486400 |   9 | 29870000000 | 30479999999 | 31380000000
  22497 | 1311091200 |   9 | 28990000000 | 29528888888 | 30210000000
  22497 | 1311004800 |   9 | 30090000000 | 30679999999 | 31410000000
  22497 | 1310918400 |   9 | 29640000000 | 30312222220 | 31110000000
  22497 | 1310659200 |   9 | 29110000000 | 29816666664 | 30650000000
  22497 | 1310140800 |   9 | 29440000000 | 30132222221 | 30970000000
  22497 | 1311177600 |   9 | 26230000000 | 26672222220 | 27180000000
  22497 | 1310572800 |   9 | 29450000000 | 30109999999 | 30740000000
  22497 | 1310745600 |   9 | 30890000000 | 31497777776 | 32360000000
  22497 | 1310227200 |   9 | 29270000000 | 29926666664 | 30620000000
  22497 | 1309708800 |   9 |        2759 |        2842 |        2926
  22497 | 1309867200 |   9 | 36720000000 | 37706666665 | 38800000000
  22497 | 1310400000 |   9 | 30060000000 | 30735555553 | 31630000000
  22497 | 1310054400 |   9 | 28040000000 | 28655555554 | 29480000000
  22497 | 1309896000 |  10 | 26080000000 | 26327999998 | 26560000000
  22497 | 1309921200 |  10 | 40230000000 | 40942999998 | 42040000000
  22497 | 1309928400 |  10 | 42430000000 | 42492999999 | 42580000000
  22497 | 1309924800 |  10 | 40530000000 | 41782999998 | 42450000000
  22497 | 1309910400 |  10 | 36680000000 | 38296999997 | 39570000000
  22497 | 1309917600 |  10 | 41180000000 | 41622999999 | 42000000000
  22497 | 1309914000 |  10 | 39790000000 | 40484999999 | 41060000000
  22497 | 1309906800 |  10 | 32420000000 | 34408999998 | 36450000000
  22497 | 1309870800 |  10 | 36380000000 | 36914999998 | 37610000000
  22497 | 1309874400 |  10 | 33680000000 | 34942999998 | 36190000000
  22497 | 1309885200 |  10 | 27350000000 | 27858999997 | 28590000000
  22497 | 1309888800 |  10 | 26680000000 | 26885999999 | 27110000000
  22497 | 1309892400 |  10 | 26320000000 | 26544999997 | 26680000000
  22497 | 1310180400 |  11 | 40940000000 | 41366363633 | 41800000000
  22497 | 1310184000 |  11 | 41110000000 | 41846363634 | 42270000000
  22497 | 1310187600 |  11 | 42300000000 | 42451818180 | 42670000000
  22497 | 1310191200 |  11 | 42110000000 | 42299090908 | 42560000000
  22497 | 1310194800 |  11 | 41710000000 | 41991818180 | 42210000000
  22497 | 1310198400 |  11 | 40270000000 | 40909999998 | 41820000000
  22497 | 1310202000 |  11 | 39630000000 | 39877272725 | 40180000000
  22497 | 1310205600 |  11 | 39770000000 | 40109090906 | 40450000000
  22497 | 1310209200 |  11 | 38650000000 | 39257272726 | 39960000000
  22497 | 1310212800 |  11 | 36770000000 | 37688181815 | 38590000000
  22497 | 1310216400 |  11 | 36120000000 | 36916363634 | 37840000000
  22497 | 1310220000 |  11 | 34350000000 | 35370909089 | 36560000000
  22497 | 1310230800 |  11 | 27820000000 | 28470909089 | 29180000000
  22497 | 1310234400 |  11 | 27050000000 | 27359999997 | 27850000000
  22497 | 1310238000 |  11 | 26420000000 | 26810909088 | 27110000000
  22497 | 1309903200 |  11 | 29490000000 | 30714545453 | 32140000000
  22497 | 1310414400 |  12 | 27170000000 | 27545833331 | 28050000000
  22497 | 1310306400 |  12 | 33490000000 | 34488333332 | 35700000000
  22497 | 1311141600 |  12 | 40330000000 | 40657499997 | 41020000000
  22497 | 1310932800 |  12 | 27360000000 | 27613333330 | 27950000000
  22497 | 1310727600 |  12 | 41290000000 | 42091666664 | 42810000000
  22497 | 1311199200 |  12 | 26430000000 | 27009999998 | 27460000000
  22497 | 1310475600 |  12 | 37710000000 | 38419999997 | 39190000000
  22497 | 1309899600 |  13 | 26660000000 | 27809999997 | 29480000000
  22497 | 1310515200 |  13 | 38640000000 | 40651538458 | 42020000000
  22497 | 1310101200 |  13 | 41300000000 | 41459230766 | 41650000000
  22497 | 1309996800 |  14 | 34030000000 | 35414285711 | 36800000000



1の時にはvalue_min,value_avg,value_max が同じ数字。
2にもvalueが同じものはあるし、6,7,8,9,10は法則すらパッと見てわからない。
11は一番多く、割愛。12,13,14もあるけれどやっぱり不明。

■trends が入っているプログラムを探す。
zabbix-1.8.4のソースからインストールしたので、コンパイルしたディレクトリに移動して、
# fgrep -R ‘trends’ ./*
こんな感じで探した。

./create/data/data.sql:insert into help_items values (5,'zabbix[trends]','Number of values stored in table TRENDS.');
./create/data/data.sql:insert into help_items values (5,'zabbix[trends_uint]','Number of values stored in table TRENDS_UINT.');
 
./frontends/php/api/classes/class.citem.php:                            'trends',
./frontends/php/api/classes/class.citem.php:                            'trends_uint',
 
./src/libs/zbxdbcache/dbcache.c:        value_type = trends[0].value_type;
./src/libs/zbxdbcache/dbcache.c:        case ITEM_VALUE_TYPE_FLOAT: table_name = "trends"; break;
./src/libs/zbxdbcache/dbcache.c:        case ITEM_VALUE_TYPE_UINT64: table_name = "trends_uint"; break;
./src/libs/zbxdbcache/dbcache.c:                zbx_error("Unsupported value type for trends");
./src/libs/zbxdbcache/dbcache.c:        for (i = 0; i < *trends_num; i++)
./src/libs/zbxdbcache/dbcache.c:                trend = &trends[i];
./src/libs/zbxdbcache/dbcache.c:                        trends_to = i + 1;
./src/libs/zbxdbcache/dbcache.c:                        for (i = 0; i < trends_to; i++)
./src/libs/zbxdbcache/dbcache.c:                                trend = &trends[i];
./src/libs/zbxdbcache/dbcache.c:                        if (i == trends_to)
./src/libs/zbxdbcache/dbcache.c:                        /* if 'trends' is not a primary trends buffer */
 
■一番有力
./src/libs/zbxdbhigh/db.c:      result = DBselect("select num,value_min,value_avg,value_max from trends where itemid=" ZBX_FS_UI64 " and clock=%d",
 
./src/libs/zbxdbhigh/db.c:              DBexecute("update trends set num=%d, value_min=" ZBX_FS_DBL ", value_avg=" ZBX_FS_DBL ", value_max=" ZBX_FS_DBL " where itemid=" ZBX_FS_UI64 " and clock=%d",
./src/libs/zbxdbhigh/db.c:              DBexecute("insert into trends (clock,itemid,num,value_min,value_avg,value_max) values (%d," ZBX_FS_UI64 ",%d," ZBX_FS_DBL "," ZBX_FS_DBL "," ZBX_FS_DBL ")",
./src/libs/zbxdbhigh/db.c:      result = DBselect("select num,value_min,value_avg,value_max from trends_uint where itemid=" ZBX_FS_UI64 " and clock=%d",
./src/libs/zbxdbhigh/db.c:              DBexecute("update trends_uint set num=%d,value_min=" ZBX_FS_UI64 ",value_avg=" ZBX_FS_UI64 ",value_max=" ZBX_FS_UI64 " where itemid=" ZBX_FS_UI64 " and clock=%d",
./src/libs/zbxdbhigh/db.c:              DBexecute("insert into trends_uint (clock,itemid,num,value_min,value_avg,value_max) values (%d," ZBX_FS_UI64 ",%d," ZBX_FS_UI64 "," ZBX_FS_UI64 "," ZBX_FS_UI64 ")",



■核心。
int     DBadd_trend(zbx_uint64_t itemid, double value, int clock)
{
        DB_RESULT       result;
        DB_ROW          row;
        int     hour;
        int     num;
        double  value_min, value_avg, value_max;
 
        zabbix_log(LOG_LEVEL_DEBUG,"In add_trend()");
 
        hour=clock-clock%3600;
 
        result = DBselect("select num,value_min,value_avg,value_max from trends where itemid=" ZBX_FS_UI64 " and clock=%d",
                itemid,
                hour);
 
        row=DBfetch(result);
 
        if(row)
        {
                num=atoi(row[0]);
                value_min=atof(row[1]);
                value_avg=atof(row[2]);
                value_max=atof(row[3]);
                if(value<value_min)     value_min=value;
                if(value>value_max)     value_max=value;
                value_avg=(num*value_avg+value)/(num+1);
                num++;
                DBexecute("update trends set num=%d, value_min=" ZBX_FS_DBL ", value_avg=" ZBX_FS_DBL ", value_max=" ZBX_FS_DBL " where itemid=" ZBX_FS_UI64 " and clock=%d",
                        num,
                        value_min,
                        value_avg,
                        value_max,
                        itemid,
                        hour);
        }
        else
        {
                DBexecute("insert into trends (clock,itemid,num,value_min,value_avg,value_max) values (%d," ZBX_FS_UI64 ",%d," ZBX_FS_DBL "," ZBX_FS_DBL "," ZBX_FS_DBL ")",
                        hour,
                        itemid,
                        1,
                        value,
                        value,
                        value);
        }
 
        DBfree_result(result);
 
        return SUCCEED;



この部分を見ると、今までのデータをチェックして、num をインクリメントしている。つまり、何回もこのデータはUPDATEされるということ。
インクリメントされた回数を表しているのなら、納得。このアイテムは5分おきに取得しているので、11回は55分となる。きっちり12回というのもあるだろうし、それ以上というのは、ちょっと頻度多くとってしまったからだろう。

とりあえず、テーブルの構造が分かったので、安心してinsert時にnum = 1 で入れられます。
今後も入れるかもしれません。その時は、シェルスクリプトで計算するのが面倒くさいので、ストアドプロシージャを使って、clockとitemid、valueを渡してチェックとインクリメント、平均値計算をするようにとか思っています。

7/31 追記
続きです。