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

Support: Full-Stack Observability (FSO) Infrastructure
#1

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

#2

Hello @sanjeev.giribhattana

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
      USERNAME: oracledb_user
      PASSWORD: oracledb_password
      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
#3

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

Thank you.

#4

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

#5

is there a way to include multiple query files ?