Trying to understand bin_at

[Dante Nahuel Ciai]

Hi all

I know this is a silly question but i'm struggling to understand how and where to use bin and bin_at
I've read the docs https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/bin_at()

but I don't understand the FixedPoint value. What and how should I use it?

Bin is used to round values, correct?

But why and how to use bin_at?

Best regards

[#2]

The fixed point value determines fixed offset from the binning that would occur using the bin() function without the third parameter. So for example, if you run the following query: 

Heartbeat | summarize count() by bin(TimeGenerated, 12h) 

This returns rows with the bin Timestamp and the summarized count. For me this defaults to bins starting at midnight and midday. If instead I wanted to look at bins starting at 5am, I could use the following query: 

Heartbeat | summarize count() by bin_at(TimeGenerated, 12h, datetime("5:00"))

So this would give 12h bins, but ensure that the bins align to 5am and 5pm. Similarly, we could choose a date we know is a Sunday and choose bin size 7d to align to weeks starting on Sunday. 

Hope this helps! 

[#3 · Accepted solution]

Hi Dante,

This is not a silly question at all. @Stanislav Zhelyazkov noted your question and also brought to my attention how confusing the behavior is, so I'd like to explain how it actually works, and will also push to update the documentation of it.

First, I ran this query to get the latest CPU report on a Computer named "ContosoWeb":

Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total" and Computer == "ContosoWeb" 
| summarize arg_max(TimeGenerated, *)

The results showed the latest records is from 15:03:57.

I wanted to calculate the average CPU usage per hour, over the last 6 hours (not shown in this query, selected in the UI), so I used bin:

Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total" and Computer == "ContosoWeb" 
| summarize AVGCPU = avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
| sort by TimeGenerated desc

and got 7 bins of results. Since ran the query around 15:10:00 UTC and considering the 6-hour selected time range, the results I got spread between approximately 09:10:00 and 15:10:00.

Note that:

1. "bin()" creates bins that start at a round hour

2. The time shown in the results is the starting time of each bin, not its end time.

I got these bins:

09:00:00 (which shows average of records timed between 09:00:00 and 09:59:59)

10:00:00 (average of records timed between 10:00:00 and 10:59:59)

and so on:

But I wanted to get bins that don't start at a round hour, but instead align with a fixed point in time. To do that I used "bin_at". The fixed point I chose to use is the time now. This means that since I ran the query at 15:13:40, one of the bins should align (start or end) at exactly that time, and the others should align around it, according to the bin-size I set (in this case 1-hour bins). This is the query syntax:

Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total" and Computer == "ContosoWeb" 
| summarize avg(CounterValue) by Computer, bin_at(TimeGenerated, 1h, now())
| extend time_now = now() 
| sort by TimeGenerated desc

And as you see the bins indeed show start and end at xx:13:40 of each hour, in the 6-hour time range I applied:

Since I don't have any results that are timed past my fixed point - "now()" - I don't have a bin that starts at 15:13:40, yet.

I hope this helps. If there are still doubts, please let me know.

Noa