Give a service, find if calling service having problem

[PVThach]

I’m looking for guidance on creating a DQL query for as dashboard that can:

1. Take a given list of services.
2. Identify their associated upstream and downstream dependencies.
3. Display any alerts (active, or closed) that are impacting or affecting these services.

The goal is to have a clear view of service relationships and understand how alerts propagate across dependencies. If anyone has an example query or best practices for achieving this in Dynatrace, I’d greatly appreciate your input.
Here my attempt look for at problem in upstream services.

fetch dt.entity.service 
//|filter in(id, "SERVICE-24844D84E75DDE51", "SERVICE-96A302DC14A0CCE9") // dashboard varaible
|fieldsAdd upstream_ids = called_by[dt.entity.service]
|expand upstream_id = upstream_ids // upstream_id = Caller_id
// Get associated upstream service
| lookup [fetch dt.entity.service | fieldsAdd managementZones ], sourceField:upstream_id, lookupField:id, executionOrder:leftFirst, prefix: "Caller_"

// Get problem assoicated(impacting or affecting) these upstream services
// this is where I need help, I'm getting null 
| lookup [fetch dt.davis.problems ], //| filter affected_entity_types == array(called_by[dt.entity.service]) | expand affected_entity_ids ]
         sourceField:Caller_id, lookupField:id, executionOrder:leftFirst, prefix: "Alert_"
          
| fields managementZones, entity.name, id, Caller_id,
Caller_entity.name,
Caller_managementZones, Alert_name, Alert_display_id

|sort entity.name

[raksh_hitha]

Hi @PVThach ,

fetch dt.entity.service
| expand downstream_service = calls[dt.entity.service]
| fieldsAdd downstream_service_name = entityName(downstream_service, type:"dt.entity.service")
| expand upstream_service = called_by[dt.entity.service]
| fieldsAdd upstream_service_name = entityName(upstream_service, type:"dt.entity.service")
| lookup 
[
fetch dt.davis.problems
| expand affected_entity_ids
], sourceField:id, lookupField:affected_entity_ids, prefix:"problem."
| fieldsAdd problem_id = problem.display_id
| fields entity.name, upstream_service_name, downstream_service_name, problem_id, managementZones

Feel free to modify it further based on your requirements.