Forum Discussion

ScottAllison's avatar
ScottAllison
Iron Contributor
Oct 05, 2018

Last 3 values for multiple categories

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!

Azure Log Analytics
azure monitor
  • Emilian Ertel's avatar
    Emilian Ertel
    Copper Contributor
    Oct 11, 2018

    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

     

  • Patrick Naughton's avatar
    Patrick Naughton
    Brass Contributor
    Oct 08, 2018
    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

     

  • Meir_Mendelovich's avatar
    Meir_Mendelovich
    Icon for Microsoft rankMicrosoft
    Oct 07, 2018

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

Resources