How to do discovery rule by oracle.custom.sql of zabbix-agent2?

[dynax60]

Hi all,

Is there a way to do discovery rule by oracle.custom.query[]? For example, I have sql script jobs_discovery.sql (for discovery dba_jobs which will be in Discovery rule):

SELECT job FROM dba_jobs

And I have another sql script job.sql, which will be in Item Prototypes (I will pass job number which I discovered earlier:

SELECT job, broken FROM dba_jobs WHERE job = :1

The main problem is how to do discovery rule?

[cyber]

Discovery works with JSON data. So you need to format your query output accordingly. I think there is "SELECt json_object...." in Oracle (internet says so..:P ) Or maybe there is CSV option so you can then use preprocessing "csv to json". If you are already doing formatting you can already name your fields in json as LLD macros (think of output of built in vfs.fs.discovery item) or you can point your LLD macros to specific json path (https://www.zabbix.com/documentation..._functionality)

[dynax60]

Yes, thanks. For discovery I created query jobs_discovery.sql:

SELECT JSON_ARRAYAGG(JSON_OBJECT('{#JOB}' VALUE JOB) RETURNING CLOB) LLD
  FROM DBA_JOBS

And make a key oracle.custom.query["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE. PASSWORD}","{$ORACLE.SERVICE}",jobs_discovery]

It returns GLOB like this:

[{"{#JOB}":101},{"{#JOB}":349},{"{#JOB}":141},{"{#J OB}":328},{"{#JOB}":348},{"{#JOB}":347},{"{#JOB}": 207},{"{#JOB}":25},{"{#JOB}":287},{"{#JOB}":367},{ "{#JOB}":447},{"{#JOB}":467},{"{#JOB}":487},{"{#JO B}":527},{"{#JOB}":102},{"{#JOB}":627},{"{#JOB}":6 47},{"{#JOB}":687},{"{#JOB}":707},{"{#JOB}":24},{" {#JOB}":407}]

For Item protoype I did this key:

oracle.custom.query["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE. PASSWORD}","{$ORACLE.SERVICE}",job,{#JOB}]

With the sql script job.sql

SELECT broken FROM dba_jobs WHERE job = :1

Now I've got an error:

Cannot create item: item with the same key "oracle.custom.query["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE. PASSWORD}","{$ORACLE.SERVICE}",job,{#JOB}]" already exists.

May be {#JOB} parameter is not resolved.. Meanwhile don't know why.

[dvedadt9]

Hi.
I need help with another approach for configuring LLD discovery based on oracle custom queries.

I am using zabbix version 7.0.2.
I have created Item named "Oracle Event Discovery", type "Zabbix agent", type of information is "text" with key:

oracle.custom.query["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORA CLE. PASSWORD}","{$ORACLE.SERVICE}",RDBMS_Events]

No tags nor preprocessing rules for this item.

Also created custom sql query on RDBMS server on location /etc/zabbix/zabbix_agent2.d/plugins.d/oracle_custom_queries file RDBMS_Events.sql with content:

SELECT event, COUNT(*) AS current_count FROM v$session WHERE wait_class != 'Idle' GROUP BY event ORDER BY current_count DESC

Then I have created discovery rule named "Oracle Event Discovery", type "Dependant Item", and master item "Oracle Event Discovery" described above. Key is simply named as "EVENT".
At discovery rule have preprocessing defined as json path:

$[*].EVENT

At discovery rule also have one LLD macro

{#EVENT}

defined with jsonpath value

$[*].EVENT

Then I have created Item prototype named "Event {#EVENT}" as dependant item of master item "Oracle Event Discovery". Type of information is Numeric (unsigned).
Here are defined 3 preprocessing rules for this item prototype:
- jsonpath:

$[?(@.EVENT=='{#EVENT}')].CURRENT_COUNT

- Left trim: [
- Right trim: ]

Right now I can see that master item "Oracle Event Discovery" is fetching this json data:

[{"CURRENT_COUNT":"10","EVENT":"Event 1"},{"CURRENT_COUNT":"7","EVENT":"Event 2"}]

I cannot see any errors in logs, but no LLD items based on Item prototype "Event {#EVENT}" is collected by zabbix. What am I doing wrong?

Thank you