How to get full row based on max

[CarmineCalo]

Splunkers!

I need to solve this problem.
Basically, starting from a Service Catalogue (having the same AppID linked to more Business Services having potentially different Operational WIndows), i need to select the max Operational Window required (both in term of number and ID).

Starting from an inputlookup (so no events as input) i need to select a full row (all columns) based on the max value in a specific column.

Current code

| inputlookup DOM_ServiceCatalogue
| lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeekAva, Cluster_Ava_Code as Cluster_Ava_Code

| stats max(ReqWeekAva) as ReqWeekAva, values(Cluster_Ava_Code) by CI

But values function creates a MV fields, while i just need the "Cluster_Ava_Code" corresponding to the max value selected by stats function.
Additionally, It might happen to have multiple rows in the Service Catalogue having the same max value for different services supported by the same AppID....

How can i do it?

Tks!
Carmine

[elliotproebstel · Accepted solution]

Instead of using stats on line 4, how about instead adding a field called max_ReqWeekAva to every event, which tracks the max value of ReqWeekAva by CI value, and then retaining only the events where ReqWeekAva=max_ReqWeekAva? That would look like this:

| inputlookup DOM_ServiceCatalogue
| lookup AMAP_ReqAvailability Cluster_Availability as PrimaryWindows OUTPUTNEW ReqWeeklyAvailability as ReqWeekAva, Cluster_Ava_Code as Cluster_Ava_Code
| eventstats max(ReqWeekAva) as max_ReqWeekAva by CI
| where max_ReqWeekAva=ReqWeekAva
| stats max(ReqWeekAva) AS ReqWeekAva, max(Cluster_Ava_Code) AS Cluster_AVA_Code BY CI

I don't quite understand the second requirement in your post: "Additionally, It might happen to have multiple rows in the Service Catalogue having the same max value for different services supported by the same AppID...." Can you explain that part further?

[CarmineCalo]

Great, it works!

The second requirement was related to the need to avoid multivalue fields in the following case

CI ReqWeekAva Cluster_Ava_Code
App1 10 CAC1
App1 40 CAC2
App1 30 CAC3
App1 40 CAC2

I mean avoid output like

App1 40 CAC1
40 CAC1

But your solution it's fine, so no issue 🙂