# How to specify multiple custom metric queries for Oracle DB monitoring integration

Hello all,

I am trying to setup Oracle DB monitoring and would like to use custom queries. I have set CUSTOM_METRICS_CONFIG parameter in the oracledb-config.yml file. The queries file has following entries:

queries:

• query: >-
select round(((space_limit-space_used)/space_limit)*100,2) free_space_recovery_area
from V$RECOVERY_FILE_DEST metric_types: gets: gauge • query: >- select round((CURRENT_UTILIZATION*100)/LIMIT_VALUE,2) pct_proc_limit from v$resource_limit where RESOURCE_NAME=‘processes’
metric_types:
gets: gauge

In the Data Explorer on NR dashboard, I see PCT_PROC_LIMIT metric but not FREE_SPACE_RECOVERY_AREA. I will be using these in different charts and may setup alert conditions as well. How do I specify multiple custom queries?

Any help/guidance would be greatly appreciated.

Thanks,

Sanjeev

In order to have multiple custom SQL queries you will need to do the below:

• Add the queries to oracledb-custom-query.yml, and reference that file on your configuration. Therefore, CUSTOM_METRICS_CONFIG is only enabled if CUSTOM_METRICS_QUERY is not present
Per what you mentioned you are doing correctly, however, double-check if the Yml files are correct format:
  - name: nri-oracledb
env:
METRICS: true
SERVICE_NAME: ORACLE
HOSTNAME: 127.0.0.1
PORT: 1521
ORACLE_HOME: /app/oracle/product/version/database
CUSTOM_METRICS_CONFIG: 'C:\path\to\oracledb-custom-query.yml'
interval: 15s
labels:
environment: production



Here’s an example oracledb-custom-query.yml

  #Metric names are set to the column names in the query results
- query: >-
SELECT
SUM(stat.gets) AS "gets",
SUM(stat.waits) AS "waits",
SUM(stat.waits)/SUM(stat.gets) AS "ratio",
inst.inst_id
FROM GV$ROLLSTAT stat, GV$INSTANCE inst
WHERE stat.inst_id=inst.inst_id
GROUP BY inst.inst_id

#If not set explicitly here, metric type will default to
#'gauge' for numbers and 'attribute' for strings
metric_types:
gets: gauge

#If unset, sample_name defaults to OracleCustomSample
sample_name: MyCustomSample


If the format is all correct and you are still facing issues to have multiple queries then please enable the verbose let it run for some minutes and review if there is any error that can help further troubleshooting it.

References:

I hope it helps.

1 Like

@vhenrique Thanks for the reply. I did have a formatting mistake. Once I fixed it, it is working as expected.

Thank you.

Hello, @sanjeev.giribhattana great to hear it. happy to help always. Thank you for letting me know that it worked

is there a way to include multiple query files ?