Changing affected_entity_ids for entity name

[EnnioCamacho]

Hello!

Searching for help with the following, i'm relatively new using DQL. I have the following code, what i want is to display the entity names on the affected_entity_ids row, not the id, so far i have tried to change the affected_entity for dt.entity values but no luck so far, any ideas how i could change the values on that field?

Code:

fetch events, from: timestamp(year:2023,month:10,day:16,hour:0,minute:0,second:0), to: timestamp(year:2023,month:10,day:16,hour:23,minute:59,second:0)
| filter event.kind == "DAVIS_PROBLEM"
|expand event.name
|summarize by:{event.name,affected_entity_ids}, count = countDistinct(display_id)
|sort count, direction:"descending"

Result:

[natanael_mendes]

You need to reference the type of entity like this example below

fetch dt.entity.host | fieldsAdd runs[dt.entity.process_group]

[EnnioCamacho]

Hello,

I've been trying to add the host entity with a lookup function since the first fetch i'm using it for the davis problem events, so far no luck, I've been trying to include the entity with the fieldsadd and the fieldrename but no luck, it does not write any records, could it be that the affected entity id does not match with other entities field?

[EnnioCamacho · Accepted solution]

I have something figured out for now, it might not be optimal but for now it's working, my only issue now is that the results are not grouping by event name as it used to, but for now it works for the purpose i intend to use it.

fetch events
| filter event.kind == "DAVIS_PROBLEM"
|expand affected_entity_ids
|lookup sourceField:affected_entity_ids, lookupField:id, prefix:"service.", [
fetch dt.entity.service | fields id, name = entity.name]
|lookup sourceField:affected_entity_ids, lookupField:id, prefix:"http.", [
fetch dt.entity.http_check | fields id, name = entity.name]
|lookup sourceField:affected_entity_ids, lookupField:id, prefix:"process.", [
fetch dt.entity.process_group_instance | fields id, name = entity.name]
|lookup sourceField:affected_entity_ids, lookupField:id, prefix:"host.", [
fetch dt.entity.host| fields id, name = entity.name]
|lookup sourceField:affected_entity_ids, lookupField:id, prefix:"mobile.", [
fetch dt.entity.mobile_application | fields id, name = entity.name]
|fieldsAdd componente = array(host.name, http.name, mobile.name, process.name, service.name)
|summarize by:{`Problema` = event.name, `Componente`= componente }, count = countDistinct(display_id)
|sort count, direction:"descending"