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.
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
| 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.
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
cool xyseries
Thanks for your reply, it works.