Splunk Search

for each column return max value and row key value

wsabry
New Member

Hello,

I have SPL search that returns output in the following format:

Device K1 K2 K3
A x1 y1 z1
B x2 y2 z2
C x3 y3 z3

I would like to generate table with max value of each column and row key value (Device in my example above), so the output should be in the following format:
Key Max Device
k1 x1 A
k2 y3 C
k3 z3 C

I can find the max value of each column using fieldsummary for example, but then the device id is missing.
How can I do that, thanks in advance.

Tags (2)
0 Karma
1 Solution

adonio
Ultra Champion

hello there,

here is a clumsy solution, try the following search anywhere.
I bet there are better ways, its just been a very long day 🙂

| makeresults count=1
| eval data="A 1 5 10;;;B 2 11 8;;;C 3 23 4;;;D 91 2 21;;;E 7 1 200;;;F 74 22 11"
| makemv delim=";;;" data 
| mvexpand data
| rex field=data "(?<device>[^\s]+)\s(?<k1>[^\s]+)\s(?<k2>[^\s]+)\s(?<k3>[^\s]+)"
| table device k*
| rename COMMENT as "the above generates data below is the solution" 
| eval no_op = " "
| xyseries no_op device k1 k2 k3
| transpose
| rename column as base "row 1" as values
| rex field=base "(?<key>[^\:]+)\:\s+(?<device>.*+)"
| eventstats max(values) as max_values by key
| where max_values = values

hope it helps

View solution in original post

to4kawa
Ultra Champion
| makeresults 
| eval _raw="device k1  k2  k3
A   1   5   10
B   2   11  8
C   3   23  4
D   91  2   21
E   7   1   200
F   74  22  11"
| multikv forceheader=1 
| table device k*
| rename COMMENT as "the above generates data below is the solution"
| stats list(*) as * 
| untable device Key Max 
| eval counter=mvfind(split(Max," "),max(split(Max," "))) 
| eval device=mvindex(device,counter), Max=mvindex(split(Max," "),counter) 
| table Key device Max

Hi @wsabry
Here is another way.

0 Karma

adonio
Ultra Champion

hello there,

here is a clumsy solution, try the following search anywhere.
I bet there are better ways, its just been a very long day 🙂

| makeresults count=1
| eval data="A 1 5 10;;;B 2 11 8;;;C 3 23 4;;;D 91 2 21;;;E 7 1 200;;;F 74 22 11"
| makemv delim=";;;" data 
| mvexpand data
| rex field=data "(?<device>[^\s]+)\s(?<k1>[^\s]+)\s(?<k2>[^\s]+)\s(?<k3>[^\s]+)"
| table device k*
| rename COMMENT as "the above generates data below is the solution" 
| eval no_op = " "
| xyseries no_op device k1 k2 k3
| transpose
| rename column as base "row 1" as values
| rex field=base "(?<key>[^\:]+)\:\s+(?<device>.*+)"
| eventstats max(values) as max_values by key
| where max_values = values

hope it helps

to4kawa
Ultra Champion

cool xyseries

wsabry
New Member

Thanks for your reply, it works.

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...