Host name in services running on host dql

[Pablo2]

I have a dql that relates me the services and the hosts on which it is running, but it only gives me the id of the hosts and I would like to have the name of the host instead of the id.

This is the dql:

fetch dt.entity.service
| expand dt.entity.host=runs_on[dt.entity.host]
| fieldsAdd entityAttr(dt.entity.host, "tags")
| filter in(id, classicEntitySelector(concat("type(service), tag(\"APP:", $APPLICATION, "\")")))
| filter in(id, classicEntitySelector(concat("type(service), tag(\"ENVIRONMENT:", $ENTORNO, "\")")))
| dedup id
| fields entity.name, serviceType, entity.customized_name, id, runs_on[dt.entity.host]

I found this in the documentation but I've tried it and it doesn't work for me.

https://docs.dynatrace.com/docs/discover-dynatrace/platform/grail/querying-monitored-entities 

[erh_inetum · Accepted solution]

Hi Pablo,

Adding this instruction

| lookup sourceField:runs_on[dt.entity.host], lookupField:id, fields:{host.name = entity.name}, [ fetch dt.entity.host ]

you can get the hostname instead of the hostid.

Try to change the DQL adding this line. Somethig like this maybe:

fetch dt.entity.service
| expand dt.entity.host=runs_on[dt.entity.host]

| lookup sourceField:runs_on[dt.entity.host], lookupField:id, fields:{host.name = entity.name}, [ fetch dt.entity.host ]
| fieldsAdd entityAttr(dt.entity.host, "tags")
| filter in(id, classicEntitySelector(concat("type(service), tag(\"APP:", $APPLICATION, "\")")))
| filter in(id, classicEntitySelector(concat("type(service), tag(\"ENVIRONMENT:", $ENTORNO, "\")")))
| dedup id
| fields entity.name, serviceType, entity.customized_name, id, runs_on[dt.entity.host]

and check if you get the desired results.

Hope it helps.

Regards,

Elena.

[Pablo2]

I always get an empty field in host.name results, it doesnt work for me

[marco_irmer]

You can use the entityName() function in place of the lookup function to get the host names. The other thing to account for is that the runs_on[dt.entity.host] field is an Array field. This means we need to expand the array before we use the entityName function. We can then summarize to form an array of host names. The DQL for this looks as follows:

fetch dt.entity.service
| expand dt.entity.host=runs_on[dt.entity.host]
| fieldsAdd host_name = entityName(dt.entity.host) // Add the host name
| summarize host_names = collectArray(host_name), by:{id,entity.name} // summarize to create a host_names array
| fieldsAdd entityAttr(dt.entity.host, "tags")
| filter in(id, classicEntitySelector(concat("type(service), tag(\"APP:", $APPLICATION, "\")")))
| filter in(id, classicEntitySelector(concat("type(service), tag(\"ENVIRONMENT:", $ENTORNO, "\")")))
// removed dedup command, as it is no longer needed after using the summarize command above

This should work, I hope.

[Peter_Youssef]

Hello @marco_irmer 

Thanks for your inputs, I've tried the suggested DQL Query but it didn't work, what might be the missing part.

fetch dt.entity.service
| expand dt.entity.host=runs_on[dt.entity.host]
| fieldsAdd host_name = entityName(dt.entity.host) // Add the host name
| summarize host_name = collectArray(host_name), by:{id,entity.name} // summarize to create a host_names array
| fieldsAdd entityAttr(dt.entity.host, "tags")
| filter in(id, classicEntitySelector(concat("type(service), tag(\"Env:" , abc", \")")))
| filter in(id, classicEntitySelector(concat("type(service), tag(\"Env:" , xyz", \")")))

BR, 

Peter

[CharlesPerez]

Hi @Peter_Youssef,

Try adding this:

| fieldsAdd dt.entity.host.name = entityAttr(dt.entity.host, "entity.name")

Regards,
Charles

[Peter_Youssef]

Thanks @CharlesPerez 
will try it out.

[StrangerThing]

Just change your last line with the fields command to this: 

| fields entity.name, serviceType, entity.customized_name, id, entityName(dt.entity.host)