Forum Discussion

Dante Nahuel Ciai's avatar
Dante Nahuel Ciai
Brass Contributor
Feb 15, 2018

Trying to understand bin_at

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...
  • Noa Kuperberg's avatar
    Feb 20, 2018

    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

     

Resources