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 nee...
azure monitor
kusto
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