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