topic Re: Transposing the service ID with its name using DQL in DQL

Transposing the service ID with its name using DQL

walkadda — Tue, 24 Jun 2025 09:00:34 GMT

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

Re: Transposing the service ID with its name using DQL

Akhil-Jayendran — Tue, 24 Jun 2025 16:45:24 GMT

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]

Re: Transposing the service ID with its name using DQL

TracingMySanity — Tue, 24 Jun 2025 17:22:31 GMT

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.