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?