How to map the parameter to SQL DB Extension Query

[susmita_k]

I want to pass parameter (Different for different monitoring configuration) to my extension queries. How do I achieve that?

[susmita_k]

I have used this in my yaml file to declare the variable and referenced this way :

vars:

  - id: ownerFilter

    displayName: Filter

    type: text

subgroups:

      - subgroup: DB Invalid Object Count

        featureSet: DB Invalid Object Count

        ingest: metrics

        query: >

          select

          count(*) as invalid_object_cnt

          from dba_objects

          where var:ownerFilter

But its not working, its not assigning the variable in the where clause. Could anyone please help on how do I assign the variable to the query ? Thanks in advance.

[Mike_L]

Due to security risks you cannot alter the query from the UI, you'd have to create different queries for the different options. You could toggle on/off queries based on feature sets for example.

[susmita_k]

So in which scenario we use the variable option for SQL extension ? Documentation is not clear on how to use the variable..

[Mike_L]

It can for example be used to add a dimension to the metrics being sent to Dynatrace, maybe with team name or cost center or something similar.

[susmita_k]

So I am now trying to expose the column as a dimension and filter, but it didnt work either.

vars:

  - id: ownerFilter

    displayName: Filter

    type: text

      - subgroup: DB Invalid Object Count

        featureSet: DB Invalid Object Count

        ingest: metrics

        query: >

          select

          count(*) as invalid_object_cnt,

          owner

          from dba_objects

          where status<>'VALID'

          group by owner

        metrics:

          - key: com.dynatrace.extension.sql-oracle.db_invalid_object                      

            value: col:invalid_object_cnt

            type: gauge

        dimensions:

          - key: owner

            value: col:owner

            filter: var:ownerFilter

I'm expecting it will ingest the metrics only when the owner = 'Variable Value', but thats not happening. Any suggestion?