Oct 05 2018
05:24 AM
- last edited on
Apr 07 2022
05:30 PM
by
TechCommunityAP
Oct 05 2018
05:24 AM
- last edited on
Apr 07 2022
05:30 PM
by
TechCommunityAP
All, I'm trying to get the last three values of a dataset by a particular category, and I am having trouble figuring out the best way to do this.
I have the following columns:
What I need is the last (most recent) 3 values (Availability) by SiteName and MonitorName. I want my result to look something like this:
TimeGenerated | SiteName | MonitorName | Availability |
2018-10-05T06:44:37.353 | Houston | Test1 | 0 |
2018-10-05T06:34:37.353 | Houston | Test1 | 1 |
2018-10-05T06:24:37.353 | Houston | Test1 | 0 |
2018-10-05T06:31:00.000 | Houston | Test2 | 1 |
2018-10-05T06:21:00.000 | Houston | Test2 | 1 |
2018-10-05T06:11:00.000 | Houston | Test2 | 1 |
2018-10-05T06:51:00.000 | Houston | Test3 | 0 |
2018-10-05T06:41:00.000 | Houston | Test3 | 0 |
2018-10-05T06:31:00.000 | Houston | Test3 | 1 |
2018-10-05T06:38:00.000 | Los Angeles | Test1 | 1 |
2018-10-05T06:28:00.000 | Los Angeles | Test1 | 1 |
2018-10-05T06:18:00.000 | Los Angeles | Test1 | 1 |
2018-10-05T06:55:00.000 | Los Angeles | Test2 | 0 |
2018-10-05T06:45:00.000 | Los Angeles | Test2 | 0 |
2018-10-05T06:35:00.000 | Los Angeles | Test2 | 1 |
I tried using "top-nested" to get a result like this, but can't seem to get it right. Does anyone have any thoughts?
Thanks!
Oct 07 2018 03:45 AM
Hi Scott,
We have the arg_max() function but it returns only a single raw per each aggregation value.
For example:
… | summarize arg_max(TimeGenerated, *) by SiteName, MonitorName
Hope it helps,
Meir :>
Oct 08 2018 10:58 AM - edited Oct 08 2018 11:00 AM
let YourDataTable = datatable(TimeGenerated: datetime, SiteName: string, MonitorName: string, Availability: int) [
datetime("2018-10-05T06:44:37.353"), "Houston", "Test1", 0,
datetime("2018-10-05T06:34:37.353"), "Houston", "Test1", 1,
datetime("2018-10-05T06:24:37.353"), "Houston", "Test1", 0,
datetime("2018-10-05T05:00:00.000"), "Houston", "Test1", 999,
datetime("2018-10-05T06:31:00.000"), "Houston", "Test2", 1,
datetime("2018-10-05T06:21:00.000"), "Houston", "Test2", 1,
datetime("2018-10-05T06:11:00.000"), "Houston", "Test2", 1,
datetime("2018-10-05T05:00:00.000"), "Houston", "Test2", 999,
datetime("2018-10-05T06:51:00.000"), "Houston", "Test3", 0,
datetime("2018-10-05T06:41:00.000"), "Houston", "Test3", 0,
datetime("2018-10-05T06:31:00.000"), "Houston", "Test3", 1,
datetime("2018-10-05T05:00:00.000"), "Houston", "Test3", 999,
datetime("2018-10-05T06:38:00.000"), "Los Angeles", "Test1", 1,
datetime("2018-10-05T06:28:00.000"), "Los Angeles", "Test1", 1,
datetime("2018-10-05T06:18:00.000"), "Los Angeles", "Test1", 1,
datetime("2018-10-05T05:00:00.000"), "Los Angeles", "Test1", 999,
datetime("2018-10-05T06:55:00.000"), "Los Angeles", "Test2", 0,
datetime("2018-10-05T06:45:00.000"), "Los Angeles", "Test2", 0,
datetime("2018-10-05T06:35:00.000"), "Los Angeles", "Test2", 1,
datetime("2018-10-05T05:00:00.000"), "Los Angeles", "Test2", 999
];
let TopN = 3;
YourDataTable
| order by TimeGenerated desc
| extend row = row_number()
| as T
| join kind=leftouter (
T | summarize minRow = min(row) by SiteName, MonitorName
) on SiteName, MonitorName
| where row < minRow + TopN
| project TimeGenerated, SiteName, MonitorName, Availability
| order by SiteName asc, MonitorName asc, TimeGenerated desc
Oct 11 2018 02:01 AM
We had a similar problem and after some help from real pros (Thanks Yoni !) the query turned out to be pretty simple using the top-nested command (see below).
Hope this helps.