How do I create custom metric alert on Oracle DB instance for the metric I am getting out of my custom SQL Extension?

[susmita_k]

How do I create custom metric alert on Oracle DB instance for the metric I am getting out of my custom SQL Extension?

I am adding a dimension of custom device by adding custom device filter for the DB instance, but its creating the problem at the environment level. Please suggest.. Do I need to add a dimension in my Extension yaml file ?

[rudy_h]

His Susmita,

You may create json files inside alerts folder to define your custom metric alert:

Best Regards,

Rudy

[susmita_k]

Thanks @rudy_h. However, I am trying to use the new davis anomaly detector where I am able to create the alert. But the alert is being created at environment level instead of the database instance. I am looking for option on how do configure the source entity to fix this.

[Mike_L]

How does your yaml look like? Make sure that the metrics it reports are attached to a generic entity, and that the metrics have that entity type as the sourceEntityType in the metric metadata.

[susmita_k]

Hi @Mike_L,

below is my extension.yaml. I have defined my source entity as sourceEntityType: sql:com_dynatrace_extension_sql-oracle_instance. That is not generic entity, but its the oracle DB extension entity. so do I need to define this entity in my extension.yaml ?

name: custom:dynatrace.x.x.x.db.ext
version: "0.0.3"
minDynatraceVersion: "1.283.0"
author:
name: Susmita
metrics:
- key: dynatrace.x.x.x.db.ext.gg_replication_status
metadata:
displayName: GG Replication Status
unit: Count
description: GG Replication Status
metricProperties:
valueType: error
minValue: 0
sourceEntityType: sql:com_dynatrace_extension_sql-oracle_instance
sqlOracle:
- group: Golden Gate Replication Status Check
interval:
minutes: 5
subgroups:
- subgroup: Golden Gate Replication Status Check
featureSet: Golden Gate Replication Status Check
ingest: metrics
query: >
my Query
metrics:
- key: dynatrace.x.x.x.db.ext.gg_replication_status
value: col:OPT_TIME_VAL
type: gauge
dimensions:
- key: gg_tablename
value: col:TABLE_NAME
- key: gg_ops_time_value
value: col:OPT_TIME_VAL

[Mike_L]

This is the definition of that entity:

    - name: sql:com_dynatrace_extension_sql-oracle_host
      displayName: Oracle DB Server
      enabled: true
      rules:
        - idPattern: sql_sql-oracle_{device.address}
          instanceNamePattern: '{instance.host}'
          iconPattern: databaseserver
          sources:
            - sourceType: Metrics
              condition: $prefix(com.dynatrace.extension.sql-oracle)
          attributes:
            - pattern: '{device.address}'
              key: dt.ip_addresses
              displayName: IP Addresses
            - pattern: '{device.name}'
              key: dt.dns_names
              displayName: DNS names
            - pattern: '{device.port}'
              key: dt.listen_ports
              displayName: Listener ports 

As you can see it requires the dimension instance.host to be added to your metrics, and it requires that the metrics start with com.dynatrace.extension.sql-oracle. For you to attach metrics to that entity you'd have to rename your metrics to fit with that condition, and you'd have to query for the instance host in the group attribute, and move your other queries to the sub group so it inherits the top group dimensions.

Example top group:

  - group: Instance
    query: >
      SELECT
        sys_context('USERENV', 'SERVER_HOST') as SERVER_HOST
      FROM dual
    dimensions:
      - key: instance.host
        value: col:SERVER_HOST

[susmita_k]

So my yaml need to be something as below ?

name: custom:dynatrace.x.x.x.db.ext
version: "0.0.3"
minDynatraceVersion: "1.283.0"
author:
name: Susmita
metrics:
- key: dynatrace.x.x.x.db.ext.gg_replication_status
metadata:
displayName: GG Replication Status
unit: Count
description: GG Replication Status
metricProperties:
valueType: error
minValue: 0
sourceEntityType: sql:com_dynatrace_extension_sql-oracle_instance
sqlOracle:
- group: Instance
query: >
SELECT
sys_context('USERENV', 'SERVER_HOST') as SERVER_HOST
FROM dual
dimensions:
- key: instance.host
value: col:SERVER_HOST
interval:
minutes: 5
subgroups:
- subgroup: Golden Gate Replication Status Check
featureSet: Golden Gate Replication Status Check
ingest: metrics
query: >
my Query
metrics:
- key: dynatrace.x.x.x.db.ext.gg_replication_status
value: col:OPT_TIME_VAL
type: gauge
dimensions:
- key: gg_tablename
value: col:TABLE_NAME
- key: gg_ops_time_value
value: col:OPT_TIME_VAL

[Mike_L · Accepted solution]

Much closer, you just also need to rename the metric to fulfill the metric name startsWith condition. You can check if all is correct if you can split by the Oracle entity when charting the metric in the data explorer.

[susmita_k]

Okay, so my metric need to be like this as well - sql:com_dynatrace_extension_gg_replication_status correct - ?

[Mike_L]

In the topology snippet I posted above you can see that the metric name needs to start with 

com.dynatrace.extension.sql-oracle