Defining a RUNS_ON relationship between custom entities and sql_server instance

[MarianneR]

I have developed a Python extension using the 2.0 framework that connects to a Microsoft SQL Server, executes a stored procedure, and reports custom metrics to Dynatrace. I want to link my custom metrics to a host that is different from the one added automatically by the OneAgent, because the host the extension is running on is not necessarily the one that should be associated with the metrics.

I am trying to define a RUNS_ON relationship from custom entities to a sql_server_instance entity.  In my extension.yaml file I have the following:

name: custom:foo-extension
version: 0.0.14
minDynatraceVersion: "1.253"
author:
  name: "Name"

python:
  runtime:
    module: foo_extension
    version:
      min: "3.9"

  activation:
    remote:
      path: activationSchema.json
    local:
      path: activationSchema.json

  featureSets:
    - featureSet: Job
      metrics:
        - key: mssql.job.status
    - featureSet: Job Step
      metrics:
        - key: mssql.jobstep.status


metrics:
  - key: mssql.job.status
    metadata:
      description: last run outcome of the job
      displayName: mssql job status
      unit: gauge
      dimensions:
        - key: job_id
          displayName: Job ID
        - key: job_name
          displayName: Job Name
        - key: category
          displayName: Category
        - key: type
          displayName: Type
        - key: owner
          displayName: Owner
        - key: current_execution_status
          displayName: Current Execution Status
        - key: has_step
          displayName: Total Number of Steps Per Job
        - key: current_execution_step
          displayName: Current Execution Step's Name
        - key: step_id
          displayName: This Step's ID
  - key: mssql.jobstep.status
    metadata:
      description: last run outcome of the job step
      displayName: mssql job step status
      unit: gauge
      dimensions:
        - key: job_id
          displayName: This Job's ID
        - key: job_name
          displayName: This Job's Name
        - key: step_id
          displayName: This Step's ID
        - key: step_name
          displayName: Step Name

topology:
  types:
    - name: sql:sql_server_instance
      displayName: MSSQLSERVER
      enabled: true
      rules:
        - idPattern: sql_server_instance_{originating_server}
          instanceNamePattern: "MSSQLSERVER on {originating_server} (via: {originating_server})"
          iconPattern: mssql
          sources:
            - sourceType: Metrics
              condition: $prefix(mssql.job.)
            - sourceType: Metrics
              condition: $prefix(mssql.jobstep.)
    
    - name: foo-extension:job
      displayName: Job
      enabled: true
      rules:
        - idPattern: "{job_id}"
          instanceNamePattern: "{job_name}"
          iconPattern: mssql
          attributes:
          - key: type
            displayName: Type
            pattern: "{type}"
          - key: owner
            displayName: Owner
            pattern: "{owner}"
          - key: category
            displayName: Category
            pattern: "{category}"
          sources:
            - sourceType: Metrics
              condition: $prefix(mssql.job.)
            - sourceType: Metrics
              condition: $prefix(mssql.jobstep.)
          role: default

    - name: foo-extension:jobstep
      displayName: Job Step
      enabled: true
      rules:
        - idPattern: "{job_id}-{step_id}"
          instanceNamePattern: "{step_name}"
          iconPattern: mssql
          attributes:
            - key: job_id
              displayName: this job's id
              pattern: "{job_id}"
            - key: job
              displayName: this job's name
              pattern: "{job_name}"
            - key: step_id
              displayName: this step's id
              pattern: "{step_id}"
          sources:
            - sourceType: Metrics
              condition: $prefix(mssql.jobstep.)
          role: default

  relationships:
    - enabled: true
      sources:
        - sourceType: Metrics
          condition: $prefix(mssql.job.)
        - sourceType: Metrics
          condition: $prefix(mssql.jobstep.)
      fromType: foo-extension:jobstep
      typeOfRelation: CHILD_OF
      toType: foo-extension:job

    - enabled: true
      sources:
        - sourceType: Entities
          mappingRules:
            - sourceProperty: originating_server
              sourceTransformation: To lower case
              destinationProperty: server
              destinationTransformation: To lower case
      fromType: foo-extension:job
      typeOfRelation: RUNS_ON
      toType: sql:sql_server_instance

    - enabled: true
      sources:
        - sourceType: Entities
          mappingRules:
            - sourceProperty: originating_server
              sourceTransformation: To lower case
              destinationProperty: server
              destinationTransformation: To lower case
      fromType: foo-extension:jobstep
      typeOfRelation: RUNS_ON
      toType: sql:sql_server_instance

