# Write a lookup query in DQL that uses the entity id to pull the host group

**URL:** <https://community.dynatrace.com/t/write-a-lookup-query-in-dql-that-uses-the-entity-id-to-pull-the-host-group/50240>\
**Category:** DQL\
**Tags:** dql, lookup\
**Created:** [February 12, 2025, 7:29pm UTC](https://community.dynatrace.com/t/write-a-lookup-query-in-dql-that-uses-the-entity-id-to-pull-the-host-group/50240 "2025-02-12T19:29:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![gdalessandro](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/gdalessandro/32/5318_2.png) [@gdalessandro](https://community.dynatrace.com/u/gdalessandro)\
**Post date:** [February 12, 2025, 7:29pm UTC](https://community.dynatrace.com/t/write-a-lookup-query-in-dql-that-uses-the-entity-id-to-pull-the-host-group/50240/1 "2025-02-12T19:29:02Z")

</div>

I am trying to write a DQL query that starts on the dt.davis.problems events and uses the affected\_entity\_id field to lookup the dt.entity.host.group.name field.

---

<div class="post-metadata">

**Author:** ![krzysztof\_hoja](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/krzysztof_hoja/32/34625_2.png) [@krzysztof\_hoja](https://community.dynatrace.com/u/krzysztof_hoja)\
**Post date:** [February 12, 2025, 8:58pm UTC](https://community.dynatrace.com/t/write-a-lookup-query-in-dql-that-uses-the-entity-id-to-pull-the-host-group/50240/2 "2025-02-12T20:58:46Z")

</div>

Here is the example query:

```markup
fetch dt.davis.problems
| filter matchesValue(affected_entity_types,"dt.entity.host")
| expand affected_entity_ids
| join [ fetch dt.entity.host
  | fieldsAdd dt.entity.host_group=instance_of[dt.entity.host_group] 
  ], on:{left[affected_entity_ids]==right[id]}, fields:{dt.entity.host_group} 
| fieldsAdd entityName(dt.entity.host_group)
| summarize { affected_host_group_names = collectDistinct(dt.entity.host_group.name) }, by: { timestamp, display_id, event.description }

```

First of all I would filter only events which affected hosts. Because single problem can affect multiple hosts and they are stored as array (also with other types of entities), we need to use _ **expand** _ before we _ **join** _ with dt.entity.host.&nbsp;  
Using join will automatically eliminate from result set records referencing to non hosts.&nbsp;  
From dt.host.entity we need&nbsp; to retrieve reference to host group which is stored in “instance\_of[dt.entity.host\_group]” relationship. Having it allows us to grab directly host group name using _ **entityName** _ function.  
Single problem could affect hosts from different host groups, so if you want to have single record for each problem, _ **summarize** _&nbsp;is needed to collect array of host group names.

---

<div class="post-metadata">

**Author:** ![zaid-bashir](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/zaid-bashir/32/22651_2.png) [@zaid-bashir](https://community.dynatrace.com/u/zaid-bashir)\
**Post date:** [February 13, 2025, 7:35am UTC](https://community.dynatrace.com/t/write-a-lookup-query-in-dql-that-uses-the-entity-id-to-pull-the-host-group/50240/3 "2025-02-13T07:35:02Z")

</div>

Hi&nbsp;[@gdalessandro](https://community.dynatrace.com/user/74173)&nbsp;  
Below is the query that will solve your probelm

```markup
fetch dt.davis.problems
| filter matchesValue(affected_entity_types,"dt.entity.host")
| expand affected_entity_ids
| fieldsRename id = affected_entity_ids
| join [fetch dt.entity.host], on:id
| fieldsAdd right.hostGroupName

```

Here is the result and the query in Notebook App.

 ![zaidbashir_0-1739431748123.png](https://global.discourse-cdn.com/dynatrace/original/3X/b/e/be5f545ef72da6e7824d167ce90f75b8fea4ddea.png)

Explanation:  
==\>First I query the affected\_entities, affected\_entity\_ids is an array that contains id of affected entities like Services Id, Process Group Id, Host Id. To target only Host Ids I **expanded the&nbsp;affected\_entity\_ids** Arraythat will put each affected id in a separate row and also i changed the name of affected\_entity\_ids to id (there is a reason why)

==\>Then i joined it with dt.entity.host where we will get two things entity.name & id.  
==\> The target for **join** is id. (It matches the id of first query with the id of 2nd query).

==\> Then added the host\_group in the final result.

Thanks, If you feel any issue, reply

---

<div class="post-metadata">

**Author:** ![gdalessandro](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/gdalessandro/32/5318_2.png) [@gdalessandro](https://community.dynatrace.com/u/gdalessandro)\
**Post date:** [February 13, 2025, 1:56pm UTC](https://community.dynatrace.com/t/write-a-lookup-query-in-dql-that-uses-the-entity-id-to-pull-the-host-group/50240/4 "2025-02-13T13:56:16Z")

</div>

Thank you both for the great responses.&nbsp; In my case the entities I am starting with are kubernetes workloads, not hosts.&nbsp; My first issue is joining the workload to a host and then the host to a host group.&nbsp; I need some help with the “RUNS\_ON” portion that takes the workload ID and finds the host its lives on.
