Last 3 values for multiple categories

[Scott Allison]

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:

• TimeGenerated
• MonitorName
• SiteName
• Availability

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!

[#2]

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 :>

[#3]

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

[#4]

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.

let mydata = datatable(TimeGenerated:datetime, SiteName:string, MonitorName:string, Availability:int) // this is just an “inline” table for demonstration purposes

[

datetime(2018-10-05 06:54), "Houston", "Test1", 1,

datetime(2018-10-05 06:44), "Houston", "Test1", 0,

datetime(2018-10-05 06:34), "Houston", "Test1", 1,

datetime(2018-10-05 06:24), "Houston", "Test1", 0,

datetime(2018-10-05 06:14), "Houston", "Test1", 0,

datetime(2018-10-05 06:04), "Houston", "Test1", 0,

datetime(2018-10-05 06:21), "Houston", "Test2", 1,

datetime(2018-10-05 06:11), "Houston", "Test2", 1,

datetime(2018-10-05 06:01), "Houston", "Test2", 1,

datetime(2018-10-05 05:51), "Houston", "Test2", 0,

datetime(2018-10-05 05:41), "Houston", "Test2", 0,

datetime(2018-10-05 06:51), "Houston", "Test3", 0,

datetime(2018-10-05 06:41), "Houston", "Test3", 0,

datetime(2018-10-05 06:31), "Houston", "Test3", 1,

datetime(2018-10-05 06:21), "Houston", "Test3", 0,

datetime(2018-10-05 06:11), "Houston", "Test3", 0,

datetime(2018-10-05 06:38), "Los Angeles", "Test1", 1,

datetime(2018-10-05 06:28), "Los Angeles", "Test1", 0,

datetime(2018-10-05 06:18), "Los Angeles", "Test1", 1,

datetime(2018-10-05 06:08), "Los Angeles", "Test1", 0,

datetime(2018-10-05 06:55), "Los Angeles", "Test2", 0,

datetime(2018-10-05 06:45), "Los Angeles", "Test2", 0,

datetime(2018-10-05 06:35), "Los Angeles", "Test2", 1,

];

let _topCount=3;

mydata

| top-nested of SiteName by max(1), top-nested of MonitorName by max(1), top-nested _topCount of TimeGenerated by max(TimeGenerated), top-nested of Availability by max(1)

| project-away aggregated_MonitorName, aggregated_SiteName, aggregated_TimeGenerated, aggregated_Availability

| order by SiteName asc, MonitorName asc, TimeGenerated desc