(The originating server dimension is specified for both of my custom metrics (although it is not mentionned in the metrics section), and its value is the name of a host already present on Dynatrace.)

With this code, the CHILD_OF relationship is working properly, but when I look at the runs_on field of my custom entities on Dynatrace I see "null"  for all of them.

I have previously been able to specify the dt.entity.host dimension by assigning its value to a host's id I found in Dynatrace, but I  have never gotten the relationship to work. 

What else could I try to get my topology working in Dynatrace? Is my mapping done correctly and is this the right relationship type? Is there something else missing in my topology?

Any help on the matter is greatly appreciated!

[MarianneR · Accepted solution]

I ended up using a child_of relationship instead. I also realized that the sql:sql_server_instance entity should not be defined in my code since its already present in Dynatrace. This created a second entity with the same name as the one in Dynatrace and caused issues with mapping. Here is what worked for me:

metrics:
  - key: mssql.job.status
    metadata:
      description: last run outcome of the job
      displayName: mssql job status
      unit: gauge
      dimensions:
        - key: job_id
          displayName: Job ID
        - key: job_name
          displayName: Job Name
        - key: category
          displayName: Category
        - key: type
          displayName: Type
        - key: owner
          displayName: Owner
        - key: current_execution_status
          displayName: Current Execution Status
        - key: has_step
          displayName: Total Number of Steps Per Job
        - key: current_execution_step
          displayName: Current Execution Step's Name
        - key: step_id
          displayName: This Step's ID
        - key: originating_server
          displayName: Originating Server
  - key: mssql.jobstep.status
    metadata:
      description: last run outcome of the job step
      displayName: mssql job step status
      unit: gauge
      dimensions:
        - key: job_id
          displayName: This Job's ID
        - key: job_name
          displayName: This Job's Name
        - key: step_id
          displayName: This Step's ID
        - key: step_name
          displayName: Step Name
        - key: originating_server
          displayName: Originating Server

topology:
  types:
    - name: foo-extension:job
      displayName: Job
      enabled: true
      rules:
        - idPattern: mssql_job_{job_id}
          instanceNamePattern: "{job_name}"
          iconPattern: mssql
          attributes:
          - key: type
            displayName: Type
            pattern: "{type}"
          - key: owner
            displayName: Owner
            pattern: "{owner}"
          - key: category
            displayName: Category
            pattern: "{category}"
          - key: originating_server
            displayName: originating_server
            pattern: "{originating_server}"
          sources:
            - sourceType: Metrics
              condition: $prefix(mssql.job.)
            - sourceType: Metrics
              condition: $prefix(mssql.jobstep.)
          role: default

    - name: foo-extension:jobstep
      displayName: Job Step
      enabled: true
      rules:
        - idPattern: mssql_job_{job_id}_{step_id}
          instanceNamePattern: "{step_name}"
          iconPattern: mssql
          attributes:
            - key: job_id
              displayName: this job's id
              pattern: "{job_id}"
            - key: job
              displayName: this job's name
              pattern: "{job_name}"
            - key: step_id
              displayName: this step's id
              pattern: "{step_id}"
            - key: originating_server
              displayName: originating_server
              pattern: "{originating_server}"
          sources:
            - sourceType: Metrics
              condition: $prefix(mssql.jobstep.)
          role: default

  relationships:
    - enabled: true
      sources:
        - sourceType: Metrics
          condition: $prefix(mssql.job.)
        - sourceType: Metrics
          condition: $prefix(mssql.jobstep.)
      fromType: foo-extension:jobstep
      typeOfRelation: CHILD_OF
      toType: foo-extension:job

    - enabled: true
      sources:
        - sourceType: Entities
          mappingRules:
            - sourceProperty: originating_server
              sourceTransformation: To lower case
              destinationProperty: server
              destinationTransformation: To lower case
      fromType: foo-extension:job
      typeOfRelation: CHILD_OF
      toType: sql:sql_server_instance