Oct 22 2019
06:22 AM
- last edited on
Apr 08 2022
10:10 AM
by
TechCommunityAP
Oct 22 2019
06:22 AM
- last edited on
Apr 08 2022
10:10 AM
by
TechCommunityAP
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:
Oct 23 2019 03:25 AM
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 |