DQL- count total number of entity host for tags in each month/day

[Jingyi]

Hi,

I would like to use DQL to count total numbers of hosts for tags in each month for a year(Or day for a month).

For example, I have tags "id:1", "id:2" and so on.... for many different entity hosts.

I am able to count total number of hosts according to each tag in a selected time range but I have trouble to show for each month in a year or each day in a month.

My final goal is to present in a dashboard which shows the trends.

fetch dt.entity.host
|fields tags_host=asArray(tags)
|expand tags_host 
|filter contains(tags_host,"id:")
|summarize total=count(),by:{tags_host}

[sinisa_zubic]

Hi @Jingyi 

I don't really see from your query how you do this:
I am able to count total number of hosts according to each tag in a selected time range

In the query from above you are querying numbers of hosts for tags but I don't see any historical dimension. Can you share the query where you count total number of hosts according to each tag in a selected time range

Best,
Sini

[Jingyi]

Hi sinisa_zubic

Thank you for your replying.

Basically, I am using the custom timeframe to set the time range and I think this works same as "from", "to", if I am wrong, please correct. 

[sinisa_zubic · Accepted solution]

for every dt.entity there is a "lifetime" field

the life time start represents the "first time seen" of an entity, and the lifetime end represents the "last time seen". For the time frame selector the lifetime is been taken into account. In case you are shutting down a host for a longer time, you can't take the lifetime for your use case.

A better approach would be to takt the metric dt.host.availability.

timeseries available=avg(dt.host.availability), by: {dt.entity.host}, interval:1d
| lookup [fetch dt.entity.host], sourceField:dt.entity.host, lookupField:id
| fieldsAdd tags = lookup.tags
| expand available
| filter available != ""

but the problem here is that the timestamp information is not (yet) available for every datapoint for timeseries.

Best,
Sini

[Jingyi]

Thank you for the advice. I will try it to see