Writing Directly to Zabbix Database

[hellfire27]

Anyone know of any potential issues writing directly to the database? Mostly I just want to INSERT or UPDATE the host_inventory table, but perhaps the host table as well. I realize there is the API, and I did mess around with it a bit in Python, but I would rather cook up some simple bash scripts to do what I need.

I tested some inserts on a test Zabbix box and everything shows up fine but I am wondering if this will break something in the future. If anyone can foresee some problems with this, I would like to know. Thank you,

[jairoalves]

I was wondering the same thing.

I just want to update a host inventory, but maybe there's some potential issue with that.

Do anyone know if editing the inventory info directly to the database is fine?

[hellfire27]

Just a follow up; I used Python to talk to the Zabbix API and did all my host creations and host inventories that way. Created a web interface to front the Python script so I could just simply drop the information in form and submit. This also allowed me to add some intelligence checking into the mix as well. The API is pretty simple to use once you get the hang of it, and quite feature rich.

[kloczek]

I was wondering the same thing.

I just want to update a host inventory, but maybe there's some potential issue with that.

Do anyone know if editing the inventory info directly to the database is fine?

You need to check closer zabbix caching code.
In src/libs/zbxdbcache/dbcache.c you can find some traces of caching inventory values in zabbix caches. If some inventory updates would be done by database content updates it may be a risk that this may cause some issues.

https://www.zabbix.com/forum/showthread.php?t=36842

[jairoalves]

You need to check closer zabbix caching code.
In src/libs/zbxdbcache/dbcache.c you can find some traces of caching inventory values in zabbix caches. If some inventory updates would be done by database content updates it may be a risk that this may cause some issues.

https://www.zabbix.com/forum/showthread.php?t=36842

Thanks kloczek,

Reading the link you referenced, If I understand correctly, there may be problems if one updates the inventory fields directly in the database, since that change might not be reflected in the zabbix caches and that cache is used by the create / update function when a host is being updated. Is that correct?

If it is so, supposing I have set the cache to refresh every 60 seconds, would it suffice if one just waits at least this refresh time before attempting to perform any other host changes ?

[kloczek]

Thanks kloczek,

Reading the link you referenced, If I understand correctly, there may be problems if one updates the inventory fields directly in the database, since that change might not be reflected in the zabbix caches and that cache is used by the create / update function when a host is being updated. Is that correct?

If it is so, supposing I have set the cache to refresh every 60 seconds, would it suffice if one just waits at least this refresh time before attempting to perform any other host changes ?

Zabbix caching infrastructure dramatically reduces number of selects.
If you are going to flush cached data you will probably end up in situation when selects pressure on DB will be significantly higher and obtaining necessary data will be done with higher latency.
Keeping warm cache content will be even more important when all web frontend will be using zabbix API on obtaining series of data which needs to be presented in form of graphs. Such redesign in zabbix is now one of the mail goals is upcoming new major release.

Risk of doing some modifications not over API (like it is in pointed link) is that those data will be not coherent in cache or updates propagated from items to cache and DB backend will instantly lost if this will be done done straight in DB content.
Again: I'm not sure about exact impact (maybe even there is no risk here) however to say something more about such way of doing updates you need to analyze source code.
Complexity of doing updates over API is not so big and number of possible updates cannot be be high so to be honest I really don't understand why you are going to do those updates in some way which adds some level of the risk
Doing those updates over API guarantees that it will be working with future zabbix version.

BTW: IMO it would be good to redesign zabbix inventory data representation from current per host fixed records to dynamically connected inventory fields per items. With additional layer mapping such key<>value pairs it will be possible easier to add user defined inventory fields with any names (not only those statically defined in current inventory definition).

I see some potential to organize some 3D metrics over inventory.
In zabbix jira you can find https://support.zabbix.com/browse/ZBXNEXT-2760
In this ticket is change allowing to monitor number of unsupported items per host. Such counter is very important because in case some delays in monitoring when data metrics cannot be obtained in some exact time metrics is marked as "unsupported". Monitoring some fluctuations related to number of of items in such state can be used as "not strictly defined early alarm that SomethingIsWrong(tm) with monitoring".

I have in my monitoring number of unsupported items mapped values from this key to inventory "Site country" (one of few which was not used ). By observing https://zabbix.web/hostinventoriesov...y=site_country we have instant informations about aggregated data about number of items on all hosts in unsupported state.
Such way of observing those data is waaaay easier to digest/analyze than over "Latest data".
Effectively inventory creates vector of such data which can be (theoretically) sampled on regular base and plotted in 3D graph showing very useful informations about all hosts monitoring state.
At the moment I'm trying to encourage few people to implement necessary changes in zabbix to remove static inventory record per host and replace this by 1:1 item<>inventory_value pairs. On next stage can be possible to replace static set of inventory fields by user defined inventory. Maybe in future will be possible to sample inventory data per group of hosts of globally to produce vector of some data which can be possible to present in 2D flame graphs or 3D graphs.

