Extract TAG values from hosts tags in DQL

[DavidePiras]

I am querying the list of hosts in our environment, i would need to also filter by presence of a tag and to see the tag name and value in a column of the resulting table,

I am using this query to start with:

fetch dt.entity.host
| fieldsAdd entity.type, tags
| filter matchesPhrase(tags,"Business Segment")

the fact is that Business Segment tag can have multiple values, like Sales, IT, Finance, and each hosts has may tags and what i need is to extract the value of the Business Segment tag into a separated column, to see a grid showing only IT, Finance or Sales in a column, after the host name and the host id,

how can I do this?

Thanks,

Davide

[sinisa_zubic · Accepted solution]

Hi @DavidePiras 

the field tags is an array of multiple tags. You need to use the expand command to expand it and then parse the key value information from the tag itself.

fetch dt.entity.host
| fieldsAdd entity.type,tags
| expand tags
| parse tags, """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)"""
| fieldsAdd tagvalue = if(isNotNull(value), value)
| fieldsRemove value
| fields id, entity.type,tag, tagvalue

 The parsing looks a bit complex, but it is already optimized for some edge cases I have observed so far.

Best,
Sini

[DavidePiras]

Amazing, thank you so much for the hint, this is my adapted final query and works quite well, appreciated your prompt answer Sinisa!

fetch dt.entity.host
| fieldsAdd grp=instance_of[dt.entity.host_group]
| fieldsAdd tags
| fieldsAdd monitoringMode
| expand tags
| parse tags, """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)"""
| fieldsAdd tagvalue = if(isNotNull(value), value)
| fieldsRemove value
| lookup [fetch dt.entity.host_group],sourceField:grp,lookupField:id
| fields HostGroup=lookup.entity.name, HostName=entity.name, tags, monitoringMode, tag, BusinessSegment=tagvalue
| filter isNotNull(HostGroup)
| filter tag == "Business Segment"
| sort HostGroup ASC, HostName ASC