Exclude logs between a certain time range

%3CLINGO-SUB%20id%3D%22lingo-sub-466960%22%20slang%3D%22en-US%22%3EExclude%20logs%20between%20a%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-466960%22%20slang%3D%22en-US%22%3E%3CP%3EWe'd%20like%20to%20exclude%20logs%20generated%20between%20certain%20time%20ranges%20from%20our%20alerts%20but%20are%20having%20a%20hard%20time%20figuring%20out%20how%20to%20play%20with%20the%20time%20value%20of%20datetime.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ELets%20say%20I%20have%20%3CSTRONG%3EtimeOfOccurence_t%20%5BUTC%5D%3C%2FSTRONG%3E%20which%20has%20a%20value%20of%26nbsp%3B%3CSTRONG%3E2019-04-17T04%3A40%3A04.203Z%3C%2FSTRONG%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'd%20like%20to%20exclude%20any%20logs%20with%20a%26nbsp%3B%3CSTRONG%3EtimeOfOccurence_t%20%5BUTC%5D%3C%2FSTRONG%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3Ebetween%20the%20hours%20of%201%20AM%20and%206%20AM.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHow%20can%20I%20go%20about%20this%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-466960%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-470120%22%20slang%3D%22en-US%22%3ERe%3A%20Exclude%20logs%20between%20a%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-470120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322793%22%20target%3D%22_blank%22%3E%40nickthompson%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BHere%20are%20two%20examples%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%2F%2F%20go%20back%201hr%0Alet%20startDate%20%20%20%20%20%20%20%3D%20ago(1h)%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20go%20back%20in%20time%20nn%20%0Alet%20endDate%20%20%20%20%20%20%20%20%20%3D%20now()%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20what%20is%20the%20date%20now%0Aunion%20withsource%20%3D%20tt%20*%20%0A%7C%20where%20TimeGenerated%20%20between%20(startDate%20..%20endDate%20)%0A%7C%20where%20_IsBillable%20%3D%3D%20True%0A%7C%20summarize%20by%20tt%2C%20TimeGenerated%3C%2FPRE%3E%0A%3CP%3EI%20probably%20think%20you'll%20need%20a%20modified%20version%20this%20time%20range%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%2F%2F%20Exclude%201am%20to%206am%20%0Aunion%20withsource%20%3D%20tt%20*%20%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1day))%20%20%2F%2F%20start%20from%20midnight%20yesterday%0A%7C%20where%20TimeGenerated%20%20!between%20(datetime('01%3A00%3A00')%20..%20datetime('06%3A00%3A00'))%20%20%2F%2F%20exclude%20times%20today%20%0A%7C%20where%20_IsBillable%20%3D%3D%20True%0A%7C%20where%20tt%20%3D%3D%20%22Event%22%0A%7C%20summarize%20count()%20by%20tt%2C%20TimeGenerated%0A%7C%20render%20barchart%20%20title%20%3D%22Exclude%201am%20thru%206am%22%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20944px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109444i145F6BE555B5BC3D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-04-18%20085455.jpg%22%20title%3D%22Annotation%202019-04-18%20085455.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

We'd like to exclude logs generated between certain time ranges from our alerts but are having a hard time figuring out how to play with the time value of datetime.

 

Lets say I have timeOfOccurence_t [UTC] which has a value of 2019-04-17T04:40:04.203Z.

 

I'd like to exclude any logs with a timeOfOccurence_t [UTC] between the hours of 1 AM and 6 AM.

 

How can I go about this?

1 Reply
Highlighted

@nickthompson   Here are two examples

 

// go back 1hr
let startDate       = ago(1h);              // go back in time nn 
let endDate         = now();                // what is the date now
union withsource = tt * 
| where TimeGenerated  between (startDate .. endDate )
| where _IsBillable == True
| summarize by tt, TimeGenerated

I probably think you'll need a modified version this time range example:

 

// Exclude 1am to 6am 
union withsource = tt * 
| where TimeGenerated > startofday(ago(1day))  // start from midnight yesterday
| where TimeGenerated  !between (datetime('01:00:00') .. datetime('06:00:00'))  // exclude times today 
| where _IsBillable == True
| where tt == "Event"
| summarize count() by tt, TimeGenerated
| render barchart  title ="Exclude 1am thru 6am"

Annotation 2019-04-18 085455.jpg