topic Re: Can I count multi-value fields? in Splunk Search

Can I count multi-value fields?

i111040d — Tue, 29 Sep 2020 13:11:16 GMT

My event(NOT table):

_time,id,eth_src,eth_dst
090000,1,u,v
090001,1,w,x
090002,1,y,z
090003,2,u,v
090004,2,w,x
090005,3,u,v
090006,4,u,v
090007,4,w,x

The table I want to create
([~] means multi-value field.):

route,count,sparkline(count)
[u>v,w>x,y>z],1,sparkline
[u>v,w>x],2,sparkline
[u>v],1,sparkline

How can I create the table?

Re: Can I count multi-value fields?

gcusello — Wed, 08 Mar 2017 09:18:50 GMT

Hi i111040d,
I'm not sure about your need because I see in your example that you have the same values u>v in all the results, every way, try something like this:

 your_search
| eval route=case(eth_src="u" AND eth_dst="v", "[u>v]",eth_src="w" AND eth_dst="x", "[w>x]",eth_src="y" AND eth_dst="z", "[y>z]",............)
| stats sparkline count by route

Obviously, you have to build your eval by your needs.

Bye.
Giuseppe

Re: Can I count multi-value fields?

i111040d — Thu, 09 Mar 2017 01:36:11 GMT

Hi, cusello.
Thanks for your answering.
But sorry my bad.
What I wanted is slightly different.
So I rewrite clarity.

Event:
_time=090000 id=1 src=w dst=x
_time=090001 id=1 src=y dst=z
_time=090002 id=2 src=w dst=x
_time=090003 id=3 src=w dst=x
_time=090004 id=3 src=y dst=z

First search:
Index=*
|eval route=src.">".dst
|stats last(_time) as _time values(route) as route by id

First result(table):
_time,id,route
090001,1,[w>x,y>z]
090002,2,w>x
090004,3,[w>x,y>z]

Next search:
|stats count sparkline by route

Ideal result(table):
route,count,sparkline
[w>x,y>z],2,sparkline
w>x,1,sparkline

Actual result(table):
route,count,sparkline(failed)
w>x,3,sparkline(failed)
y>z,2,sparkline(failed)

How can I get the "Ideal result"?

Re: Can I count multi-value fields?

DalJeanis — Thu, 09 Mar 2017 05:22:19 GMT

This inputs your example data.

  | makeresults 
  | eval mydata="090000,1,u,v 090001,1,w,x 090002,1,y,z 090003,2,u,v 090004,2,w,x 090005,3,u,v 090006,4,u,v 090007,4,w,x" 
  | makemv mydata | mvexpand mydata | makemv delim="," mydata 
  | eval time=mvindex(mydata,0),id=mvindex(mydata,1),eth_src=mvindex(mydata,2),eth_dst=mvindex(mydata,3)
  | table time id eth_src eth_dst

This translates the data to the a>b format, strips the records to only the time, leg and id, then puts the legs together into a single multivalue field ("legs") by id. Then it uses mvjoin to create the requested format, and finally, produce the count of each route, with sparkline.

| eval leg = eth_src.">".eth_dst
| table time id leg
| eventstats min(time) as mintime, list(leg) as legs by id
| eval route = "[".mvjoin(legs,",")."]"
| where time=mintime
| eval _time = time
| eventstats count as routecount by route
| eval route=route.":".routecount
| table _time route routecount
| chart count sparkline by route

Resulting in

route         count   sparkline
[u>v,w>x,y>z]    1    \____
[u>v,w>x]        2    _/\_/
[u>v]            1    __/\_

Re: Can I count multi-value fields?

DalJeanis — Thu, 09 Mar 2017 15:16:03 GMT

Take a look at the updated code on my post. I believe it is what you are looking for.

Re: Can I count multi-value fields?

somesoni2 — Thu, 09 Mar 2017 15:21:22 GMT

Give this a try

Index=*
|eval route=src.">".dst
|stats last(_time) as _time values(route) as route by id delim=","
| nomv route
|stats count sparkline by route