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?
2
Answers
Code:
In this query,
MyTable
is filtered with the data betweenstartdate
andenddate
. Then the average value ofmyValue
for each day in the time range specified bystarttime
andendtime
is calculated the results by day. Thesummarize
operator is used twice to first calculate the average value ofmyValue
and the minimum timestamp for each day, and then to aggregate the results into two lists: one for the average value ofmyValue
and one for the minimum timestamp.demo
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:
Or if you wanted to use the data itself for the range you could do something like this.