# Filter Events or Time-Series by Array Contents Using DQL

**URL:** <https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835>\
**Category:** DQL\
**Tags:** dql\
**Created:** [March 21, 2025, 2:56pm UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835 "2025-03-21T14:56:57Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Landho](https://avatars.discourse-cdn.com/v4/letter/l/3be4f8/32.png) [@Landho](https://community.dynatrace.com/u/Landho)\
**Post date:** [March 21, 2025, 2:56pm UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835/1 "2025-03-21T14:56:57Z")

</div>

Hello - new to DT and just deploying it.&nbsp; 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.&nbsp; &nbsp;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&nbsp; filters - like this events&nbsp; - 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&nbsp;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?

---

<div class="post-metadata">

**Author:** ![Etienne1](https://avatars.discourse-cdn.com/v4/letter/e/278dde/32.png) [@Etienne1](https://community.dynatrace.com/u/Etienne1)\
**Post date:** [March 21, 2025, 3:57pm UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835/2 "2025-03-21T15:57:41Z")

</div>

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.

> **[DQL correlation and join commands](https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/commands/correlation-and-join-commands)**
>
> DQL correlation and join commands

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}

---

<div class="post-metadata">

**Author:** ![Landho](https://avatars.discourse-cdn.com/v4/letter/l/3be4f8/32.png) [@Landho](https://community.dynatrace.com/u/Landho)\
**Post date:** [March 21, 2025, 4:21pm UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835/3 "2025-03-21T16:21:20Z")

</div>

I’ve looked at joins - thinking this would be more efficient than lookups - and attempted them thoroughly.&nbsp; The documentation of them and DQL in general is a bit thin, and Davis Copilot is no help.&nbsp; The examples are not even using real schema examples.&nbsp; I have not stubled across an example or write up on how to&nbsp;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…&nbsp; 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?

---

<div class="post-metadata">

**Author:** ![Landho](https://avatars.discourse-cdn.com/v4/letter/l/3be4f8/32.png) [@Landho](https://community.dynatrace.com/u/Landho)\
**Post date:** [March 26, 2025, 8:42pm UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835/4 "2025-03-26T20:42:17Z")

</div>

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  
&nbsp;| 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  
&nbsp;| fieldsAdd geoloc\_siteID = (fetch dt.entity.geoloc\_site  
&nbsp;| fieldsAdd contains[dt.entity.host]  
&nbsp;| filter in(dt.entity.host,`contains[dt.entity.host]`)  
&nbsp;| 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

---

<div class="post-metadata">

**Author:** ![Landho](https://avatars.discourse-cdn.com/v4/letter/l/3be4f8/32.png) [@Landho](https://community.dynatrace.com/u/Landho)\
**Post date:** [March 26, 2025, 8:50pm UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835/5 "2025-03-26T20:50:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Landho](https://avatars.discourse-cdn.com/v4/letter/l/3be4f8/32.png) [@Landho](https://community.dynatrace.com/u/Landho)\
**Post date:** [March 27, 2025, 4:16am UTC](https://community.dynatrace.com/t/filter-events-or-time-series-by-array-contents-using-dql/50835/6 "2025-03-27T04:16:14Z")

</div>

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&nbsp;fetch dt.entity.geoloc\_site with filters , however&nbsp; -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