What I'm trying to say is that whatever may be changed in zabbix inventory to implement above it will be colliding with doing updates straight by executing SQL queries. However .. if such updates will be done over API probably you scripts used on your updates still could be used

PS. I have many examples of the 2D data changing in time which would be good to monitor and present in some way.
Some short example:

# dtrace -qn 'syscall::write:entry /execname == "mysqld"/ {self->stime = timestamp;} syscall::write:return /self->stime != 0/ {@LWrite = quantize(timestamp - self->stime);} tick-10s {printa(@LWrite);}'
            value  ------------- Distribution ------------- count
             256 |                                         0
             512 |                                         10
            1024 |                                         15544
            2048 |                                         28204
            4096 |@@@@@@@@@@                               713511
            8192 |@@@@@@@@@@@@@@@@@@@@@@@@@@               1829254
           16384 |@@                                       109075
           32768 |@                                        59445
           65536 |                                         9685
          131072 |                                         2268
          262144 |                                         1408
          524288 |                                         621
         1048576 |                                         830
         2097152 |                                         338
         4194304 |                                         377
         8388608 |                                         598
        16777216 |                                         260
        33554432 |                                         9
        67108864 |                                         3
       134217728 |                                         0

Above data shows distributions of physical IOs latencies done by mysqld quantized on N^2 nanoseconds base. Above command if not interruped by ctrl-c will be printing updated distribution of latencies in form of ascii art graph every 10s.
In this case is used SSD storage. I want to have possibility to observe long term changes to see any degradations of SSD storage caused by approaching to flash max writes.

[jairoalves]

Zabbix caching infrastructure dramatically reduces number of selects.
If you are going to flush cached data you will probably end up in situation when selects pressure on DB will be significantly higher and obtaining necessary data will be done with higher latency.
Keeping warm cache content will be even more important when all web frontend will be using zabbix API on obtaining series of data which needs to be presented in form of graphs. Such redesign in zabbix is now one of the mail goals is upcoming new major release.

Risk of doing some modifications not over API (like it is in pointed link) is that those data will be not coherent in cache or updates propagated from items to cache and DB backend will instantly lost if this will be done done straight in DB content.
Again: I'm not sure about exact impact (maybe even there is no risk here) however to say something more about such way of doing updates you need to analyze source code.
Complexity of doing updates over API is not so big and number of possible updates cannot be be high so to be honest I really don't understand why you are going to do those updates in some way which adds some level of the risk
Doing those updates over API guarantees that it will be working with future zabbix version.

BTW: IMO it would be good to redesign zabbix inventory data representation from current per host fixed records to dynamically connected inventory fields per items. With additional layer mapping such key<>value pairs it will be possible easier to add user defined inventory fields with any names (not only those statically defined in current inventory definition).

I see some potential to organize some 3D metrics over inventory.
In zabbix jira you can find https://support.zabbix.com/browse/ZBXNEXT-2760
In this ticket is change allowing to monitor number of unsupported items per host. Such counter is very important because in case some delays in monitoring when data metrics cannot be obtained in some exact time metrics is marked as "unsupported". Monitoring some fluctuations related to number of of items in such state can be used as "not strictly defined early alarm that SomethingIsWrong(tm) with monitoring".

I have in my monitoring number of unsupported items mapped values from this key to inventory "Site country" (one of few which was not used ). By observing https://zabbix.web/hostinventoriesov...y=site_country we have instant informations about aggregated data about number of items on all hosts in unsupported state.
Such way of observing those data is waaaay easier to digest/analyze than over "Latest data".
Effectively inventory creates vector of such data which can be (theoretically) sampled on regular base and plotted in 3D graph showing very useful informations about all hosts monitoring state.
At the moment I'm trying to encourage few people to implement necessary changes in zabbix to remove static inventory record per host and replace this by 1:1 item<>inventory_value pairs. On next stage can be possible to replace static set of inventory fields by user defined inventory. Maybe in future will be possible to sample inventory data per group of hosts of globally to produce vector of some data which can be possible to present in 2D flame graphs or 3D graphs.

What I'm trying to say is that whatever may be changed in zabbix inventory to implement above it will be colliding with doing updates straight by executing SQL queries. However .. if such updates will be done over API probably you scripts used on your updates still could be used

