Compare Kusto results from three timespans

%3CLINGO-SUB%20id%3D%22lingo-sub-926850%22%20slang%3D%22en-US%22%3ECompare%20Kusto%20results%20from%20three%20timespans%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-926850%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20seen%20some%20examples%20of%20this%2C%20but%20not%20quite%20nailed%20it%20myself.%20I%20basically%20want%20to%20find%20the%20number%20of%20users%20this%20week%2C%20last%20week%20and%20the%20week%20before%20that%20and%20put%20it%20in%20a%20simple%20table.%20I%20think%20I%20need%20to%20use%20the%20LET%2C%20but%20haven't%20figured%20it%20out%20yet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheses%20are%20the%20three%20basic%20KQL's%20I%20want%20to%20to%20create%20a%20simple%20table%20of%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EcustomEvents%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btimestamp%26nbsp%3B%26lt%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E14%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btimestamp%26nbsp%3B%26gt%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E21%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BDeviceId_%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Eparse_json%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(customDimensions.Properties)).DeviceId)%3C%2FSPAN%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Edcount%3C%2FSPAN%3E%3CSPAN%3E(DeviceId_)%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3EcustomEvents%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btimestamp%26nbsp%3B%26lt%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btimestamp%26nbsp%3B%26gt%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E14%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BDeviceId_%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Eparse_json%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(customDimensions.Properties)).DeviceId)%3C%2FSPAN%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Edcount%3C%2FSPAN%3E%3CSPAN%3E(DeviceId_)%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3EcustomEvents%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btimestamp%26nbsp%3B%26gt%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BDeviceId_%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Eparse_json%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(customDimensions.Properties)).DeviceId)%3C%2FSPAN%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Edcount%3C%2FSPAN%3E%3CSPAN%3E(DeviceId_)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-926850%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EApplication%20Insights%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934142%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20Kusto%20results%20from%20three%20timespans%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934142%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430879%22%20target%3D%22_blank%22%3E%40gauteweb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20used%20Log%20Analytics%20demo%20data%2C%20and%20I've%20used%20a%20case()%20function%20as%20all%20your%20data%20is%20in%20the%20same%20table%2C%20we%20can%20look%20at%20the%20the%20time%20buckets.%26nbsp%3B%20Tweak%20as%20required....%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%2FH4sIAAAAAAAAA62SQUvEMBCF7wv7Hx49pVACrYW9qKcV8Srel7SZrVGaLMl0uyv%25252BeNMqq5YuHtxcAi%25252BT9w0z725PlpeLd%25252FTP5AlPpqV7suQVk8YtAivPbqvVUajGiSLXaTpU04HJalRd%25252FUqMG9Qq0HKB2SPOPWCCq4h7Igsxoa4iFJAS1vUiTbOJSZJLXGOFWB6S7L%25252BwvBxoETbTREQnhYwosENeXog4DnWGOHaSZsmVHFiRWOR%25252FEpNSjlS4joPR8d7CK9tQcu7Pz3WuaW9qetCbuFF2gb2xjdgpH2jzEpwVJ%25252B0xVse46DWF2psdG2fTz2CErm2VN28E6Np1lsXJNE6vNVb8GsqgqcNEQ3X8Stbo6Dx%25252FK1Ch%25252FgBrMMoStAIAAA%25253D%25253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(21d))%0A%7C%20extend%20bucket%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(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TimeGenerated%20between%20(startofday(ago(7d))%20%20..%20now())%2C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%221.%20%26lt%3B%207%20days%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%20TimeGenerated%20between%20(startofday(ago(14d))%20..%20startofday(ago(7d)))%2C%20%222.%207%20%20to%2014%20days%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%20TimeGenerated%20between%20(startofday(ago(21d))%20..%20startofday(ago(14d)))%2C%223.%2014%20to%2021%20days%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%20%224.%20Time%20outside%20of%20range%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%7C%20extend%20DeviceId_%20%3D%20tostring(parse_json(tostring(RenderedDescription)))%0A%7C%20summarize%20%20dcount(DeviceId_)%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%20by%20bucket%0A%7C%20sort%20by%20bucket%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExample%20result%3CBR%20%2F%3E%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%2FH4sIAAAAAAAAA62SQUvEMBCF7wv7Hx49pVACrYW9qKcV8Srel7SZrVGaLMl0uyv%25252BeNMqq5YuHtxcAi%25252BT9w0z725PlpeLd%25252FTP5AlPpqV7suQVk8YtAivPbqvVUajGiSLXaTpU04HJalRd%25252FUqMG9Qq0HKB2SPOPWCCq4h7Igsxoa4iFJAS1vUiTbOJSZJLXGOFWB6S7L%25252BwvBxoETbTREQnhYwosENeXog4DnWGOHaSZsmVHFiRWOR%25252FEpNSjlS4joPR8d7CK9tQcu7Pz3WuaW9qetCbuFF2gb2xjdgpH2jzEpwVJ%25252B0xVse46DWF2psdG2fTz2CErm2VN28E6Np1lsXJNE6vNVb8GsqgqcNEQ3X8Stbo6Dx%25252FK1ChXnwAc3Sl6LUCAAA%25253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%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%3Ebucket%3C%2FTH%3E%0A%3CTH%3Edcount_DeviceId_%3C%2FTH%3E%0A%3CTH%3Emin_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emax_TimeGenerated%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1.%20%26lt%3B%207%20days%3C%2FTD%3E%0A%3CTD%3E249522%3C%2FTD%3E%0A%3CTD%3E2019-10-16T00%3A00%3A31Z%3C%2FTD%3E%0A%3CTD%3E2019-10-23T10%3A21%3A29.677Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2.%207%20to%2014%20days%3C%2FTD%3E%0A%3CTD%3E264440%3C%2FTD%3E%0A%3CTD%3E2019-10-09T00%3A00%3A02.517Z%3C%2FTD%3E%0A%3CTD%3E2019-10-15T23%3A59%3A56.047Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E3.%2014%20to%2021%20days%3C%2FTD%3E%0A%3CTD%3E419510%3C%2FTD%3E%0A%3CTD%3E2019-10-02T00%3A00%3A02.023Z%3C%2FTD%3E%0A%3CTD%3E2019-10-08T23%3A59%3A58.063Z%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I've seen some examples of this, but not quite nailed it myself. I basically want to find the number of users this week, last week and the week before that and put it in a simple table. I think I need to use the LET, but haven't figured it out yet.

 

