Zabbix 3.4.5 odbc discovery for MySQL query

[mantonik]

Hi,
I am trying to create monitoring for MySQL query

I create template and in Discovery add discovery rule as
Database monitor

key: db.odbc.discovery[daily_sales,db_qa_masqa_3306]

query
select term_no as TERMNO ,sum(amount) as AMOUNT from p_trx where trx_date > curdate() group by term_no

Query is good, and it retrieves a record from DB like

{"data":[{"{#TERMNO}":"3001","{#AMOUNT}":"10265"},{"{#TERMN O}":"3002","{#AMOUNT}":"525"}]}"

Now I have a problem setup Item prototypes
In name I add
Daily sales term {#TERMNO} - {#AMOUNT}

and I see both values are available for the item but I don't know how to setup type and key that AMOUNT will be listed as latest value for key #TERMNO.

Thank you for suggestions.

----
Zabbix version 3.4.6 ( not 3.4.5 as in the title)

[mantonik]

Hi

I add an item as

tilte: Daily sales term 2: {#TERMNO}
key: db.odbc.select[sales{#TERMNO},db_qa_masqa_3306]
query select sum(amount) as AMOUNT from p_trx where trx_date > curdate() and term_no = {#TERMNO}

now I can see AMOUNT for each terminal, but now discovery is running the second query to get the amount which I was able to get at the first query.

I don't think this is a good solution, I am sure there has to be a better way to get an item from first discovery rule query.

Thank for suggestions.

[mantonik]

I create item prototype
type: Calculated
key: amount[{#AMOUNT}]
formula: {#AMOUNT}

now I have latest data populated for each terminal


screenshots:

https://mariuszantonik.com/blog/2018/01/23/zabbix-discovery-rule-mysql/