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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...