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

[christopheryu]

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:

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 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| &nbsp&nbsp&nbsp&nbsp&nbsp| &nbsp&nbsp&nbsp
CCC&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|010| &nbsp&nbsp&nbsp

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!

[woodcock]

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

[grittonc]

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.

[christopheryu]

thanks but your suggestion produced no results.

[grittonc]

Did you | rename values(RouterA) as column?

[christopheryu]

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)

[christopheryu]

yes I did.