'DnsEvents | summarize by ClientIP, TimeGenerated' doesn't return expected result

[Brady Evans]

When I execute the following query on the demo portal:

DnsEvents 

| summarize by ClientIP, TimeGenerated

It doesn't return what I expect.  It seems the TimeGenerated is rounded to the nearest hour and all sub-hour records are filtered..  It's as if there was a hypothetical startofhour function applied to TimeGenerated.  Is this expected?

[#2]

Hi,

Can you let us what exactly you are trying to achieve as the query you are executing does not make much sense?

[#3 · Accepted solution]

Hi,

This is expected. It is a failsafe functionality in the system to protect it from returning huge amounts of records which will be the situation if we would have return every TimeGenerated in accuracy of a millisecond. It automatically use 1 hour binning.

We are evaluating this failsafe mechanism and consider if it worth keeping it. 

If you want control over the binning period, you can use the bin function. This query does the same but use a 1 minute binning instead of the 1 hour binning:

DnsEvents | summarize count() by ClientIP, bin(TimeGenerated,1m)

Thanks,

Meir 😆

[#4]

Thanks for the response. That makes sense. I'm just trying to understand Log Analytics and was reading through the docs on Materialize. There is a query that has a let assignment: let totalPagesPerDay = PageViews | summarize by Page, Day = startofday(Timestamp) | summarize count() by Day; This basically bins the PageViews by day (?) and was wondering if I could bin them by hour. Playing with DnsEvents I stumbled across this behavior and was thinking if I can't understand a fundamental query like this I must be missing something. Sorry if the question was off base.

[#5]

Hi,

Here is a more compact way to write the first query:
PageViews | summarize count() by bin(Timestamp,1d)

You can also do 1 hour binning using bin(Timestamp,1h).
You can see all details on the bin functions here: https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/bin()
There are additional options for more advanced scenarios. For example, see this: https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/bin_at()

Your feedback on the confusion is good. This is why we think to eliminate the auto-binning functionality.

[#6]

Ah, Thanks explicitly calling bin makes more sense.