%3CLINGO-SUB%20id%3D%22lingo-sub-1667574%22%20slang%3D%22en-US%22%3EHow%20to%20align%20your%20Analytics%20with%20time%20windows%20in%20Azure%20Sentinel%20using%20KQL%20(Kusto%20Query%20Language)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1667574%22%20slang%3D%22en-US%22%3E%3CH3%20id%3D%22toc-hId-1230449997%22%20id%3D%22toc-hId-1233015925%22%20id%3D%22toc-hId-1233015925%22%20id%3D%22toc-hId-1233015925%22%20id%3D%22toc-hId-1233015925%22%20id%3D%22toc-hId-1233015925%22%3EOverview%3C%2FH3%3E%0A%3CP%3EThanks%20to%20Ofer%20Shezaf%2C%20Kieran%20Bhardwaj%20and%20Younes%20Khaldi%20for%20the%20ideas%20and%20proof%20reading!%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMany%20of%20the%20query%20examples%20you%20see%20in%20KQL%20(Kusto%20Query%20Language)%20Detections%2C%20Rules%2C%20Hunting%20and%20Workbooks%20use%20a%20time%20filter.%26nbsp%3B%20In%20fact%2C%20the%20first%20recommendation%20in%20the%20best%20practices%20section%20is%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-09-15%20091522.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218320i0380EA9DAC5BE967%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-09-15%20091522.jpg%22%20alt%3D%22Annotation%202020-09-15%20091522.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%222%22%3ESource%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbest-practices%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbest-practices%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%26nbsp%3BI%20will%20show%20you%20some%20hopefully%20simple%20but%20useful%20query%20examples%20(and%20I%E2%80%99ll%20use%20the%20demo%20data%20we%20provide%20for%20free%20so%20you%20can%20try%20these).%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20click%20the%20links%20provided%2C%20all%20you%20need%20is%20a%20Microsoft%20account%20to%20grant%20you%20access%20to%20our%20extensive%20demo%20data.%3C%2FP%3E%0A%3CP%3EYou%20should%20also%20get%20familiar%20with%20the%20timespan%20literals%3A%26nbsp%3B%3CFONT%20size%3D%222%22%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fscalar-data-types%2Ftimespan%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fscalar-data-types%2Ftimespan%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--577004466%22%20id%3D%22toc-hId--574438538%22%20id%3D%22toc-hId--574438538%22%20id%3D%22toc-hId--574438538%22%20id%3D%22toc-hId--574438538%22%20id%3D%22toc-hId--574438538%22%3EQuery%20Examples%26nbsp%3B%3C%2FH3%3E%0A%3CP%3E1.%26nbsp%3BThis%20first%20example%20looks%20back%20one%20day%20in%20time%20(looking%20back%20over%20the%20last%2024hrs%2C%20from%20the%20moment%20you%20run%20the%20query)%3B%20%3CSTRONG%3Eyou%20can%20use%2024h%20instead%20of%201d%20if%20you%20prefer.%3C%2FSTRONG%3E%26nbsp%3B%20%3CBR%20%2F%3EI%20prefer%20using%201d%20rather%20than%2024hrs%2C%20typically%20I%20only%20use%20hours%20when%20I%20need%20a%20partial%20day%20i.e.%20%3CSTRONG%3E6h%3C%2FSTRONG%3E%3CBR%20%2F%3EThis%20form%20of%20time%20span%20filter%20is%20one%20of%20the%20most%20common%20lines%20people%20add%20to%20a%20query.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20read%20on%20for%20an%20explanation%20on%20why%20this%20may%20not%20be%20what%20you%20intended%2C%20and%20ways%20to%20improve.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAAwtOTS4tyiypdC1LzSvh5apRKM9ILUpVCMnMTXVPzUstSixJTVGwU0hMz9cwTNHkAgAFDBJ6LwAAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%26nbsp%3BYou%20can%20also%20look%20between%20a%20range.%26nbsp%3B%20Here%20I%20look%20back%20%3CSTRONG%3E7d%3C%2FSTRONG%3Eays%20from%20now%20(today%20at%20this%20current%20time).%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3ENotice%20in%20the%20last%20line%2C%20I%20show%20the%20oldest%20record%20and%20the%20latest%20that%20are%20returned%20%E2%80%93%20I%E2%80%99ll%20do%20this%20for%20many%20of%20the%20examples%20just%20so%20you%20can%20see%20how%26nbsp%3Bthe%20query%20is%20working%2C%26nbsp%3B%20I%20wouldn%E2%80%99t%20normally%20expect%20you%20to%20use%20them%20in%20a%20real%20query%20%E2%80%93%20only%20to%20test%20or%20when%20you%20need%20to%20show%20that%20level%20of%20detail.%3CBR%20%2F%3EI'll%20also%20have%20some%20more%20real%20life%20queries%20at%20the%20end%20of%20this%20article%2C%20but%20for%20now%20I'll%20keep%20them%20basic%20and%20simple.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA13LQQ5AMBAF0L3EHf6yTcTWCcQBuEDxQxczkipFHF4s2b7ktRy24ONZ79SYZzfSzEB0XthQGVzkiJ4xkQrjpsVUo0VZQpdkLOxb1k3EBX8R4tV8qi0g7vhZnj3loliidgAAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20between%20(ago(7d)%20..%20now()%20)%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EResult%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3Emin_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emax_TimeGenerated%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2020-09-08T08%3A23%3A39.887Z%3C%2FTD%3E%0A%3CTD%3E2020-09-15T08%3A23%3A29.323Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20above%20is%20really%20just%20a%20longer%20way%20of%20writing%20the%20example%20in%20query%201%20as%20it%20went%20to%20now()%20anyway.%26nbsp%3B%20It%20does%20allow%20you%20however%20to%20amend%20it%20to%20this%2C%20where%20we%20look%20between%207%20and%205%20days%20ago.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%20%0A%7C%20where%20TimeGenerated%20between%20(ago(7d)%20..%20ago(5d)%20)%20%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ERead%20on%2C%20in%20example%204%20we%20will%20make%20these%20results%20more%20accurate%2Fpredictable.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E3.%26nbsp%3BDuring%20investigations%20you%20may%20have%20a%20date%20and%20time%20range%20in%20mind%2C%20or%20you%20wish%20to%20reduce%20the%20data%20volume%20returned.%26nbsp%3B%20%3CBR%20%2F%3ETip%3A%20You%20can%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Ftopoperator%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ETop%3C%2FA%3E%20or%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Flimitoperator%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ELimit%3C%2FA%3E%20to%20help%20reduce%20the%20amount%20of%20returned%20data.%26nbsp%3B%20Please%20look%20at%20the%20supplied%20help%20links%20for%20examples.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20amend%20the%20query%20(%232)%20to%20provide%20an%20actual%20date%20%2F%20time.%3C%2FP%3E%0A%3CP%3ENotice%2C%20this%20gets%20data%20from%201%3CSUP%3Est%3C%2FSUP%3E%20July%20through%20to%2030%3CSUP%3Eth%3C%2FSUP%3E%20July%2C%20but%20only%20until%209am%20(I%20added%20this%20to%20show%20you%20can%20do%20both%20date%20and%20time%20in%20the%20one%20syntax).%3C%2FP%3E%0A%3CP%3EThis%20is%20especially%20useful%20for%20looking%20maybe%20at%20your%20online%20business%20day%20or%20a%20known%20period%20that%20you%20are%20particularly%20interested%20in.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA2WMMQ6CQBQFexLu8MrdBMgHC6O98QB6gUVe4hZ%25252Fi%25252FUDajg8WKrlzCRz4W3M0V6nicnKYsF8ZyauUXlmYg7GAT1tJhMcho1ta66TTmrZ19J6NM2%25252F30kFORxFPPxn%25252BxhVQ45vQmNyX3tfQcPzx5XFCqajvhCaAAAA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20between%20(%20datetime(2020-07-01)%20..%20datetime(2020-07-30%2C%2009%3A00)%20)%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.%26nbsp%3BHere%20is%20where%20I%20see%20issues%20with%20many%20queries%2C%20just%20like%20the%20queries%20above%20which%20are%20using%20%3CSTRONG%3Enow%20%3C%2FSTRONG%3Eas%20a%20point%20in%20time%20to%20pivot%20on.%26nbsp%3Bi.e.%20Show%20the%20data%20from%20%3CSTRONG%3Enow%3C%2FSTRONG%3E%20until%201day%20ago.%26nbsp%3B%20%3CBR%20%2F%3E%3CFONT%20size%3D%225%22%3EThis%20will%20mean%20that%20the%20returned%20data%20will%20change%20each%26nbsp%3Btime%20you%20run%20the%20query%2C%20which%20may%20give%20unpredictable%20or%20undesirable%20results.%26nbsp%3B%20%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EI%20have%20seen%20many%20issues%20in%20the%20past%20due%20to%20this%2C%20questions%20are%20often%20asked%20on%20the%20forums%2C%20asking%20where%20the%20data%20has%20gone%2C%20as%20it%20%22was%20there%20when%20I%20last%20ran%20the%20query%22%20-%20this%20is%20often%20due%20to%20this%20form%20of%20syntax%20being%20used%2C%20now()%20or%20ago().%3CBR%20%2F%3E%3CBR%20%2F%3EWe%20can%20solve%20this%20with%20a%20powerful%20function%20called%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fstartofdayfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSTRONG%3Estartofday%3C%2FSTRONG%3E%3C%2FA%3E()%2C%20and%20don't%20worry%20there%20are%20other%20functions%20as%20well%2C%20read%20on%20for%20those!%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAAwtOTS4tyiypdC1LzSvh5apRKM9ILUpVCMnMTXVPzUstSixJTVGwUyguSSwqyU9LSazUSEzP1zBM0dQEKS4uzc1NLMqsSlXIzczTQNGkqaOQm1iBJsbLBQDIOhGUcAAAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3EResult%3A%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3Emin_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emax_TimeGenerated%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2020-09-14T00%3A00%3A00.103Z%3C%2FTD%3E%0A%3CTD%3E2020-09-15T08%3A37%3A10.497Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CP%3EIn%20this%20example%20using%20startofday%2C%20we%20are%20saying%20go%20from%20%E2%80%98the%20start%20of%20day%E2%80%99%20(the%20first%20record%20found%20after%20mid-night)%20until%20the%20end%20time.%26nbsp%3B%20So%20in%20this%20query%20%3CSTRONG%3Estartofday(ago(1d))%3C%2FSTRONG%3E%20is%20a%20fixed%20point%20in%20time%20close%20to%20midnight%20one%20day%20ago%2C%20until%20now()%20-%20so%20you%20are%20seeing%20more%20that%20one%20days%20worth%20of%20data.%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%222%22%3ETip%3A%20This%20can%20also%20make%20your%20charts%20look%20better%2C%20as%20you%20get%20a%20full%20day%20of%20data%20at%20each%20end%20of%20the%20y-axis.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E5.%26nbsp%3BWe%20can%20now%20combine%20startofday()%20and%20with%20another%20function%20called%20%3CSTRONG%3Eendofday%3C%2FSTRONG%3E().%3C%2FP%3E%0A%3CP%3EThis%20query%20guarantees%20you%20have%20data%20between%20two%20fixed%20points.%26nbsp%3B%20So%20you%20should%20always%20get%20the%20same%20results%20between%20query%20executions.%3CBR%20%2F%3EAs%20you%20can%20see%20in%20the%20example%20it's%20possible%20to%20mix%20and%20match%20%3CEM%3Ehours%3C%2FEM%3E%20and%20%3CEM%3Edays%3C%2FEM%3E%2C%20which%20may%20be%20useful%20in%20certain%20scenarios.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA13MMQrCQBAF0F7wDr%25252FcBQkIFl5APIBeYHS%25252FZouZwOzEGPHwaiXavuIdeB69xry70WK5eGLq6cSxKvc0ugQLToyJNCS0EI%25252FhUmROch3SZtt7yxldB1r5%25252Brq8MX%25252B6NqqK1weh1dJPm1dQuf%25252FZC8oeNYqQAAAA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20between%20(%20startofday(ago(48hrs))%20..%20endofday(ago(1d))%20)%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3EResult%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3Emin_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emax_TimeGenerated%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2020-09-13T00%3A00%3A00.043Z%3C%2FTD%3E%0A%3CTD%3E2020-09-14T23%3A59%3A59.62Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E6.%26nbsp%3BYou%20can%20also%20use%20startofweek%2C%20startofmonth%20and%20startofyear%20in%20a%20similar%20way%20to%20the%20example%20in%20query%20%235.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20example%20uses%20startofmonth.%26nbsp%3B%20In%20this%20case%20no%20matter%20what%20entry%20you%20put%20in%20the%20ago()%2C%20the%201%3CSUP%3Est%3C%2FSUP%3E%20day%20of%20the%20month%20is%20used.%26nbsp%3B%20Again%2C%20you%20get%20a%20fixed%20point%20in%20time%2C%20the%20first%20day%20of%20the%20month%20(more%20of%20this%20in%20the%20extended%20examples%20at%20the%20end%20of%20the%20blog)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAAwtOTS4tyiypdC1LzSvh5apRKM9ILUpVCMnMTXVPzUstSixJTVGwUyguSSwqyU%25252FLzc8rydBITM%25252FXMEzR1AQpLy7NzU0syqxKVcjNzNNA0aapo5CbWIEmxssFAGzrPppyAAAA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofmonth(ago(0d))%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%3CSPAN%3EStartofweek%20and%20startofmonth%20are%20great%20functions%20especially%20for%20reporting%2C%20as%20you%20can%20show%20the%20whole%20week%20or%20week%20to%20date%20easily.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EMaybe%20you%20need%20a%20graph%20that%20shows%20this%20view%3F%3C%2FSPAN%3E%0A%3CP%3ENote%3A%20The%20first%20day%20of%20the%20week%20is%20Sunday%20(day%200).%3CBR%20%2F%3ETo%20adjust%20to%20Monday%20please%20use%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%7C%20where%20TimeGenerated%20%26gt%3B%20startofweek(ago(1d))%2B1d%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CDIV%3E%2B2%20for%20Tuesday%20etc...%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E7.%26nbsp%3B%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Eyou%20may%20need%20the%20oldest%20or%20latest%20records%2C%20in%20examples%20%231-6%20I%20have%20shown%20queries%20using%20min%20and%20%3C%2FSPAN%3E%3CA%20style%3D%22font-family%3A%20inherit%3B%20background-color%3A%20%23ffffff%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmax-aggfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Emax%3C%2FA%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E.%26nbsp%3B%20You%20can%20also%20use%20%3C%2FSPAN%3E%3CA%20style%3D%22font-family%3A%20inherit%3B%20background-color%3A%20%23ffffff%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Farg-min-aggfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Earg_min%3C%2FA%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20and%20arg_max%20instead%2C%20please%20see%20the%20help%20link%20for%20examples.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAAwtOTS4tyiypdC1LzSvh5apRKM9ILUpVCMnMTXVPzUstSixJTVGwUyguSSwqyU9LSazUSEzP1zBM0dQEKS4uzc1NLMqsSlVQSCxKj89NrNBA0amjoAVUBwDcjqErZAAAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0AIn%20this%20query%20we%20get%20just%20the%20latest%20record%20for%20the%20time%20range%20selected.%26nbsp%3B%20This%20is%20useful%20if%20you%20only%20need%20the%20latest%20(arg_max)%20or%20earliest%20(arg_min)%20records%2C%20and%20doesn%E2%80%99t%20retrieve%20a%20lot%20of%20unwanted%20data%2C%20the%20%E2%80%9C*%E2%80%9D%20in%20the%20second%20parameters%20returns%20us%20all%20the%20Columns%2C%20you%20could%20name%20a%20specific%20column(s)%20to%20return%20if%20you%20prefer%2C%20e.g%3A%20This%20example%20just%20show%20the%20Task%20and%20EventID%20column%2C%20rather%20than%20all%20available%20data.%3C%2FDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%7C%20summarize%20%20arg_max(TimeGenerated%2C%20Task%2C%20EventID)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CBR%20%2F%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ETimeGenerated%3C%2FTH%3E%0A%3CTH%3ETask%3C%2FTH%3E%0A%3CTH%3EEventID%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2020-09-15T16%3A12%3A58.907Z%3C%2FTD%3E%0A%3CTD%3E12545%3C%2FTD%3E%0A%3CTD%3E4634%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E8.%26nbsp%3B%26nbsp%3BThere%20is%20no%20startofhour%20option%2C%20but%20we%20can%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbinfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ebin%3C%2FA%3E%20scalar%20to%20help%20us%20with%20that%2C%20as%20this%20example%20shows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA23MsQrCMBSF4b3QdzhjA106i26im4N9gdgeTcCkcHOjVXx47VQtrof%25252FfFcqEq10rvWBWKNxq7I4ssvi9bG9MWpZvHB3FGJKdowUq%25252Byxgb0M1Xw2U8hRGXsktaKH837I8jFx8rH6OdeNM5jh7%25252Fy%25252Fm3IIVvyTCEvL1Ah2XGxvcHwWDdgAAAA%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20searchTime%20%3D%201h%3B%0ASecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(searchTime)%0A%7C%20extend%20startOfHour%20%3D%20bin(TimeGenerated%2C1h)%0A%7C%20where%20startOfHour%20%26gt%3B%20ago(searchTime)%0A%7C%20summarize%20min(TimeGenerated)%2C%20max(TimeGenerated)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%3CBR%20%2F%3EResult%3A%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3Emin_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emax_TimeGenerated%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2020-09-15T16%3A00%3A00.39Z%3C%2FTD%3E%0A%3CTD%3E2020-09-15T16%3A15%3A49.42Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E9.%20This%20is%20a%20common%20requested%20example%2C%20people%20often%20wish%20to%20show%20data%20between%20or%20outside%20a%20time%20range%20-%20maybe%20'business%20hours'.%26nbsp%3B%20I%20have%20used%20between%20to%20allow%20a%20certain%20range%2C%20but%20you%20can%20also%20use%20%3CSTRONG%3E!between%3C%2FSTRONG%3E%20to%20exclude%20a%20time%20range.%26nbsp%3B%26nbsp%3BThis%20example%20will%20exclude%20rows%20of%20data%20between%2022pm%20and%206am%20(as%20we%20set%2007%20..%2022)%20as%20the%20allowed%20hours%20in%20the%20query.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1WPuwrCQBBFe8F%25252FGFJtIEi0sVGbIFpY2svEvWjA3ZXJxBd%25252BvMkKCemG4dxzZ%25252FZg0RKs08mXnlcI6Fg57OAhrLC0Ib4EM7dpB%25252BCl8JauoRFak20BbeHTvXWYpNsm2TieDtoYKqFPwJPJlzSb0WIRgbpxjqX6gA5ca8G3W2t3%25252FDJjF5VvKoK7NwrJ%25252BmnrH5UE7%25252BA1iyVDZW9b%25252FZ%25252FIXawLYiGdLJ7E9Zmmkx%25252FlWMkpCAEAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20extend%20hour%20%3D%20datetime_part(%22hour%22%2C%20TimeGenerated)%0A%7C%20where%20hour%20between%20(07%20..%2022)%0A%7C%20summarize%20LastCall%20%3D%20max(TimeGenerated)%20by%20Computer%2C%20ComputerEnvironment%2C%20hour%0A%7C%20where%20LastCall%20%26lt%3B%20ago(10m)%0A%7C%20order%20by%20hour%20asc%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E10.%26nbsp%3BNow%20lets%20look%20at%20certain%20days%20of%20the%20week%2C%20in%20this%20case%20Monday%20(day%201)%20thru%20Friday%20(day%205)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30d)%0A%7C%20where%20dayofweek(TimeGenerated)%20in%20('1.00%3A00%3A00'%2C'2.00%3A00%3A00'%2C'3.00%3A00%3A00'%2C'4.00%3A00%3A00'%2C'5.00%3A00%3A00')%0A%7C%20summarize%20count()%20by%20Computer%2C%20bin(TimeGenerated%2C%201d)%0A%7C%20order%20by%20TimeGenerated%20asc%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%2C%20I%20think%20this%20is%20easier%20to%20read%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA3XOsQrCQBCE4V7wHaa8QAgGsbWx0AdIL5vcqCHcnWw2hIgPbySFpLBdvv2ZC0Wtpth288b4oBJVG3hmpIrR4wi5J7ff%25252BewnvEzpNpKdW9kMNW0%25252BR7jSoyhwWJ76IQTR9kU0aYjmshxBOl57mvuXmlsTTik8B6PmqNu4BjnKJZ7UU794PVv65gPtrs1A2wAAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30d)%0A%7C%20where%20dayofweek(TimeGenerated)%20between%20(1d%20..%205d)%0A%7C%20summarize%20count()%2C%20make_set(dayofweek(TimeGenerated))%20by%20Computer%2C%20bin(TimeGenerated%2C%201d)%0A%7C%20order%20by%20TimeGenerated%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E11.%20Lets%20now%20extend%20Query%2010%2C%20we%20can%20also%20add%20a%20Column%20with%20the%20Name%20of%20the%20day%2C%20Monday%2C%20Tuesday%20etc%E2%80%A6%20to%20make%20the%20report%20easier%20to%20read.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30d)%0A%7C%20where%20dayofweek(TimeGenerated)%20between%20(1d%20..%205d)%0A%7C%20extend%20theDay%20%3D%20case(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'0.00%3A00%3A00'%2C%20%22Sunday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'1.00%3A00%3A00'%2C%20%22Monday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'2.00%3A00%3A00'%2C%20%22Tuesday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'3.00%3A00%3A00'%2C%20%22Wednesday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'4.00%3A00%3A00'%2C%20%22Thursday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'5.00%3A00%3A00'%2C%20%22Friday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'6.00%3A00%3A00'%2C%20%22Saturday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20strcat(%22error%3A%20%22%2C%20dayofweek(TimeGenerated))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%7C%20summarize%20by%20theDay%0A%0A%0A%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA6XSsWrDMBAG4N3gdzi8RAZjnKbpEHCn0mbp1EDnc%25252FS3NsUynE8kDnn4hrSlaDCFStJ0%25252FPokuNuCRRuwpsmZDi0EtOt6PMFBWGHpnvh9MKvK5r8Jy9PwdgA%25252BTJDNqYFeyo7M0lJZ0vrrEo4KZ0lbPPBENe15hEkTmlmzel3ToiqranM9i4KyF%25252B8u6az4J7YMsOchCrsJsJ3HGKOtAu0V1sV5t%25252BHvWi9R3DrgHqWLwe7CnrJ6%25252BYMbVfasJoPIIBvKitkH8lnkOpqj73uW7gRqpu8BTZOf%25252FQnnHhhwHAMAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EtheDay%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3ETuesday%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EMonday%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EFriday%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EWednesday%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EThursday%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E12.%20A%20common%20ask%20would%20be%20to%20remove%20the%20Weekend%20from%20the%20returned%20data%2C%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1WOTQrCMBCF94Xe4eEqBUntQl3ppoLu9QJpMtqiSSSdUBQPb4pg6XLefO%25252BnLLHCDufojHrlWVliM56KY0gCiLWUMs9OpAI3pDjPPhhaCoRLZ%25252BlIjoJiMthD3bzYmmICam%25252BfkSmg52Tuh45bLA71qlpMTOrw14HoLmZxBRriJDtAVAZSYv1L7qO1KnRvgvbRsUjg61%25252B0RNO5edAS1c%25252Fog0lLEjyfrXo9fgO58a39I1qn2zQXX5xzvvMZAQAA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%2F%200%20%3D%20Sunday%0A%2F%2F%206%20%3D%20Saturday%20etc...%0AHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7d)%0A%7C%20where%20Computer%20startswith%20%22DC01%22%0A%7C%20where%20dayofweek(TimeGenerated)%20between%20%20(1d%20..%205d)%0A%7C%20summarize%20count()%20by%20Computer%2C%20bin(TimeGenerated%2C%201d)%0A%7C%20order%20by%20TimeGenerated%20asc%0A%7C%20render%20columnchart%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EResult%3A%20You%20can%20see%20the%20gap%20for%20the%20Weekend%20we%20excluded%20(in%20red)%2C%20this%20assumes%20your%20Weekend%20is%20Saturday%20to%20Sunday%2C%20please%20amend%20if%20it%20isn't.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%3A%20If%20desired%20you%20should%20amend%20the%20above%20query%2C%20removing%20this%20line%20(I%20used%20it%20to%20show%20the%20gap%20the%20weekend%20left)%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7d)%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-09-15%20181805.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218416i419F772422EBB8E4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-09-15%20181805.jpg%22%20alt%3D%22Annotation%202020-09-15%20181805.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%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------------------------------------------------------------------------%3C%2FP%3E%0A%3CP%3E%3CSTRONG%20style%3D%22font-family%3A%20inherit%3B%22%3EExtended%20%3C%2FSTRONG%3E%3CSTRONG%3Eexamples%3C%2FSTRONG%3E%3CSTRONG%20style%3D%22font-family%3A%20inherit%3B%22%3E%26nbsp%3Bthat%20you%20might%20really%20use.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20we%20will%20use%20some%20of%20the%20above%20examples%2C%20in%20real%20queries%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Show%20unique%20counts%20of%20EventIds%2C%20per%20day%20over%20the%20last%20Week%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1WOsQrCMBiEd8F3ODqloAWnTm6KONsXSJvTBmyiyR9LxYc31kW344777k7sUrAy7R90sly8MPYMRGMHHugYtNCgpYykg0IUHcSfjZ6UvnhVm7JEVf3azo8qm%25252BWHFdMw6GCfhOl8cqLmleOuRDuhtU79zaw2Zm4FOsMAyVnXZ25WciW2KJqeSM7eEzHz4M%25252F4Ik3EGjcdBXU%25252BEYs3eu99ptcAAAA%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20between%20(%20startofday(ago(7d))%20..%20startofday(now())%20)%0A%7C%20summarize%20dcount(EventID)%20by%20bin(TimeGenerated%2C1d)%0A%7C%20render%20timechart%20title%20%3D%20%22The%20unique%20count%20of%20EventIds%20-%20past%207days%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-09-15%20183751%20-1%20.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218420i8C4672A5C78CCB22%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-09-15%20183751%20-1%20.jpg%22%20alt%3D%22Annotation%202020-09-15%20183751%20-1%20.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B2.%20Show%20unique%20EventIds%20so%20far%20during%20the%20current%20month%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1XOsQrCQBAE0F7wH4ZUF1DBVrCLiIWVwT7JjdxBsoeXPUPEjzeJld3Asm%25252FmxiZFr%25252BPpRdH16oPBMRKl73imMFZKi5o6kAKDXquo4dEFUWckDCbPgd0OS0Q%25252BA33quir6N2GbkETNQl%25252BKHPWI2ov5szd7u3xFimWETrfGTR1T0pY4IisdkcQ%25252FE7F4CA%25252F8SNtji%25252Bs8pR1x9xwOKKoRe6iLCRrmVdl69QWxJwYz4wAAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now())%20..%20now()%20)%0A%7C%20summarize%20dcount(EventID)%20by%20bin(TimeGenerated%2C1d)%0A%7C%20render%20timechart%20title%20%3D%20%22The%20unique%20count%20of%20EventIds%20-%20Monthly%20View%3A%20Day%201%20thru%20to%20Today%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-09-15%20183818%20-2.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218421i74E5490FFD9D1A52%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-09-15%20183818%20-2.jpg%22%20alt%3D%22Annotation%202020-09-15%20183818%20-2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E3.%20Show%20unique%20EventIds%20so%20far%20this%20current%20month%2C%20excluding%20weekends%20(Saturday%20%26amp%3B%20Sunday).%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1WPQUsDMRBG74X%25252Bh4%25252BeEtDCHrwIvVXEgycXe06T2Sa4mWB24prijzfdgtLbXN6b772RLTlIffoilvXqB7OnTOhDpGdiykbI4UgyEzEUJjFZ0hATi1ecZqU1sN1iOaH%25252FBc7UNDToQ92o9J8LqnMX8sEt1FRiNDmcG2lTYVHLoJd9AyqOgW81d92VysSOMmDTWCJb38YBEmQk7LDpPaFw%25252BCyExYk04Kp1E%25252B7xeokYK94DzY%25252FYm4oO4nOBJPSpBUDRtx2LC3zCoaW0Z5PerFe%25252FtsVIgTQBAAA%25253D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%20%3C%2FA%3E%3CBR%20%2F%3EYou%20can%20adjust%20for%20a%20Friday%20to%20Saturday%20weekend%2C%26nbsp%3B%20swap%201d%20..%205d%20to%200d%20..%204d%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now())%20..%20now()%20)%0A%7C%20where%20dayofweek(TimeGenerated)%20between%20(1d%20..%205d)%0A%7C%20summarize%20dcount(EventID)%20by%20bin(TimeGenerated%2C1d)%0A%7C%20render%20columnchart%20title%20%3D%20%22The%20unique%20count%20of%20EventIds%20-%20Monthly%20View%3A%20Day%201%20thru%20to%20Today%20(excluding%20Weekends)%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-09-15%20183848%20-3%20.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218422i573C10B27A60FDE0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-09-15%20183848%20-3%20.jpg%22%20alt%3D%22Annotation%202020-09-15%20183848%20-3%20.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E4.%20Show%20unique%20EventIds%20for%20the%20current%20Week%2C%20Week%20starting%20on%20Monday.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1WOsU7DQBBE%25252B0j5h1EqW0CRFgkqEKJIFQvqy91EPhHvivUelhEfjy80uNyZnZl3ZCyWfX7%25252Bovh284OppxFdHvhCoQVnwiNGD%25252BZ6nsiPRnRq2hY32KcaGMswBMvfRIpaxJtr1etTi9OMU5Zm1XW7T21NqSVa%25252FVgvhTFW1yjVjnopg8R%25252B2cY%25252FeaXDs1%25252BIB%25252By6niiSPwtxBYGe8ceSRtzhfWHHW%25252BZ0j4NKCjO8twJXdLpcu%25252B3mF6oYre8MAQAA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EI%20used%20%3CSTRONG%3E%2B1d%3C%2FSTRONG%3E%20to%20make%20the%20week%20start%20on%20Monday%2C%20as%20the%20default%20is%20Sunday%20(0d).%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofweek(now())%20%2B%201d%0A%7C%20summarize%20dcount(EventID)%20by%20bin(TimeGenerated%2C1d)%0A%7C%20order%20by%20TimeGenerated%20asc%0A%7C%20render%20columnchart%0A%7C%20render%20columnchart%20title%20%3D%20%22The%20unique%20count%20of%20EventIds%20-%20Week%20View%3A%20Monday%20thru%20to%20Today%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-09-15%20183924%20-%204.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218423i407EA348F6C5F4EA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-09-15%20183924%20-%204.jpg%22%20alt%3D%22Annotation%202020-09-15%20183924%20-%204.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1910508367%22%20id%3D%22toc-hId-1913074295%22%20id%3D%22toc-hId-1913074295%22%20id%3D%22toc-hId-1913074295%22%20id%3D%22toc-hId-1913074295%22%20id%3D%22toc-hId-1913074295%22%3ESummary%3C%2FH3%3E%0A%3CP%3EThe%20above%20examples%20should%20give%20you%20some%20ideas%20on%20how%20to%20search%20(and%20visualize)%20your%20data%20using%20Time%20and%20Dates%20techniques.%26nbsp%3B%20Did%20I%20miss%20any%20you%20use%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1667574%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20of%20the%20key%20things%20you%20do%20in%20Azure%20Sentinel%20is%20look%20at%20data.%26nbsp%3B%20The%20data%20is%20stored%20in%20Azure%20Monitor%20Logs%20(Log%20Analytics)%2C%20each%20row%20of%20stored%20data%20has%20a%20column%20called%20%3CSTRONG%3ETimeGenerated%3C%2FSTRONG%3E%20that%20contains%20a%20UTC%20date.%26nbsp%3B%20You%20may%20choose%20to%20look%20at%20the%20data%20through%20the%20Azure%20Sentinel%20User%20Interface%20(UI)%2C%20Workbooks%20or%20Notebooks%2C%20in%20this%20article%20we%20are%20discussing%20how%20to%20use%20KQL%20(Kusto%20Query%20Language)%20to%20access%20the%20Logs%20data.%26nbsp%3B%20%3CBR%20%2F%3EKnowing%20how%20to%20work%20with%20time%2C%20will%20help%20you%20align%20your%20queries%2C%20rules%2C%20and%20hunting%20use%20cases.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22logsTimeWindow_intro.gif%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218319i035E9AE93DE1BD13%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22logsTimeWindow_intro.gif%22%20alt%3D%22logsTimeWindow_intro.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1667574%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Sentinel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EInvestigation%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2032477%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20align%20your%20Analytics%20with%20time%20windows%20in%20Azure%20Sentinel%20using%20KQL%20(Kusto%20Query%20Language)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2032477%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%3B%26nbsp%3B%3C%2FP%3E%3CP%3EVery%20good%20your%20article.%3C%2FP%3E%3CP%3EReally%20enjoyed.%3C%2FP%3E%3CP%3EApplicable%20examples.%3C%2FP%3E%3CP%3EI'm%20having%20a%20problem%20and%20maybe%20you%20can%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20in%20GMT-3%20time%2C%20however%2C%20when%20executing%20the%20query%2C%20only%20the%20displayed%20view%20that%20converts%20to%20GMT-3%20time%2C%20but%20the%20query%20needs%20to%20be%20drawn%20in%20UTC%20time%2C%20because%20if%20inserted%20in%20my%20local%20time%2C%20it%20returns%20inconsistent%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20usually%20have%20this%20problem%20when%20using%20the%20command%20%5Bbetween%5D%2C%20because%20I%20need%20to%20insert%20it%20in%20UTC%20time%2C%20in%20this%20case%2C%20every%20time%20I%20need%20to%20research%20thinking%203%20hours%20ahead.%20It%20disturbs%20and%20confuses%20me%20at%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPoint%20X%20is%3A%20Can%20you%20tell%20me%20if%20I%20can%20modify%20any%20parameter%20or%20use%20a%20mallet%20to%20search%20the%20KQL%20with%20a%20time%20other%20than%20UTC%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2035812%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20align%20your%20Analytics%20with%20time%20windows%20in%20Azure%20Sentinel%20using%20KQL%20(Kusto%20Query%20Language)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2035812%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20set%20Local%20Time%20in%20the%20Log%20Analytics%20settings%20(or%20swap%20using%20the%20UI)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202021-01-06%20105528.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244511i752D18D424B29079%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Annotation%202021-01-06%20105528.jpg%22%20alt%3D%22Annotation%202021-01-06%20105528.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eor%20use%20a%20offset%20in%20the%20code%2C%20such%20as%20%3CSTRONG%3Eminus%3C%2FSTRONG%3E%203hours%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%3EAzureDiagnostics%E2%80%AF%E2%80%AF%20%0A%7C%20extend%20utc%20%3D%20now()%0A%7C%20extend%20greenland%20%3D%20now()-3h%0A%7C%20summarize%20max(utc)%2C%20max(greenland)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Microsoft

