Compare Kusto results from three timespans

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

@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