Forum Discussion

Anonymous's avatar
Anonymous
Aug 07, 2018

Summarize operator applies an unexpected bin(DateTime, 1h) on DateTime values

Dear community!   Given a table of (StartTime,EndTime) rows I want to combine all overlapping timespans to the longest common timespans.  Input example: datatable(StartTime:datetime,EndTime:datet...
azure monitor
Query Language
Noa Kuperberg's avatar
Noa Kuperberg
Icon for Microsoft rankMicrosoft
Nov 18, 2018

Hi,

Indeed the first issue you raise seems strange, I'm trying to find more about why it behaves that way, will update when I get answers.

The second issue - identifying the longest common time range - is more difficult, I don't know of a simple way to do exactly that, but a somewhat similar issue is solved in this example.

 

Noa

Noa Kuperberg's avatar
Noa Kuperberg
Icon for Microsoft rankMicrosoft
Nov 25, 2018

Hi,

So about the first issue (unexpected binning) - the documented change hasn't been updated in Log Analytics yet... that's a long time coming, I hope it'd be applied soon.

Meantime, another workaround would be to bin to "1tick" which is the exact value you want:

datatable(StartTime:datetime,EndTime:datetime)[
datetime("2018-05-17T08:11:00.00"), datetime("2018-05-17T15:33:00.000"),
datetime("2018-05-17T08:11:00.00"), datetime("2018-05-17T15:45:00.000"),
datetime("2018-05-17T08:13:00.00"), datetime("2018-05-17T15:47:00.000"),
datetime("2018-05-17T08:13:00.00"), datetime("2018-05-17T15:49:00.000"),
datetime("2018-05-17T08:09:00.00"), datetime("2018-05-17T15:43:00.000"),
datetime("2018-05-17T17:34:00.00"), datetime("2018-05-18T00:00:00.000")]
| summarize max(EndTime) by bin(StartTime, 1tick)

Resources