topic Re: How to create a timechart plotting average values for nested JSON data? in Splunk Search

How to create a timechart plotting average values for nested JSON data?

paulwrussell — Sun, 22 May 2016 05:50:11 GMT

I am receiving JSON into Splunk in the following format. I'm trying to figure out how I can do searches to plot average values for this nested data. I need to be able to plot a line for each node over time, but I also need to be able to plot the average value for all nodes within a gateway over time. I don't know if I should be splitting up this data into multiple events as it comes into Splunk or whether my search should normalize all nodes to a common name as the id is in the data, and then try to split it up. Are there other options I haven't thought of yet?

The list of nodes is a dynamic list, so I can't hardcode these node ids.

gateway: "gateway1",
nodes: { 
     1002: { 
        id:  1002 
     } 
     11: {
         id:  11 
         value:  100
     } ,
     14: {
         id:  11 
         value:  120
     } 
}

Help is really appreciated.

Re: How to create a timechart plotting average values for nested JSON data?

sundareshr — Sun, 22 May 2016 16:16:20 GMT

Have you looked at spath?

http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Spath

Re: How to create a timechart plotting average values for nested JSON data?

paulwrussell — Sun, 22 May 2016 21:47:59 GMT

yes ive looked at it. i can turn into string and use rex to remove outside node ids. but not sure what to do after that.

Re: How to create a timechart plotting average values for nested JSON data?

sundareshr — Sun, 22 May 2016 22:24:32 GMT

Great. Use this to build your timechart, after you have extracted the fields. In this example, I am going to use rex (you may have to tweak the regex for your data)

.... | rex max_match=0 "id:\s(?<id>\s)" | rex max_match=0 "value:\s(?<value>\d+)" | eval z=mvzip(id, value) | mvexpand z | rex field=z "(?<id>\d+),(?<value>\d+) | timechart avg(value) as average by id

Re: How to create a timechart plotting average values for nested JSON data?

jkat54 — Mon, 23 May 2016 17:43:05 GMT

First off, the json is not valid. it only validates after I edit it to look like this:

{
    "gateway": "gateway1",
    "nodes": {
        "1002": {
            "id": 1002,
            "value": 100
        },
        "11": {
            "id": 11,
            "value": 100
        },
        "14": {
            "id": 14,
            "value": 120
        }
    }
}

Once i have it indexed with correct syntax, and sourcetype=_json, the following search works:

 ...| timechart avg(nodes.*.value) AS *.value | addtotals

You'll have to do a separate timechart for each gateway though... good use case for a dashboard with drop down selector, etc.

Re: How to create a timechart plotting average values for nested JSON data?

shawny2005 — Mon, 15 Aug 2016 23:51:27 GMT

spunk seriously needs some help in this place. being able to utilize nested jsons would be really useful. right now it kind sucks.