Feb 15 2018 07:23 AM
Feb 15 2018 07:23 AM
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?
Feb 17 2018 01:46 AM - edited Feb 17 2018 01:46 AM
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:
Feb 20 2018 07:40 AM - edited Feb 20 2018 07:43 AMSolution
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.
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.