Splunk Search

How to get full row based on max

CarmineCalo
Path Finder

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

Tags (2)
0 Karma
1 Solution

elliotproebstel
Champion

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?

View solution in original post

0 Karma

elliotproebstel
Champion

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?

0 Karma

CarmineCalo
Path Finder

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 🙂

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...