Overview

Thanks to Ofer Shezaf, Kieran Bhardwaj and Younes Khaldi for the ideas and proof reading!
 

Many of the query examples you see in KQL (Kusto Query Language) Detections, Rules, Hunting and Workbooks use a time filter.  In fact, the first recommendation in the best practices section is:

Annotation 2020-09-15 091522.jpg

Source: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices

 

Now I will show you some hopefully simple but useful query examples (and I’ll use the demo data we provide for free so you can try these). 

Please click the links provided, all you need is a Microsoft account to grant you access to our extensive demo data.

You should also get familiar with the timespan literals: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/timespan

Query Examples 

1. This first example looks back one day in time (looking back over the last 24hrs, from the moment you run the query); you can use 24h instead of 1d if you prefer. 
I prefer using 1d rather than 24hrs, typically I only use hours when I need a partial day i.e. 6h
This form of time span filter is one of the most common lines people add to a query.   

Please read on for an explanation on why this may not be what you intended, and ways to improve.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > ago(1d)

 

2. You can also look between a range.  Here I look back 7days from now (today at this current time).  
Notice in the last line, I show the oldest record and the latest that are returned – I’ll do this for many of the examples just so you can see how the query is working,  I wouldn’t normally expect you to use them in a real query – only to test or when you need to show that level of detail.
I'll also have some more real life queries at the end of this article, but for now I'll keep them basic and simple.

 

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between (ago(7d) .. now() )
| summarize min(TimeGenerated), max(TimeGenerated)

