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

Highlighted
Deleted
Not applicable
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: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 StartTime
I expect this command to return pairs of timestamps where all StartTimes are unique and the EndTimes are the latest found EndTimes for each given StartTime.
 
The code, however produces the following result:
2018-08-07_22h00_39.png
 
 
 
 
 
The StartTime column was rounded to the nearest hour instead of using exact values!
 
The automatic hourly bins for datetime columns is no longer supported. Use explicit binning instead – for example summarize by bin(timestamp, 1h).
 
So shouldn't this feature already have been removed?? I find this bug/feature rather unexpected. Instead of 4 output rows I've got only 2.
 
Here is a workaround I found:
| summarize max(EndTime) by tostring(StartTime)
This way the groups are based on exact StartTimes, but I lose the possibility to apply datetime operations like between(..) in the following code :(
 
 
Additionally, I'd like to ask if someone has an idea how to reduce this dataset example to contain the longest durations only.
Input:
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") // is alone
The first five rows share some partial coverage among each other, thus the longest combined duration (strictly without gaps) is 08:09 until 15:49. The sixth row does not overlap with any other row, thus it is left unchanged.
Wanted output:
datetime("2018-05-17T08:09:00.00"), datetime("2018-05-17T15:49:00.000")
datetime("2018-05-17T17:34:00.00"), datetime("2018-05-18T00:00:00.000")
It's simple to do this manually on paper. I tried various configurations of joins but I couldn't come up with an algorithm in Log Analytics Query Language.
 

Thank you very very much in advance!

Best regards,

David

3 Replies
Highlighted

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

Highlighted

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)
Highlighted

Hi Noa,

Greetings!

 

Noa this script is only for the timing of VM's or anything else.??