Sum Entities by Specific Tag Using DQL for Aggregated Insights

[TylerJVitale]

I am trying to create a report with a count of all the entities that have a specific tag value, and then *of those*, another specific value.  My current DQL looks like this:

fetch dt.entity.host
| filter tags ~ "specific value 1"
| expand tags
| parse tags, """LD:Key ':' LD:Value"""
| summarize displayName = takeAny(entity.name),has_keyfield1=countif(Key=="keyfield1"), has_keyfield2=countif(Key=="keyfield2"), by : id
| filter has_keyfield1==1
| summarize sum(has_keyfield1), sum(has_keyfield2)

This gets me the results, but it's quite tedious having to replace "dt.entity.host" with every other entity type. Is there a better way to search across every entity so that I can get all the counts I'm looking for, separated by entity type, in a single search?

[deni · Accepted solution]

Hi @TylerJVitale ,

I spent some time searching, but I don’t think there’s such an option in DQL. Maybe you can explain your case further as an idea for something like fetch entities.

What’s your exact use case? Perhaps you could use a workflow — get the list of all entities from the API, and then pass them into the DQL you already have?

Regards,
Deni

[cusack_christop]

Ive tried this but its non-performative.  exceeds the max 2 minute step timeout in the workflow, and i was only searching a subset of entities about 70 or 80 in total. 

A use case for fetch entities is, we have an entity name and want to create maintenance windows for it, but need to see all the objects DT has for it. For example, host name could appear in dt.entity.hosts and dt.entity.network:device or dt.entity.aws_ec2_instance (making these up but hopefully you get the idea here.)