for each column return max value and row key value

[wsabry]

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.

[to4kawa]

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

[adonio · Accepted solution]

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]

cool xyseries

[wsabry]

Thanks for your reply, it works.