DQL - show detected services number trend

[paolo_fumanelli]

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

[krzysztof_hoja · Accepted solution]

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.

[paolo_fumanelli]

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

[krzysztof_hoja]

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..

[paolo_fumanelli]

Clear and very useful, thanks again! Paolo