Transposing the service ID with its name using DQL

[walkadda]

I have the following DQL query in my dashboard:

fetch dt.entity.host
| fields entity.name, runs
| fieldsFlatten runs
| filter in(runs.dt.entity.service, $services)
| fields entity.name, runs.dt.entity.service

The $services variable returns a list of service IDs, i.e "SERVICE-C1917273162D91".

I would like the query to return a table of hosts and their corresponding services, using hostnames and service names. Here is an example of what I'm trying to achieve:

hostname	service
server-01	myservice, anotherservice, yetanotherservice
server-02	myservice
server-03	anotherservice, yetanotherotherservice

But the actual result I get is:

entity.name	runs.dt.entity.service
server-01	SERVICE-C912138912D19, SERVICE-C1298314717J89, SERVICE-G821398124921B90
server-02	SERVICE-C1298314717J89
server-03	SERVICE-C1298314717J89, SERVICE-G821398124921B90

I have tried using the "lookup" function, but did not have any luck.

fetch dt.entity.service
| filter in(id, runs.dt.entity.host)
], lookupField:id, sourceField:runs.dt.entity.host

How can I display the name of the service in this table instead, whilst keeping the relationship between the host and its services?

Thanks in advance

[Akhil-Jayendran · Accepted solution]

Hi @walkadda ,

you can try this probably this will work ?

fetch dt.entity.host
| fields entity.name, runs
| fieldsFlatten runs
| filter in(runs.dt.entity.service, $SERVICE)
| expand runs.dt.entity.service
| fields entity.name, runs.dt.entity.service
|fieldsAdd service_name = lookup([fetch dt.entity.service],
sourceField:runs.dt.entity.service, lookupField:id)[entity.name]

Br

[TracingMySanity]

If you're filtering by service to look for the hosts it runs on, similar to @Akhil-Jayendran's solution, you can query the `runs_on` relationship on the service entity.

fetch dt.entity.service
| filter id == "SERVICE-FFB92DD67873FAC7"
| fieldsAdd runsOn = runs_on[dt.entity.host]
| filter isNotNull(runsOn)
| expand runsOn
| fields serviceName = entity.name, hostName = entityName(runsOn, type:"dt.entity.host")