DLQ Filter events or timeseries by comparing contents of array fields

[Landho]

Hello - new to DT and just deploying it.  So far I am struggling to fully mature my DQL savvy - so this question is my first.

I'm trying to setup dashboard filters to filer by Data Center.   I already have the dashboard variable generate a dropdown of available data centers:

Drop down to select $Data_Center, this is a friendly list of geoloc_site which shows the entitiy name.

fetch dt.entity.geoloc_site
| expand managementZones
| dedup entity.name
| fields entity.name

The selection from the dropdown assigns the $geoloc_siteID as the "id" field of the data center, which is then used within DQL tiles in the dashboard.

fetch dt.entity.geoloc_site
| expand managementZones
| filter entity.name == $Data_Center
| dedup entity.name
| fields id

This works very well when querying dt.entity.host for things:

fetch dt.entity.host
| filterOut isMonitoringCandidate == true
| filter belongs_to[dt.entity.geoloc_site] == $geoloc_siteID
| summarize count(), alias: hosts

However - I am struggling to figure out the syntax or most effective/efficient DQL to filter timeseries or events (or other tables) using the array fields which contain the host id's.

I can get the hosts which are part of a geoloc_site as:

fetch dt.entity.geoloc_site | fieldsAdd contains[dt.entity.host]

or get the geoloc_site from the dt.entity.host directly as (statically assigned geoloc-site in the example below)

fetch dt.entity.host
| filter matchesValue(belongs_to[dt.entity.geoloc_site], "GEOLOC_SITE-173D0FB9BA7EB97B")
| fields id

Where I am struggling is to compare the array fields to use in  filters - like this events  - I'd like to filter the events by hosts which are part of a specific geoloc_site... in this example below - i'd like to filter the events such that hosts ID's in affected_entity_ids related_entity_id array fields are also in the geoloc_site (by filter on where array contents in fetch dt.entity.geoloc_site | fieldsAdd contains[dt.entity.host]??)

What I am struggling to figure out is how to compare / filter array field in timeseries or events or other table based on the contents of the geoloc_site array field contents or the dt.entity.host array field of similar.

fetch events, from: toTimestamp("2025-02-28T19:37:00.000Z")-6h-10m, to: toTimestamp("2025-02-28T19:37:00.000Z")
| filter dt.system.bucket == "default_davis_events"
| filter event.kind == "DAVIS_PROBLEM"

// filter here also where any hostid within affected_entity_ids or related_entity_ids is part of geoloc_site???

| filter contains(toString(affected_entity_ids),"HOST") or contains(toString(related_entity_ids),"HOST")
| sort timestamp

ANY suggestions to join or otherwise filter where affected_entity_id's or related_entity_ids are in geohost_site or similar?

[Etienne1]

Hi,

If I understand correctly, you want to have Davis events by geolocation site?

I think you should use the lookup function to make 2 queries in one.

https://docs.dynatrace.com/docs/shortlink/correlation-and-join-commands#lookup

You should take inspiration from ‘Example 2: Nested field as join condition’.

And add something like this to the end of your DQL

| lookup [fetch dt.entity.geoloc_site], sourceField:host, lookupField:entity.name, fields:{id}

[Landho]

I've looked at joins - thinking this would be more efficient than lookups - and attempted them thoroughly.  The documentation of them and DQL in general is a bit thin, and Davis Copilot is no help.  The examples are not even using real schema examples.  I have not stubled across an example or write up on how to compare contents of arrays with another field in a join (nor lookup)

Where I struggle with the join specifically is that I'm attempted to join two array fields within events which contain hosts id's...  so, where is the example to interpret array field contents (affected_entity_ids and related_entitiy_ids) on the host_id which is part of the geoloc_site?

[Landho]

I'm still stumped on this... certainly I can't be the only person out there attempting to filter events (or other things) based on the data center the host is in?

Simplifying this greatly to try and decipher:

fetch events
| filter dt.system.bucket == "default_davis_events"
| filter event.kind == "DAVIS_PROBLEM"
| filter contains(toString(affected_entity_ids),"HOST") or contains(toString(related_entity_ids),"HOST")

//attempting a lookup but the geoloc_site list of hosts is in contains[dt.entity.host] and it doesn't appear the lookup can compare the hostID as being "in" that array
 | lookup [fetch dt.entity.geoloc_site ],sourceField:dt.entity.host, lookupField:contains[dt.entity.host], fields: {dt.entity.hosts = contains[dt.entity.host]}

Above - I want to filter on the geoloc_site based on where the host ID is within the contains of geoloc_site - the above produces message "Expression not yet implemented (only root-level fields)."

Likewise - if I attempt fieldsadd (to simply add geoloc_site id to the events results, and then later in the query filter on that) - but syntax isn't happy.

//attempting fieldsadd
 | fieldsAdd geoloc_siteID = (fetch dt.entity.geoloc_site
 | fieldsAdd contains[dt.entity.host]
 | filter in(dt.entity.host,`contains[dt.entity.host]`)
 | fields id)

I had another idea to create an array via a variable at the top of the dashboard ... variable named thisDChosts, populated via query - this works, and produces a nice array full of hosts within the DC for the dashboard, but the filter syntax I've not sorted yet.

//query to populate the array in the dashboard 

fetch dt.entity.geoloc_site
| filter id == $geoloc_siteID
| fieldsAdd contains[dt.entity.host]
| fields `contains[dt.entity.host]`, alias: thisDChosts

and - my attempt to filter events on that ...

fetch events
| filter dt.system.bucket == "default_davis_events"
| filter event.kind == "DAVIS_PROBLEM"
| filter contains(toString(affected_entity_ids),"HOST") or contains(toString(related_entity_ids),"HOST")

| filter in(dt.entity.host, Array($thisDChosts))

The above with filtering the array always results in nothing, even when I know there are hosts with problems in that data center which are in the dashboard variable array

[Landho]

OK.  Weird ... .setting the variable on the dashboard to multiselect seems to fix my last attempt there...

[Landho]

I updated the query to generate the array variable of hosts within the dashboard to below as this seems to run quicker (no idea why) than the above query which does fetch dt.entity.geoloc_site with filters , however  -due to very large amount of hosts within data centers, seem to have hit a limit on variables in dashboards - going to hit our DT support queue for that one.

fetch dt.entity.host
| filter matchesValue(belongs_to[dt.entity.geoloc_site], $geoloc_siteID)
| fields id