Find all host_groups with hosts that have a specific tag

[sinisa_zubic]

Hi there,

How would one find all host_groups with hosts that have a specific tag? Something like:

fetch dt.entity.host_group
| filter in(id,[fetch dt.entity.host |filter matchesValue(tags,"MyTag")|fields id])

I still struggle a bit with DQL syntax…   Do I need a FilterIn, a Lookup, etc?

Best,
Sini

[stefan_eggersto · Accepted solution]

You need to join the 2 tables using lookup  and then do a filter. Please try following DQL:

fetch dt.entity.host_group
| lookup [fetch dt.entity.host
| filter matchesPhrase(tags,"MyTag")
| fieldsAdd hostGroupReference = instance_of[dt.entity.host_group]], sourceField:id, lookupField:hostGroupReference
| filter isNotNull(lookup.id)
| fieldsRemove "lookup*"

Some explanation

• first fetch all host groups
• then do a lookup for hosts filtered by the tag. 
• hosts are linked to the host groups by the field instance_of[dt.entity.host_group] 
• then filter on hosts groups which have the field lookup.id . Basically filter only those host groups which have match from the lookup