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"]