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

[SGOconnell1]

How would one go about this using timeseries data? Same thing?

i.e. I have a query for disk space by a single host filter that works, but i want to leverage Host Group rather than dt.entity.host for my filter value.

[krzysztof_hoja · Accepted solution]

Even simpler then it was possible 1 year ago. E.g. to get disk usage for all disks of hosts in specific host group:

timeseries disk_used=max(dt.host.disk.used), 
  by:{dt.entity.host, dt.entity.disk}, 
  filter: {entityName(dt.entity.host_group)=="vmware"}