KQL Set make-series to startofday from first step onwards from a non-startofday starting point

Question

Perhaps not the best question title but I believe my issue is simple enough. I'm new to KQL and what I'm trying to achieve is to generate a time series with some aggregation of a value per day. It works fine when the starting point is at startofday():

MyTable
| where id == 11111
| make-series avg(myValue) on timestamp from startofday(ago(7d)) to startofday(now()) step 1d

My issue arises when trying to use a starting point that is not startofday. For example:

MyTable
| where id == 11111
| make-series avg(myValue) on timestamp from datetime("2023-09-28T13:44:15.0000") to startofday(now()) step 1d

This yields:

"timestamp": [
    "2023-09-28T13:44:15.0000000Z",
    "2023-09-29T13:44:15.0000000Z",
    "2023-09-30T13:44:15.0000000Z",
    "2023-10-01T13:44:15.0000000Z",
    "2023-10-02T13:44:15.0000000Z",
    "2023-10-03T13:44:15.0000000Z",
    "2023-10-04T13:44:15.0000000Z"
]

Is there some way to have the first step only look at 2023-09-28T13:44:15.0000000Z - 2023-09-29T00:00:00.0000000Z and then rest of the steps go from full startofday to full startofday?

Answer 1

If I understand your question correctly you can probably get what you want by using bin(/floor) as Aswin identifies above as well as getting KQL to start from a known point and then fill in the gaps from there.

You can take an arbitrary time window like this:

let MyTable = datatable(id:int, timestamp:datetime, myValue:int) [
1111, datetime('2023-10-07 13:14:20'), 7,
1111, datetime('2023-10-07 19:22:00'), 2,
1111, datetime('2023-10-09 09:13:30'), 15,
1111, datetime('2023-10-09 23:43:40'), 1,
1111, datetime('2023-10-09 23:55:20'), 17,
1111, datetime('2023-10-15 01:34:50'), 4
];
MyTable
| where id == 1111
| make-series avg(myValue) on timestamp from floor(ago(28d), 1d) to floor(now(+1d), 1d) step 1d

avg_myValue

timestamp

[0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,4.5,0.0,11.0]

["2023-09-11T00:00:00.0000000Z","2023-09-12T00:00:00.0000000Z","2023-09-13T00:00:00.0000000Z","2023-09-14T00:00:00.0000000Z","2023-09-15T00:00:00.0000000Z","2023-09-16T00:00:00.0000000Z","2023-09-17T00:00:00.0000000Z","2023-09-18T00:00:00.0000000Z","2023-09-19T00:00:00.0000000Z","2023-09-20T00:00:00.0000000Z","2023-09-21T00:00:00.0000000Z","2023-09-22T00:00:00.0000000Z","2023-09-23T00:00:00.0000000Z","2023-09-24T00:00:00.0000000Z","2023-09-25T00:00:00.0000000Z","2023-09-26T00:00:00.0000000Z","2023-09-27T00:00:00.0000000Z","2023-09-28T00:00:00.0000000Z","2023-09-29T00:00:00.0000000Z","2023-09-30T00:00:00.0000000Z","2023-10-01T00:00:00.0000000Z","2023-10-02T00:00:00.0000000Z","2023-10-03T00:00:00.0000000Z","2023-10-04T00:00:00.0000000Z","2023-10-05T00:00:00.0000000Z","2023-10-06T00:00:00.0000000Z","2023-10-07T00:00:00.0000000Z","2023-10-08T00:00:00.0000000Z","2023-10-09T00:00:00.0000000Z"]

Or if you wanted to use the data itself for the range you could do something like this.

let MyTable = datatable(id:int, timestamp:datetime, myValue:int) [
1111, datetime('2023-10-07 13:14:20'), 7,
1111, datetime('2023-10-07 19:22:00'), 2,
1111, datetime('2023-10-09 09:13:30'), 15,
1111, datetime('2023-10-09 23:43:40'), 1,
1111, datetime('2023-10-09 23:55:20'), 17,
1111, datetime('2023-10-15 01:34:50'), 4
];
let MinTime = toscalar(MyTable //Find the earliest time...
| summarize arg_min(timestamp, *)
| summarize by floor(timestamp, 1d));
let MaxTime = toscalar(MyTable //... and the latest time
| summarize arg_max(timestamp, *)
| summarize by floor(timestamp, 1d));
MyTable
| where id == 1111
| make-series avg(myValue) on timestamp from MinTime to datetime_add('day', 1, MaxTime) step 1d

avg_myValue	timestamp
[4.5,0.0,11.0,0.0,0.0,0.0,0.0,0.0,4.0]	["2023-10-07T00:00:00.0000000Z","2023-10-08T00:00:00.0000000Z","2023-10-09T00:00:00.0000000Z","2023-10-10T00:00:00.0000000Z","2023-10-11T00:00:00.0000000Z","2023-10-12T00:00:00.0000000Z","2023-10-13T00:00:00.0000000Z","2023-10-14T00:00:00.0000000Z","2023-10-15T00:00:00.0000000Z"]

Answer 2

Code:

let starttime = datetime("2023-09-28T13:44:15.0000");
let endtime = startofday(now());
MyTable
|where timestamp>starttime and timestamp<endtime
|summarize avg(myValue),min_timestamp= min(timestamp) by bin(timestamp,1d)
|summarize avg_myValue=make_list(avg_myValue),timestamp=make_list(min_timestamp)

In this query, MyTable is filtered with the data between startdate and enddate. Then the average value of myValue for each day in the time range specified by starttime and endtime is calculated the results by day. The summarize operator is used twice to first calculate the average value of myValue and the minimum timestamp for each day, and then to aggregate the results into two lists: one for the average value of myValue and one for the minimum timestamp.

demo