Splunk Search

Alternative/Compliment to stats first()

duesser
Path Finder

 

| makeresults
| eval _raw="id;x;y;z;k
a;1;;;
a;;1;;
a;;;1;
a;2;;;
a;;2;;
a;;;;1
b;1;;;
b;;1;;
b;;;1;
b;2;;;
b;;2;;
b;;;;1
a;;1;;
a;;;1;
a;2;;;
a;;2;;
a;;;;1
b;1;;;
b;;1;;
b;;;1;
b;2;;;
b;;2;;
b;;;;1"
| multikv forceheader=1
| fields id x y z k
| table id x y z k
| stats first(*) AS * BY id

 

I have date of the following form, where the usual variables Z and K (might) have multiple measurements that are not unique, so I use "| stats first()" to aggregate them to the ID. However there are variables X and Y that do contain multiple unique values (which might also repeat). In the end I would like to obtain a table like so:

 

id	x1	x2	y1	y2	z	k
a	1	2	1	2	1	1
b	1	2	1	2	1	1

 

Where (ideally) the fields of X and Y are numbered for each unique value (NOT the value in the field) so that if 3 unique values are in the data it would yield X1,2,3.

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| table id x y z k
| stats list(x) as x list(y) as y first(z) AS z first(k) as k BY id
| eval x=mvdedup(x)
| eval y=mvdedup(y)
| eval xrange=mvrange(0,mvcount(x))
| mvexpand xrange
| eval name="x".(xrange+1)
| eval {name}=mvindex(x,xrange)
| eval yrange=mvrange(0,mvcount(y))
| mvexpand yrange
| eval name="y".(yrange+1)
| eval {name}=mvindex(y,yrange)
| fields - x xrange y yrange name
| stats values(*) as * by id

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| table id x y z k
| stats list(x) as x list(y) as y first(z) AS z first(k) as k BY id
| eval x=mvdedup(x)
| eval y=mvdedup(y)
| eval xrange=mvrange(0,mvcount(x))
| mvexpand xrange
| eval name="x".(xrange+1)
| eval {name}=mvindex(x,xrange)
| eval yrange=mvrange(0,mvcount(y))
| mvexpand yrange
| eval name="y".(yrange+1)
| eval {name}=mvindex(y,yrange)
| fields - x xrange y yrange name
| stats values(*) as * by id

duesser
Path Finder

Great solution - I did not know about mvindex and mvrange! They seem like a usefull couple.

instead of list -> mvdedup you can just use values.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I went for list and mvdedup to preserve the order, if the order is not significant, then yes, values is just as good.

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...