Question 266041 in Azure Question posted in
The official documentation to get you started can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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"]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search