Adding a field with a relationship to another field in my query

[Christian_]

I am trying to get a list of all the services affected by Davis problems and then add a field containing the host for each service. This is how I attempted to do it, but the fields that should contain host information are empty. 

fetch events, from:now() - 4h
| filter event.kind == "DAVIS_PROBLEM"
| expand affected_entity_ids
| fields affected_entity_ids
| lookup [fetch dt.entity.host], sourceField:affected_entity_ids, lookupField:runs[dt.entity.service]

If anyone can offer a solution, I would be very grateful

[sinisa_zubic · Accepted solution]

Hi @Christian_ 

Following query should work for you

fetch events, from:now()-7d
| filter event.kind == "DAVIS_PROBLEM"
| summarize {problem=takeMax(record(timestamp,affected_entity_ids) )}, by:{display_id}
| expand affected_entity_id=problem[affected_entity_ids]
| fields display_id, affected_entity_id
| filter startsWith(affected_entity_id, "SERVICE-")
| lookup
  [
    fetch dt.entity.service
    | fieldsAdd hosts = runs_on[dt.entity.host]
  ], sourceField:affected_entity_id, lookupField:id, fields:{hosts, entity.name}
| expand dt.entity.host = hosts
| filter isNotNull(dt.entity.host)
| fields display_id, affected_service = affected_entity_id,service_name=entity.name, affected_host =dt.entity.host

 You can also try it out in Discover Dynatrace

Best,
Sini

[Christian_]

Yes, that worked perfectly, thank you!