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

[gdalessandro]

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. 

[krzysztof_hoja]

Here is the example query:

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. 
Using join will automatically eliminate from result set records referencing to non hosts. 
From dt.host.entity we need  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 is needed to collect array of host group names.

[zaid-bashir · Accepted solution]

Hi @gdalessandro 
Below is the query that will solve your probelm

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.

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 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

[gdalessandro]

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