Theses are the three basic KQL's I want to to create a simple table of:

 

customEvents
where timestamp < ago(14dand timestamp > ago(21d)
extend DeviceId_ = tostring(parse_json(tostring(customDimensions.Properties)).DeviceId) 
summarize dcount(DeviceId_)

customEvents
where timestamp < ago(7dand timestamp > ago(14d)
extend DeviceId_ = tostring(parse_json(tostring(customDimensions.Properties)).DeviceId) 
summarize dcount(DeviceId_)

customEvents
where timestamp > ago(7d)
extend DeviceId_ = tostring(parse_json(tostring(customDimensions.Properties)).DeviceId) 
summarize dcount(DeviceId_)

 

 

1 Reply
Highlighted

@gauteweb 

 

I've used Log Analytics demo data, and I've used a case() function as all your data is in the same table, we can look at the the time buckets.  Tweak as required....

Go to Log Analytics and Run Query

Event
| where TimeGenerated > startofday(ago(21d))
| extend bucket = case
                      (
                        TimeGenerated between (startofday(ago(7d))  .. now()),               "1. < 7 days",
                        TimeGenerated between (startofday(ago(14d)) .. startofday(ago(7d))), "2. 7  to 14 days",
                        TimeGenerated between (startofday(ago(21d)) .. startofday(ago(14d))),"3. 14 to 21 days",
                        "4. Time outside of range"
                      )
| extend DeviceId_ = tostring(parse_json(tostring(RenderedDescription)))
| summarize  dcount(DeviceId_), min(TimeGenerated), max(TimeGenerated) by bucket
| sort by bucket asc

 

 

Example result

Go to Log Analytics and Run Query

bucket dcount_DeviceId_ min_TimeGenerated max_TimeGenerated
1. < 7 days 249522 2019-10-16T00:00:31Z 2019-10-23T10:21:29.677Z
2. 7 to 14 days 264440 2019-10-09T00:00:02.517Z 2019-10-15T23:59:56.047Z
3. 14 to 21 days 419510 2019-10-02T00:00:02.023Z 2019-10-08T23:59:58.063Z