Joining two tables

samgar-kali — Mon, 25 Sep 2023 19:36:11 GMT

I am trying to join two tables. dt.entity.service and "Davis problem events". I believe the syntax is correct, and I am not seeing any errors. However, the following DQL shows that there are no matching services. Which sounds incorrect. Am I missing something?

fetch events, from:now()-24h, to:now()
| filter event.kind == "DAVIS_PROBLEM"
| fields event.start, event.end, display_id, event.category, event.name, affected_entity_ids, root_cause_entity_id, event.status, resolved_problem_duration
| sort event.start desc
| lookup [fetch dt.entity.service],sourceField:affected_entity_ids,lookupField:id
| summarize count=count(), by:{lookup.entity.name}

Re: Joining two tables

Tiit_Hallas — Mon, 25 Sep 2023 20:01:30 GMT

The issue is coming from different data types: the source field "affected_entity_ids" is an array, while the lookup field "id" is a string. The quickest fix here would be to use "expand" to break down the array, e.g:

fetch events, from:now()-24h, to:now() | filter event.kind == "DAVIS_PROBLEM" | fields event.start, event.end, display_id, event.category, event.name, affected_entity_ids, root_cause_entity_id, event.status, resolved_problem_duration | expand affected_entity_ids | lookup [fetch dt.entity.service], sourceField:affected_entity_ids, lookupField: id

topic Re: Joining two tables in DQL

Joining two tables

Re: Joining two tables