Help with YAML customization for Microsoft SQL Server Extension

[Joao1]

Hi,

If possible, i need help with something related to the YAML of the extension. I was editing it for a custom extension using the same data source. I was trying to edit a query which is used for the database state, to get information for a specific database. My question:

1. I created a variable with perItem: true 
so that i can define it in each endpoint, which is called:

- id: database

    displayName: Database Name

    perItem: true

    type: text

this is the query:

metrics:

          - key: sql-server.databases.state

            value: col:state

        query: >-

          SELECT

            name                  AS database_name,

            state,

            state_desc,

            recovery_model_desc,

            user_access_desc,

            is_auto_close_on,

            ISNULL(

              DATABASEPROPERTYEX(name, 'Updateability'),

              CASE

                is_read_only

                WHEN

                  0

                THEN

                  'READ_WRITE'

                ELSE

                  'READ_ONLY'

              END

            ) AS updateability

          FROM

            sys.databases

          WHERE

            name = 'var:database'

I tried it like that with 'var:database' and with ${var:database} and both didn't work. Does anyone know a way to dynamically put the information there?

[t_pawlak · Accepted solution]

Hi,
I think "trick" should be easy.
In Extension 2.0 you can’t inject variables inside the SQL text. Instead, you run the query “wide” and then filter the dimension using your per-endpoint variable. That’s the supported pattern.
here is documentation:
Extension YAML file 

So, in your yaml:

vars:
  - id: database
    displayName: Database Name
    perItem: true
    type: text
sqlServer:
  - group: Database state
    query: >-
      SELECT
        name AS database_name,
        state,
        state_desc,
        recovery_model_desc,
        user_access_desc,
        is_auto_close_on,
        ISNULL(
          DATABASEPROPERTYEX(name, 'Updateability'),
          CASE WHEN is_read_only = 0 THEN 'READ_WRITE' ELSE 'READ_ONLY' END
        ) AS updateability
      FROM sys.databases

    # Map the name as a dimension and filter it by the variable
    dimensions:
      - key: mssql.database
        value: col:database_name
        filter: var:database

    metrics:
      - key: sql-server.databases.state
        value: col:state
        type: gauge

Then, in each endpoint’s monitoring configuration, set database to the specific DB (e.g., AdventureWorks). The filter will keep only rows where mssql.database equals that value. Filters can use variables (var:<id>) or constants and are applied at the dimension level; they don’t rewrite the SQL.
Please try this and let me know if it works. Unfortunately, I don't have a way to test it myself.