Seeking help on SQL query for the group level in SQL Data Source migration to Extension 2.0

[DarshanDoshi]

Hi There,
I am working on migration task on our Custom DB Query written in python on Dynatrace managed using extension 1.0 to Extension 2.0 - SQL Data Source. Though it is successful, I wasn't able to define it as group and sub-group in our scenario. Seeking for a help in what SQL query should I define in the group level, in case I re-define 2nd, 3rd and 4th group in below example as a sub-group. Such that I can make use of same extension up to 100 metrics. Otherwise, I will be limited to 10 metrics only (assume if only one metric defined against each sql query in a group and no sub-group exist). Note, where possible I tried to merge multiple queries into one single queries. In below example, assume I cannot merge queries into one single sql statement. I do make use of dimension and multiple keys where possible. These queries are specific to application tables and not any generic that can be used across the database or server.

sqlServer:

  - group: GroupDBOne_ServerOne

    featureSet: FeatureSetOne_TableOne

    query: SELECT COUNT(*) AS ColumnO1 FROM TableO (NOLOCK) WHERE condition

    metrics:

      - key: key.colO1

        value: col:ColumnO1

        type: gauge

    interval:

      minutes: 15

    timeout: "120"

  - group: GroupDBTwo_SeverTwo_1

    featureSet: FeatureSetTwo_TableOne_1

    query: SELECT COUNT(*) AS ColumnT1 FROM TableT (NOLOCK) WHERE ColumnT LIKE'TextOne'

    metrics:

      - key: key.ColT1

        value: col:ColumnT1

        type: gauge

    interval:

      minutes: 15

    timeout: "120"

  - group: GroupDBTwo_SeverTwo_2

    featureSet: FeatureSetTwo_TableOne_2

    query: SELECT COUNT(*) AS ColumnT2 FROM TableT (NOLOCK) WHERE ColumnT LIKE'TextTwo'

    metrics:

      - key: key.ColT2

        value: col:ColumnT2

        type: gauge

    interval:

      minutes: 15

    timeout: "120"

  - group: GroupDBTwo_SeverTwo_3

    featureSet: FeatureSetTwo_TableOne_3

    query: SELECT COUNT(*) AS ColumnT3 FROM TableT (NOLOCK) WHERE ColumnT LIKE'TextThree'

    metrics:

      - key: key.ColT3

        value: col:ColumnT3

        type: gauge

    interval:

      minutes: 15

    timeout: "120"

Thanks and Regards,
Darshan Doshi

[TomásSeroteRoos · Accepted solution]

Hi Darshan,

If all your queries are completely unrelated, I think you should be able to do something like this:

- group: my_group
    subgroups:
      - subgroup: test
        ingest: metrics
        query: SELECT value FROM table
        metrics:
        - key: mykey
          value: col:value

and have 10 subgroups on each of your 10 groups, so 100 queries in total.

Group level queries can be useful if you want to extract the same dimension for all the queries in the subgroup. For example, on our Oracle DB extension, we use a group level query like this:

- group: Instance
    query: >
      SELECT DISTINCT DATABASE_TYPE FROM GV$INSTANCE
    dimensions:
      - key: database.type
        value: col:DATABASE_TYPE
    subgroups:
       ...

to have the database.type dimension as part of every metric extracted from the subgroup.