Home

Last 3 values for multiple categories

Scott Allison
Contributor

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:

 

TimeGeneratedSiteNameMonitorNameAvailability
2018-10-05T06:44:37.353HoustonTest10
2018-10-05T06:34:37.353HoustonTest11
2018-10-05T06:24:37.353HoustonTest10
2018-10-05T06:31:00.000HoustonTest21
2018-10-05T06:21:00.000HoustonTest21
2018-10-05T06:11:00.000HoustonTest21
2018-10-05T06:51:00.000HoustonTest30
2018-10-05T06:41:00.000HoustonTest30
2018-10-05T06:31:00.000HoustonTest31
2018-10-05T06:38:00.000Los AngelesTest11
2018-10-05T06:28:00.000Los AngelesTest11
2018-10-05T06:18:00.000Los AngelesTest11
2018-10-05T06:55:00.000Los AngelesTest20
2018-10-05T06:45:00.000Los AngelesTest20
2018-10-05T06:35:00.000Los AngelesTest21

 

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!

3 Replies

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 SiteNameMonitorName

 

Hope it helps,

Meir :>

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

 

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