Comparing Latency from a Subnet to 3 different destinations and output the best option.

[rjgreg]

I am testing network latency from various subnets to 3 different VCenters.  The output gives me 3 results per subnet IP. How do I have Splunk see the values per subnet and output the best of the three options?   I am fairly new to this and the tutorial got me this far.  Any constructive help would be appreciated. Current Search is below.  

index="wineventlog" host="mgmt" source="wineventlog:application" "EventCode=999" "SourceName=NetworkLatencyCheck"

| sort 1 - _time

| mvexpand SubnetSourceLatencyDestinationSiteLocationStatus

| rex Field=SubnetSourceLatencyDestinationSiteLocationStatus  "^(?<Subnet>.*),\$(?<Source>.*),\$(?<Latency>.*),\$(?<Destination>.*),\$(?<Site>.*),\$(?<Location>.*),\$(?<Status>.*)"

| regex Source="(\d{1,3}\.(\d{1,3}\.(\d{1,3}\.(\d{1,3})"

| table Subnet Latency Destination

***Example Output***

Subnet                  Latency          Destination

192.10.10               152.75            a08-vcenter

192.10.10                87                    a05-vcenter

192.10.10                8                      a03-vcenter

192.1.1                    25                     a08-vcenter

192.1.1                   13                      a05-vcenter

192.1.1                   48                      a03-vcenter

[richgalloway]

The stats command can select the "best" option.

index="wineventlog" host="mgmt" source="wineventlog:application" "EventCode=999" "SourceName=NetworkLatencyCheck"
| sort 1 - _time
| mvexpand SubnetSourceLatencyDestinationSiteLocationStatus
| rex Field=SubnetSourceLatencyDestinationSiteLocationStatus  "^(?<Subnet>.*),\$(?<Source>.*),\$(?<Latency>.*),\$(?<Destination>.*),\$(?<Site>.*),\$(?<Location>.*),\$(?<Status>.*)"
| regex Source="(\d{1,3}\.(\d{1,3}\.(\d{1,3}\.(\d{1,3})"
| stats min(Latency) as Latency by Subnet, Destination
| table Subnet Latency Destination

[rjgreg]

@richgalloway  Thank you.  The output for the stats command organized the subnets from fastest to slowest, which is a big help. Do you know of a way to drop the slower two destinations?  The chart output I am looking for will list the fastest connection for each subnet and exclude the other two  destinations for each.  

[richgalloway]

While you can use the head or sort commands to limit the number of results, I don't know of a way to show all except the last n results.  Perhaps you could count the number of results, subtract two and pass the result to head.

[rjgreg]

@richgalloway  I'm still very new to writing Splunk commands.  How would the head command look?  In the current table, I have a total of 201 results.  There are three destinations, so each subnet appears three times in the table, like the example below.  Is it possible to use the head command per subnet to end up with 67 results (1/3rd of the results), which would be comprised of the best connection for each subnet? Thank you for your help 

***Example Output Before***

Subnet                  Latency          Destination

192.10.10                8                      a03-vcenter

192.10.10                87                    a05-vcenter

192.10.10               152.75            a08-vcenter

192.1.1                   13                      a05-vcenter

192.1.1                    25                     a08-vcenter

192.1.1                   48                      a03-vcenter

***Example of desired output***

Subnet                  Latency          Destination

192.10.10                8                      a03-vcenter

192.1.1                   13                      a05-vcenter

[richgalloway]

The head command is similar to the same command in Linux in that it displays the first few lines of the given input.  It's not smart enough to count within groups.

Another option for you is the stats command.

... | stats first(Latency) as Latency, first(Destination) as Destination by Subnet

[rjgreg · Accepted solution]

@richgalloway   I tried to send this yesterday, but it seems to have been lost in the ether.  The |stats command you gave me didn't output anything in the statistics  view.  I figured out the problem and solved it with the | dedup command you can see in the complete search below.  I added a few fields for the table as well, for tracking.  Thank you for your help.  Not sure I would have found the solution without your help.

index="wineventlog" host="mgmt" source="wineventlog:application" "EventCode=999" "SourceName=NetworkLatencyCheck"
| sort 1 - _time
| mvexpand SubnetSourceLatencyDestinationSiteLocationStatus
| rex Field=SubnetSourceLatencyDestinationSiteLocationStatus  "^(?<Subnet>.*),\$(?<Source>.*),\$(?<Latency>.*),\$(?<Destination>.*),\$(?<Site>.*),\$(?<Location>.*),\$(?<Status>.*)"
| regex Source="(\d{1,3}\.(\d{1,3}\.(\d{1,3}\.(\d{1,3})"
| stats min(Latency) as latency by Subnet, Destination, Location, _time
| dedup Subnet sortby +Latency
| sort Subnet
| table Subnet Latency Destination Location _time