topic Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa? in Splunk Search

xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

christopheryu — Mon, 15 Apr 2019 20:36:43 GMT

I have a search that calculates latency in a full-mesh network, where each router has a direct connection to all of the other routers in the network. Latency is bidirectional, in other words latency between AAA-CCC is the same as CCC-AAA. I am able to generate a table but only AAA-CCC latency is showing and CCC-AAA is blank (this can be reversed depending on how source and destination was setup). How can I have CCC-AAA to show the same value as AAA-CCC instead of blank?

search ...

| eval Route=RouterA."_".RouterZ 
| eventstats perc03(RTT) as RTT_03p, perc98(RTT) as RTT_98p BY Route
| where RTT >= RTT_03p and RTT <= RTT_98p 
| stats min(RTT) as Latency values(RouterA) values(RouterZ) by Route
| xyseries values(RouterA) values(RouterZ) Latency

This is what I am getting:

This is what I want to see:

values(RouterA) |AAA|BBB|CCC
AAA&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|027|012
BBB&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |027| &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|010
CCC&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |012|010| &nbsp&nbsp&nbsp

Thank you in advance!

Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

grittonc — Tue, 16 Apr 2019 01:03:51 GMT

I'm 99% sure there's a better way to do this.

Rename values(RouterA) as column.

Then:

your search 
| append [your search again|transpose 0 header_field=column]
|stats min(*) by column

Is outputting the main search to a lookup table an option? Then you could append it to a transpose of itself without running the search twice.

Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

christopheryu — Tue, 16 Apr 2019 16:28:51 GMT

thanks but your suggestion produced no results.

Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

grittonc — Tue, 16 Apr 2019 16:48:52 GMT

Did you | rename values(RouterA) as column?

Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

christopheryu — Tue, 16 Apr 2019 19:17:08 GMT

yes I did.

Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

christopheryu — Tue, 16 Apr 2019 20:21:46 GMT

Okay, there was a typo on my code. Your suggestion produced the same results as my original search and added a new row at the bottom labeled values(RouterA)

Re: xyseries in full mesh: How to have it fill values A to Z with Z to A or vice versa?

woodcock — Tue, 16 Apr 2019 21:15:39 GMT

You need the contingency command:
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Contingency