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!

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