topic Re: DQL - show detected services number trend in DQL

DQL - show detected services number trend

paolo_fumanelli — Tue, 03 Dec 2024 16:18:53 GMT

Hi, I'm trying to setup a dashboard where I want to show some trends over the time.

To make the simplest possible example, let's assume that I'd like to chart the total number of services detected in my tenant over the time, resulting in one data point for each day.

DQL query is easy:

fetch dt.entity.service

Is there a way to tranform it to a timeseries?
I tried with | makeTimeseries but of course it needs some kinf of Timestamp dimension to be added, and I couldn't find a way.

A second attempt to achieve this result was done by reading from a timeseries that involves service, like

timeseries sum(dt.service.request.count), by:{dt.entity.service}, from:now()-10d, to:now(), interval:24h

This is producing as output an array of values.

This could be used if somehow I could expand sum(dt.service.request.count) and adding its related timestamp, but actually I don't know how to do it.
I just have the timeframe (-10d to now). I can't expand this to multiple rows, where each one represents one datapoint with a value. If I'd be able to do that, then I could count the number of entries by timestamp to get how many services I had in that timestamp.

Do you have any clue on how I can get this result?

Thanks
Paolo

Re: DQL - show detected services number trend

krzysztof_hoja — Fri, 06 Dec 2024 11:14:22 GMT

Yes, second approach is as good start. Here is my query:

timeseries {req=sum(dt.service.request.count), timestamp=start()}, by:{dt.entity.service}, from:now()-10d, to:now(), interval:24h | fieldsAdd d=record(req=req[], timestamp=timestamp[]) | expand d | filterOut isNull(d[req]) | makeTimeseries service_count=countDistinct(dt.entity.service), interval: 24h, time: d[timestamp]

We need to unwrap request count timeseries and in order no too lose information to which information each datapoint belongs we need to user special function start() and later combine it with each datapoint in a record.
After expand we need to get read rows where number of request was null - these are days when "service did not exists". Now we can build new timeseries with makeTimeseries when each datapoint will be count of unique service entity ids.

Re: DQL - show detected services number trend

paolo_fumanelli — Fri, 06 Dec 2024 11:50:51 GMT

Brilliant! Thanks a lot, was not aware about start() function.

I adapted it this way:

timeseries {req=sum(dt.service.request.count), timestamp=start()}, by:{dt.entity.service}, from:now()-10d, to:now(), interval:1d
| fieldsAdd d=record(req=req[], timestamp=timestamp[]) | expand d
| fieldsAdd value = d[req], ts = d[timestamp]
| filterOut isNull(value)
| fields ts, dt.entity.service, value
| summarize countDistinct(dt.entity.service), by:{ts}

I just a summarize by timestamp instead of making a timeseries. I take advantage of this inquiry to ask when it is more convenient makeTimeseries solution or a summarize?

Thanks!
Paolo

Re: DQL - show detected services number trend

krzysztof_hoja — Fri, 06 Dec 2024 13:30:00 GMT

makeTimeseries is a special form of summarize:

aggregation by time is obligatory: you just specify interval, no need to use of bin() function
result is presented in a form of array where index is proportional to subsequent time bins accompanied by timeframe this array covers and interval single bin represents. In an array it is also easy to spot intervals when nothing happened. Also result set is more condensed this way
set of allowed aggregation functions is limited: as intended result is timeseries (metric value over time), it make no point to use aggregation functions returning e.g. arrays (like collect...)

but general idea of operation is pretty much the same..

Re: DQL - show detected services number trend

paolo_fumanelli — Fri, 06 Dec 2024 13:58:52 GMT

Clear and very useful, thanks again! Paolo