SOLVED

Ignore bucket based on offset

%3CLINGO-SUB%20id%3D%22lingo-sub-1191571%22%20slang%3D%22en-US%22%3EIgnore%20bucket%20based%20on%20offset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1191571%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20visualize%20the%20number%20of%20Heartbeats%20in%201%20hour%20buckets%20for%20the%20last%2048%20hours.%20However%2C%20no%20matter%20what%20I%20use%20for%20an%20offset%2C%20I%20get%20a%20drop%20in%20amount%20at%20the%20beginning%20and%20end%20of%20the%20dataset%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-02-24%20142821.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173118i63D14A8F2BD382D7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-02-24%20142821.png%22%20alt%3D%22Annotation%202020-02-24%20142821.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHow%20can%20I%20get%20rid%20of%20these%20in%20the%20visual%20(they're%20confusing)%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1191571%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELog%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1192773%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20bucket%20based%20on%20offset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192773%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suspect%20you%20have%20values%20below%20300%2C000%20due%20to%20the%20time%20you%20run%20the%20query%2C%20if%20it%20runs%20in%20the%20morning%20lets%20say%209am%2C%20you%20get%20a%20small%20count%20from%20midnight%20to%208am%20that%20day%20(where%20as%20the%20previous%20whole%20day%20will%20always%20have%20more%20results)-%20hence%20the%20lower%20number.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20TimeGenerated%20between%20(startofday(ago(2d))..startofday(ago(0d)))%0A%7C%20summarize%20Rows%3Dcount()%20by%20bin(TimeGenerated%2C%201h)%0A%7C%20render%20timechart%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20would%20be%20a%20example%20to%20get%20two%20whole%20days%20(midnight%20to%20midnight)%2C%20to%20get%20rid%20of%20those%20two%20varying%20data%20points.%26nbsp%3B%20I'm%20a%20fan%20of%20%22%3CEM%3Estartofday%3C%2FEM%3E%22%20for%20this%20reason.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fstartofdayfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fstartofdayfunction%3C%2FA%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorClive%20Watson_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193048%22%20slang%3D%22en-US%22%3ERe%3A%20Ignore%20bucket%20based%20on%20offset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3Bto%20the%20rescue%20again.%20It%20trims%20my%20first%20bucket%20properly%2C%20but%20my%20last%20bucket%20still%20has%20that%20drop%20off--I%20can%20live%20with%20that.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

I'm trying to visualize the number of Heartbeats in 1 hour buckets for the last 48 hours. However, no matter what I use for an offset, I get a drop in amount at the beginning and end of the dataset:

Annotation 2020-02-24 142821.png

How can I get rid of these in the visual (they're confusing)?

2 Replies
Highlighted
Solution

@Scott Allison

 

I suspect you have values below 300,000 due to the time you run the query, if it runs in the morning lets say 9am, you get a small count from midnight to 8am that day (where as the previous whole day will always have more results)- hence the lower number.

 

Heartbeat
| where TimeGenerated between (startofday(ago(2d))..startofday(ago(0d)))
| summarize Rows=count() by bin(TimeGenerated, 1h)
| render timechart 

This would be a example to get two whole days (midnight to midnight), to get rid of those two varying data points.  I'm a fan of "startofday" for this reason. https://docs.microsoft.com/en-us/azure/kusto/query/startofdayfunction

 

 

 

Highlighted

@Clive Watson to the rescue again. It trims my first bucket properly, but my last bucket still has that drop off--I can live with that. Thanks!