PS. I have many examples of the 2D data changing in time which would be good to monitor and present in some way.
Some short example:

# dtrace -qn 'syscall::write:entry /execname == "mysqld"/ {self->stime = timestamp;} syscall::write:return /self->stime != 0/ {@LWrite = quantize(timestamp - self->stime);} tick-10s {printa(@LWrite);}'
            value  ------------- Distribution ------------- count
             256 |                                         0
             512 |                                         10
            1024 |                                         15544
            2048 |                                         28204
            4096 |@@@@@@@@@@                               713511
            8192 |@@@@@@@@@@@@@@@@@@@@@@@@@@               1829254
           16384 |@@                                       109075
           32768 |@                                        59445
           65536 |                                         9685
          131072 |                                         2268
          262144 |                                         1408
          524288 |                                         621
         1048576 |                                         830
         2097152 |                                         338
         4194304 |                                         377
         8388608 |                                         598
        16777216 |                                         260
        33554432 |                                         9
        67108864 |                                         3
       134217728 |                                         0

Above data shows distributions of physical IOs latencies done by mysqld quantized on N^2 nanoseconds base. Above command if not interruped by ctrl-c will be printing updated distribution of latencies in form of ascii art graph every 10s.
In this case is used SSD storage. I want to have possibility to observe long term changes to see any degradations of SSD storage caused by approaching to flash max writes.

Kloczek, your explanation makes plenty of sense. I wasn't really seing the whole picture here. Even if it doesn't break things, I would probably be losing performance and potentially assuming unnecessary risk.

Regarding the inventory. I think it makes total sense to allow users to define custom inventory fields, as you said. Also, to use as much of live items on inventory as possible. On the other hand, there is always that field not tied to the hardware, for example some spreadsheet data. If one wants to use Zabbix inventory to integrate that, even with the API, it would be cool, for example, to track the history of those fields as if they were items. But I digress.

Thank you for the valuable input

[kloczek]

If one wants to use Zabbix inventory to integrate that, even with the API, it would be cool, for example, to track the history of those fields as if they were items. But I digress.

It is already implemented
If you have automatic host inventory enabled and inventory value is mapped from last value of some item you can check history of the source item history values.

[jairoalves]

It is already implemented
If you have automatic host inventory enabled and inventory value is mapped from last value of some item you can check history of the source item history values.

Yes, I see this is the case with the automatic inventory items. However, if you have some host data in a spreadsheet, like a contract number, and you populate some zabbix inventory field with this (offline) info, I think there isn't a way to track that history.

[kloczek]

Yes, I see this is the case with the automatic inventory items. However, if you have some host data in a spreadsheet, like a contract number, and you populate some zabbix inventory field with this (offline) info, I think there isn't a way to track that history.

I can imagine some set conditions which can provide zabbix automatic feeding per host data to some items. If your spreadsheet/database can be processed to obtain per host inventory data using for example external script items queried one time a day you can put those data in hosts items. As long as those data would be injected to the zabbix over such items you can start mapping them automatically to per host inventory entries.
External script item script is executed on proxy/server.

[jairoalves]

I can imagine some set conditions which can provide zabbix automatic feeding per host data to some items. If your spreadsheet/database can be processed to obtain per host inventory data using for example external script items queried one time a day you can put those data in hosts items. As long as those data would be injected to the zabbix over such items you can start mapping them automatically to per host inventory entries.
External script item script is executed on proxy/server.

so, there is a way!

that's something we can do, for sure. Good idea, thanks!

[kloczek]

so, there is a way!

that's something we can do, for sure. Good idea, thanks!

You know .. zabbix it is a kind of container for some cups or glasses for some monitoring data.
All what you need is only proper bucket or ladle and something to drink to start filling those glasses to have some nice drinking party
If you will be using tea spoons on distributing your drinks all guests will be unhappy and you will be spending most of the time filling glasses :P

In your initial email you started from kind of wrong question asking "I'm organizing party. How to have all guests glasses instantly/magically full?. Mounting network of pipes? probably it will start causing some accidents (especially if few quests will overuse some drinks ). Mounting on sealing sprinkles with glasses motion detectors? probably it will be after all a lot of liquid on the floor."
So instead asking how to mount those pipes or sprinkles you should start from assessing your current situation that "on my party all my guest most of the time are spending talking with empty glasses in hands. How to make them happier?". So with such formed problem possible answers could be:
- hire waiters
- put on the center of the room big vase with punch

Remember that sometimes when you have no simple/quick/elegant answer always is possible that you are asking using wrong questions