Collect PGSQL Tables Metrics on per-table Basis

[Evgeny_A]

Hello Everyone.

Zabbix v6.2.7
PSQL v14.5

I started to use Zabbix recently and I got how to collect some basic metrics and some not (i.e. self-made calculated).
However, currently I stuck with one specific thing.

What I'd like to create:
I'd like to collect some PGSQL metrics (there are some service tables that allow this, so I just need custom query for each metric, e.g. pg_stat_all_tables) and show them on per-table basis with table name labels.
E.g. display changes of size for top N largest tables. Let's consider N=1 for simplicity in the examples below.

What I currently have:
I created custom SQL query, that returns just two required columns 'name' and 'size', made it all-set and successfully receive JSON at Zabbix server using this expression:

pgsql.custom.query["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}","size"]

Data received are like

[{"name":"tablename", size:123}]

I can parse size value using JSONPath or JavaScript code and I'm able to get some usual graph from it (for single value) but it wouldn't have "name" label. And I have no idea of how to use received name to label the graph line with it.

The question is:
Is it possible at all? If yes, how could this be achieved? If no, possible I'm moving the wrong way and there is some more common flow giving the desired result.

[Evgeny_A]

Finally I managed to create basic solution using this way:

1. Update custom SQL query to return top 5 (of cause, I may use query with parameters to customize, if necessary) data like this: [{ "{#TABLENAME}": "name1", "metric": numeric_value1 }, ..., {"{#TABLENAME}": "name5", "metric": numeric_value5 }]
2. Create "master" item that receives the JSON above as is and returns it as text
3. Create discovery rule of type "Dependent item" that uses item from the previous step as master. (LLC macros didn't work for me by some reason so I moved macros to step 1 query).
4. Create item prototype with key <...>.top["{#TABLENAME}"] and name "<...> {#TABLENAME}" that has preprocessing rule like
   

   	var json = JSON.parse(value);
   	var result = json.filter(function(v) { return v["\{#TABLENAME\}"] == "{#TABLENAME}"; })[0];
   	return result.<metric>;

5. Create graph prototype named "<...> {#TABLENAME}" and single entry in Items: item prototype from the previous step.

After that all top 5 items were discovered and I was able to add object of type "Graph prototype" to dashboard to see this set of data in dynamic.

BTW, I'm still not completely sure in a couple of things:

1. How would this work in dynamic, i.e. when top 5 tables change?
   My guess here is that table moved to 6th place would show "no data" and the new one 5th would be displayed. After "Keep lost resources period" of discovery rule 6th table would be removed from this list. Am I correct?
2. Is it possible to draw graph prototype as separate lines placed to the same grid? Current representation (MxN table of graphs for each discovered value, each having own scaling) looks huge and less usable then, say, "Graph" where you could hover values and compare neighboring graphs in the same scale.

[cyber]

Your assumption 1 is correct.. the table will be marked as not discovered any more and removed after expiry time...

You can set item name pattern in graph... so if your table items have somekind of matching part, you can show them all at once... But you have to do it in "real" graph, not prototype...