Forum Discussion

gauteweb's avatar
gauteweb
Copper Contributor
Oct 22, 2019

Compare Kusto results from three timespans

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(14d) and timestamp > ago(21d)
| extend DeviceId_ = tostring(parse_json(tostring(customDimensions.Properties)).DeviceId) 
| summarize dcount(DeviceId_)

customEvents
| where timestamp < ago(7d) and 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_)

 

 

azure monitor
kusto

1 Reply

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Oct 23, 2019

    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

Resources