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?
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
Take a look at the updated code on my post. I believe it is what you are looking for.
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 __/\_
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
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"?