Result:

min_TimeGenerated max_TimeGenerated
2020-09-08T08:23:39.887Z 2020-09-15T08:23:29.323Z

 

The above is really just a longer way of writing the example in query 1 as it went to now() anyway.  It does allow you however to amend it to this, where we look between 7 and 5 days ago.

SecurityEvent 
| where TimeGenerated between (ago(7d) .. ago(5d) ) 
| summarize min(TimeGenerated), max(TimeGenerated)

Read on, in example 4 we will make these results more accurate/predictable.



3. During investigations you may have a date and time range in mind, or you wish to reduce the data volume returned. 
Tip: You can use Top or Limit to help reduce the amount of returned data.  Please look at the supplied help links for examples.

 

You can amend the query (#2) to provide an actual date / time.

Notice, this gets data from 1st July through to 30th July, but only until 9am (I added this to show you can do both date and time in the one syntax).

This is especially useful for looking maybe at your online business day or a known period that you are particularly interested in.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( datetime(2020-07-01) .. datetime(2020-07-30, 09:00) )
| summarize min(TimeGenerated), max(TimeGenerated)

 

4. Here is where I see issues with many queries, just like the queries above which are using now as a point in time to pivot on. i.e. Show the data from now until 1day ago. 
This will mean that the returned data will change each time you run the query, which may give unpredictable or undesirable results. 

I have seen many issues in the past due to this, questions are often asked on the forums, asking where the data has gone, as it "was there when I last ran the query" - this is often due to this form of syntax being used, now() or ago().

We can solve this with a powerful function called startofday(), and don't worry there are other functions as well, read on for those!
Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > startofday(ago(1d))
| summarize min(TimeGenerated), max(TimeGenerated)

Result: 

min_TimeGenerated max_TimeGenerated
2020-09-14T00:00:00.103Z 2020-09-15T08:37:10.497Z

 

In this example using startofday, we are saying go from ‘the start of day’ (the first record found after mid-night) until the end time.  So in this query startofday(ago(1d)) is a fixed point in time close to midnight one day ago, until now() - so you are seeing more that one days worth of data.

Tip: This can also make your charts look better, as you get a full day of data at each end of the y-axis.

 

5. We can now combine startofday() and with another function called endofday().

This query guarantees you have data between two fixed points.  So you should always get the same results between query executions.
As you can see in the example it's possible to mix and match hours and days, which may be useful in certain scenarios.

 

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( startofday(ago(48hrs)) .. endofday(ago(1d)) )
| summarize min(TimeGenerated), max(TimeGenerated)

Result:

min_TimeGenerated max_TimeGenerated
2020-09-13T00:00:00.043Z 2020-09-14T23:59:59.62Z

 

6. You can also use startofweek, startofmonth and startofyear in a similar way to the example in query #5. 

The following example uses startofmonth.  In this case no matter what entry you put in the ago(), the 1st day of the month is used.  Again, you get a fixed point in time, the first day of the month (more of this in the extended examples at the end of the blog)

 

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > startofmonth(ago(0d))
| summarize min(TimeGenerated), max(TimeGenerated)
Startofweek and startofmonth are great functions especially for reporting, as you can show the whole week or week to date easily.  Maybe you need a graph that shows this view?

Note: The first day of the week is Sunday (day 0).
To adjust to Monday please use:

| where TimeGenerated > startofweek(ago(1d))+1d
+2 for Tuesday etc...
 
7. you may need the oldest or latest records, in examples #1-6 I have shown queries using min and max.  You can also use arg_min and arg_max instead, please see the help link for examples.

 

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > startofday(ago(1d))
| summarize arg_max(TimeGenerated, *)

 

In this query we get just the latest record for the time range selected.  This is useful if you only need the latest (arg_max) or earliest (arg_min) records, and doesn’t retrieve a lot of unwanted data, the “*” in the second parameters returns us all the Columns, you could name a specific column(s) to return if you prefer, e.g: This example just show the Task and EventID column, rather than all available data.
SecurityEvent
| where TimeGenerated > startofday(ago(1d))
| summarize  arg_max(TimeGenerated, Task, EventID)

TimeGenerated Task EventID
2020-09-15T16:12:58.907Z 12545 4634

 

8.  There is no startofhour option, but we can use the bin scalar to help us with that, as this example shows.

 

Go to Log Analytics and run query

let searchTime = 1h;
SecurityEvent
| where TimeGenerated > ago(searchTime)
| extend startOfHour = bin(TimeGenerated,1h)
| where startOfHour > ago(searchTime)
| summarize min(TimeGenerated), max(TimeGenerated)


Result: 

min_TimeGenerated max_TimeGenerated
2020-09-15T16:00:00.39Z 2020-09-15T16:15:49.42Z

 

9. This is a common requested example, people often wish to show data between or outside a time range - maybe 'business hours'.  I have used between to allow a certain range, but you can also use !between to exclude a time range.  This example will exclude rows of data between 22pm and 6am (as we set 07 .. 22) as the allowed hours in the query.

Go to Log Analytics and run query

Heartbeat
| where TimeGenerated > ago(1d)
| extend hour = datetime_part("hour", TimeGenerated)
| where hour between (07 .. 22)
| summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment, hour
| where LastCall < ago(10m)
| order by hour asc

 


10. Now lets look at certain days of the week, in this case Monday (day 1) thru Friday (day 5)

Heartbeat
| where TimeGenerated > ago(30d)
| where dayofweek(TimeGenerated) in ('1.00:00:00','2.00:00:00','3.00:00:00','4.00:00:00','5.00:00:00')
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated asc

or, I think this is easier to read

 Go to Log Analytics and run query

Heartbeat
| where TimeGenerated > ago(30d)
| where dayofweek(TimeGenerated) between (1d .. 5d)
| summarize count(), make_set(dayofweek(TimeGenerated)) by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated asc

 

11. Lets now extend Query 10, we can also add a Column with the Name of the day, Monday, Tuesday etc… to make the report easier to read.

 

Heartbeat
| where TimeGenerated > ago(30d)
| where dayofweek(TimeGenerated) between (1d .. 5d)
| extend theDay = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
| summarize by theDay




Go to Log Analytics and run query

theDay
Tuesday
Monday
Friday
Wednesday
Thursday

 

12. A common ask would be to remove the Weekend from the returned data, like this:

 Go to Log Analytics and run query

// 0 = Sunday
// 6 = Saturday etc...
Heartbeat
| where TimeGenerated > ago(7d)
| where Computer startswith "DC01"
| where dayofweek(TimeGenerated) between  (1d .. 5d)
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated asc
| render columnchart 

 

Result: You can see the gap for the Weekend we excluded (in red), this assumes your Weekend is Saturday to Sunday, please amend if it isn't. 

Note: If desired you should amend the above query, removing this line (I used it to show the gap the weekend left):

| where TimeGenerated > ago(7d)

Annotation 2020-09-15 181805.jpg

 

------------------------------------------------------------------------

Extended examples that you might really use.

 

Now we will use some of the above examples, in real queries:

 

1. Show unique counts of EventIds, per day over the last Week: Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( startofday(ago(7d)) .. startofday(now()) )
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| render timechart title = "The unique count of EventIds - past 7days"

 Annotation 2020-09-15 183751 -1 .jpg

 2. Show unique EventIds so far during the current month: Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( startofmonth(now()) .. now() )
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| render timechart title = "The unique count of EventIds - Monthly View: Day 1 thru to Today"

 Annotation 2020-09-15 183818 -2.jpg

3. Show unique EventIds so far this current month, excluding weekends (Saturday & Sunday). Go to Log Analytics and run query
You can adjust for a Friday to Saturday weekend,  swap 1d .. 5d to 0d .. 4d

SecurityEvent
| where TimeGenerated between ( startofmonth(now()) .. now() )
| where dayofweek(TimeGenerated) between (1d .. 5d)
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| render columnchart title = "The unique count of EventIds - Monthly View: Day 1 thru to Today (excluding Weekends)"

 Annotation 2020-09-15 183848 -3 .jpg

4. Show unique EventIds for the current Week, Week starting on Monday. Go to Log Analytics and run query 
I used +1d to make the week start on Monday, as the default is Sunday (0d).

SecurityEvent
| where TimeGenerated > startofweek(now()) + 1d
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| order by TimeGenerated asc
| render columnchart
| render columnchart title = "The unique count of EventIds - Week View: Monday thru to Today"

Annotation 2020-09-15 183924 - 4.jpg

5.   Maintenance Window is the last weekend of the month, exclude those days.  

 
//  Maintenance Window is the last weekend of the month 
//
Heartbeat 
// Look at last month 
where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 
where Computer == "JBOX00"     // < insert your Computer name here >
// find 4th week, which is week "3" (count starts at zero) 
extend maintSaturday_ = endofweek(startofmonth(now(),-1),3) -1d, maintSunday_ = endofweek(startofmonth(now(),-1),3) + 1
// exclude 4th week from data set 
where TimeGenerated !between ( maintSaturday_ .. maintSunday_ ) 
summarize heartbeat_per_hour=count() by bin(TimeGenerated, 1h), Computer 

Summary

The above examples should give you some ideas on how to search (and visualize) your data using Time and Dates techniques.  Did I miss any you use?  

3 Comments
Occasional Contributor

@Clive Watson  

Very good your article.

Really enjoyed.

Applicable examples.

I'm having a problem and maybe you can help me.

 

I work in GMT-3 time, however, when executing the query, only the displayed view that converts to GMT-3 time, but the query needs to be drawn in UTC time, because if inserted in my local time, it returns inconsistent values.

 

I usually have this problem when using the command [between], because I need to insert it in UTC time, in this case, every time I need to research thinking 3 hours ahead. It disturbs and confuses me at times.

 

Point X is: Can you tell me if I can modify any parameter or use a mallet to search the KQL with a time other than UTC?

Microsoft

You can set Local Time in the Log Analytics settings (or swap using the UI)

Annotation 2021-01-06 105528.jpg

or use a offset in the code, such as minus 3hours?

AzureDiagnostics   
| extend utc = now()
| extend greenland = now()-3h
| summarize max(utc), max(greenland)
Occasional Contributor

Thank you for your help, @Clive Watson .

 

However, my problem is not about displaying the time, but searching in my GMT-3 and not in UTC.

In the example below, the alert generated in my time at 09:36 PM on 12/20, however, I can only reach this information by searching for an additional 3 hours, thus searching, on 12/21, at 00:36.

This is bad, because I always need to think about the time to write my queries with an additional 3 hours, because if you research thinking about my schedule, in fact, the event occurred 3 hours after.

 

apagar.PNG