Entity Selector with SQL Server extension 2.3.0

[TimeZero]

Hi!

We have auto tags for "sql:sql_server_availability_replica" for our SQL Servers, but since the extension upgraded to 2.3.0, it doesn't work anymore. I know in the patch notes it says the relation changed for the entity selector, so I updated our query, but it still doesn't work.

Here's the old query before v2.3.0:
type("sql:sql_server_availability_replica"),toRelationships.runsOn(type("sql:sql_server_availability_database"),fromRelationships.isInstanceOf(type("sql:sql_server_database"),fromRelationships.isChildOf(type("sql:sql_server_instance"),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),fromRelationships.isInstanceOf(type("HOST_GROUP"),detectedName("xxxxxxxxxx")))))))

Here's the updated query after v2.3.0:

type("sql:sql_server_availability_replica"),fromRelationships.runsOn(type("sql:sql_server_instance"),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),fromRelationships.isInstanceOf(type("HOST_GROUP"),detectedName("xxxxxxxxxx")))))

Any help would be appreciated, thanks!

[vagiz_duseev · Accepted solution]

If we the expression and compare it with relationship definitions from `extension.yaml` downloadable from the Hub we can verify it step by step:

# Get all Availability Replica entities...
type("sql:sql_server_availability_replica"),
# ...which have a toType "RUNS_ON" relationship to Instance entities.
fromRelationships.runsOn(
  # Filter down to only those Instances...
  type("sql:sql_server_instance"),
  # ...that have a toType "RUNS_ON" relationship to SQL Host entitites.
  fromRelationships.runsOn(
    # Filter down to only those SQL Hosts...
    type("sql:sql_server_host"),
    # ...that have a toType "SAME_AS" relationship to actual HOSTs.
    fromRelationships.isSameAs(
      # And finally, only consider HOSTs that are part of the HOST_GROUP...
      type("HOST"),
      fromRelationships.isInstanceOf(
        type("HOST_GROUP"),
        # ...with a specific name.
        detectedName("xxxxxxxxxx")
      )
    )
  )
)

Relationship between Availability Replica to Instance:

typeOfRelation: RUNS_ON
fromType: sql:sql_server_availability_replica
toType: sql:sql_server_instance
enabled: true
sources:
  - sourceType: Metrics
    condition: $prefix(sql-server)

Relationship between Instance and SQL Host:

typeOfRelation: RUNS_ON
fromType: sql:sql_server_instance
toType: sql:sql_server_host
enabled: true
sources:
  - sourceType: Metrics
    condition: $prefix(sql-server)

Relationship between SQL Host and HOST:

typeOfRelation: SAME_AS
fromType: sql:sql_server_host
toType: host
enabled: true
sources:
  - sourceType: Entities
    mappingRules:
      - sourceProperty: dt.ip_addresses
        sourceTransformation: To lower case
        destinationProperty: ipAddress
        destinationTransformation: To lower case

Based on the above, the expression of entity selector is correct! The only place I can see it breaking down is in the relationship between the SQL Host and HOST, where it compares the detected IP Addresses of both.

The IP Address of the SQL Server Instance we connect to must match one of the IP Addresses detected on the HOST by OneAgent. But this somehow worked in previous version. 

If nothing, I recommend opening a support ticket.

[nils_stellhorn]

I also used that pattern, but based on metadata. Also, in the answer from vagiz_duseev, the availability databases are missing. So here are all my tagging rules for SQL:

SQL host:

type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),tag([Environment]Application:NAME))

SQL instance:

type("sql:sql_server_instance"),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),tag([Environment]Application:NAME)))

SQL server database:

type(sql:sql_server_database),fromRelationships.isChildOf(type(sql:sql_server_instance),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type(HOST),tag([Environment]Application:NAME))))

SQL server availability replica:

type("sql:sql_server_availability_replica"),fromRelationships.isSameAs(type(sql:sql_server_instance),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),tag([Environment]Application:NAME))))

SQL server availability group:

type(sql:sql_server_availability_group),toRelationships.isInstanceOf(type(sql:sql_server_availability_replica),fromRelationships.isSameAs(type(sql:sql_server_instance),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),tag([Environment]Application:NAME)))))

SQL server availability database:

type(sql:sql_server_availability_database),fromRelationships.runsOn(type(sql:sql_server_availability_replica),,fromRelationships.isSameAs(type(sql:sql_server_instance),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),tag([Environment]Application:NAME)))))

[Damian_G]

I've tried your tagging rules and the ones related with SQL host, SQL instance and SQL server database worked, but the ones related with SQL server availability replica, SQL server availability group and SQL server availability database didn't return any data.

[AntonPineiro]

Hi,

Topology can be different depending of SQL extension version. Maybe it was updated in a release. I would recommend checking topology relationships in your environment:

Best regards

[Damian_G]

I've reviewed the settings and topology of my SQL Extension:

type("sql:sql_server_availability_replica"),fromRelationships.isSameAs(type(sql:sql_server_instance),fromRelationships.runsOn(type("sql:sql_server_host"),fromRelationships.isSameAs(type("HOST"),tag([Environment]Application:NAME))))

should work, but I don't get any entity.

[nils_stellhorn]

Hi Damian,

I just checked it and we do indeed get the proper data from the relationship-based entity selector. I would recommend to check the relationships for your replicas via the API. You can open up the replica you're looking at, copy the custom_device ID and use it in the monitored entities API with the GET /entities/{entityId} API to see the relationships that are present in your environment.

[AntonPineiro]

Hi,

I would recommend create a metric selector per SQL entity:

• Adding a tag from host to sql_server_host (is_same_as).
• Adding a tag from sql_server_host to sql_server_instance (runs_on).
• Adding a tag from sql_server_instance to sql_server_database (is_child_of).
• Adding a tag from sql_server_database to sql_server_availability_database (is_instante_of).
• Adding a tag from sql_server_availability_database to sql_server_availability_replica (runs_on).

Issue is you cannot use placeholder, it means, you need to create a custom metric selector adding tag value.

This product idea would be helpful to avoid creating rules per tag.

Best regards