Splunk 6.3 - Correlate data from multidimensional JSON-encoded events

[edgenuity]

Hi,

We have an index populated with MTR (mytraceroute) data from remote hosts. We are looking for a way to parse this information and present a visualization (or at least a table) showing network hops, the number of times they show up in the data, and the average packet loss for each hop.

{
    "status": "success",
    "hops": [
        {
            "host": "192.168.1.1",
            "loss": "0.0%",
            "avg": "0.3"
        },
        {
            "host": "network-hop-1",
            "loss": "0.0%",
            "avg": "33.2"
        },
        {
            "host": "network-hop-2",
            "loss": "0.0%",
            "avg": "23.2"
        },
        {
            "host": "network-hop-3",
            "loss": "0.0%",
            "avg": "23.6"
        },
        {
            "host": "network-hop-4",
            "loss": "40.0%",
            "avg": "25.3"
        },
        {
            "host": "network-hop-5",
            "loss": "0.0%",
            "avg": "25.2"
        },
        {
            "host": "network-hop-6",
            "loss": "0.0%",
            "avg": "25.5"
        }
    ],
    "totalhops": "7",
    "appliance": "HOSTNAME"
}

We need a table that shows --

hop, [number of events containing this hop], [average loss % to this hop across all events containing it], [average time (avg) to this hop]

Any idea how to do this?

[somesoni2 · Accepted solution]

Assuming this json format data is properly parsed by Splunk (props.conf is setup correctly to extract fields), then try something like this

your base search | table host{}* | rename host{}.* as * | eval temp=mvzip(host,mvzip(loss,avg,"#"),"#") | table temp | mvexpand temp | rex field=temp "(?<hop>.*)#(?<loss>.*)%#(?<time>.*)" | stats count, avg(loss) as Avg_Loss_% avg(time) as Avg_Time by hop

[edgenuity]

Thank you, there was one minor mistake (should be ' table hops{}* | rename hops{}.* as * ' ... but otherwise this is perfect. Thank you!

[edgenuity]

That gives no statistics results at all. Sorry to be so unhelpful with my reply but I'm not understanding how the search string is intended to work.