How do you assign values to a variable based on a name in a nested JSON?

[sboogaar]

We are working with the following JSON:

  {
    "datapoints": [
      {
        "name": "filesystem.inode.total1",
        "value": 15728640,
        "unit": "count",
        "timestamp": "2018-12-20T11:48:51.00524107Z",
        "tags": {
          "path": "/var/lib/docker"
        }
      },
      {
        "name": "filesystem.inode.total2",
        "value": 262144,
        "unit": "count",
        "timestamp": "2018-12-20T11:48:51.00524107Z",
        "tags": {
          "path": "/var/log/audit"
        }
      },
      {
        "name": "system.uptime",
        "value": 0,
        "unit": "count",
        "timestamp": "2018-12-20T11:48:51.006448776Z",
        "tags": {
          "interface": "docker0"
        }
      }
    ]
  }

What I want to do is to assign the values to a variable based on the name.

e.g.

eval test = [the value field where the sibbling is network.in.errors]

I don't need it to be automatically extracted. I just want to find a way on how to access the specific values based on the siblings name.

[woodcock]

You may find that this will not scale well for large datasets because of the INEXCUSABLY poor performance of the mvexpand command. If you examine your search log in your Job inspector you will see that it tells you that your search is autofinalizing because it ran out of RAM and returning partial results. It is also pretty inexcuseable that Splunk does not make this more clear to you without you having to dig for it. In any case, the solution is to avoid mvexpand:

|makeresults | eval time = "2018-12-20T15:02:52 2018-12-20T15:01:59 2018-12-20T15:01:00 2018-12-20T14:59:59 2018-12-20T14:58:52 2018-12-20T14:57:52 2018-12-20T14:56:51 2018-12-20T14:55:59 2018-12-20T14:54:49 2018-12-20T14:53:52 2018-12-20T14:52:53 2018-12-20T14:52:00"
| makemv time
| mvexpand time
| rename time AS _time
| eval _time = strptime(_time, "%Y-%m-%dT%H:%M:%S")
| eval host = "ics031120105 ics031120106 ics031120107"
| makemv host
| mvexpand host
| eval name = "a b c d e f g h i j system.uptime"
| eval value = "1 2 3 4 5 6 7 8 9 10 11"
| makemv name
| makemv value

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

Like this (for the specific approach):

| nomv name
| eval tester = mvindex(split(name, "system.uptime"), 0)
| eval position = (len(tester) - len(replace(tester, "\s", "")))
| eval uptime = mvindex(value, position)
| table _time, uptime, host 
| sort - 0 _time

Or like this for the general approach:

| eval _raw = mvzip(name,value,"=")
| fields - name value
| kv
| table _time, uptime, host 
| sort - 0 _time

[sboogaar]

I fixed it:

| spath 
| rename datapoints{}.name as name, datapoints{}.value as value 
| eval x=mvzip(name,value) 
| mvexpand x 
| eval x=split(x,",") 
| eval name=mvindex(x,0) 
| eval value=mvindex(x,1) 
| search name=system.uptime
| table _time, name, value, host

[kamlesh_vaghela]

Great @sboogaar

Accept your answer to close this question.

[kamlesh_vaghela · Accepted solution]

@sboogaar

Can you please try this?

YOUR_SEARCH
| spath datapoints{} output=datapoint 
| mvexpand datapoint 
| eval _raw=datapoint 
| kv | search name="network.in.errors"
| table name "tags.interface" "tags.path" timestamp unit value

My Sample Search:

| makeresults 
| eval _raw="{   \"datapoints\": [   {     \"name\": \"filesystem.inode.total1\",     \"value\": 15728640,     \"unit\": \"count\",     \"timestamp\": \"2018-12-20T11:48:51.00524107Z\",     \"tags\": {       \"path\": \"/var/lib/docker\"     }   },   {     \"name\": \"filesystem.inode.total2\",     \"value\": 262144,     \"unit\": \"count\",     \"timestamp\": \"2018-12-20T11:48:51.00524107Z\",     \"tags\": {       \"path\": \"/var/log/audit\"     }   },   {     \"name\": \"network.in.errors\",     \"value\": 0,     \"unit\": \"count\",     \"timestamp\": \"2018-12-20T11:48:51.006448776Z\",     \"tags\": {       \"interface\": \"docker0\"     }   } ]}" 
| spath datapoints{} output=datapoint 
| mvexpand datapoint 
| eval _raw=datapoint 
| kv | search name="network.in.errors"
| table name "tags.interface" "tags.path" timestamp unit value

Thanks

[sboogaar]

This solution seems to work better but I do not understand why.
with my own query when I try to view it by _time I cant see all the results I expect but with yours it does.

index=dcos sourcetype=dcos:node:metrics 
| spath datapoints{} output=datapoint 
 | mvexpand datapoint 
 | eval _raw=datapoint 
 | kv | search name="system.uptime"
 | table name  value _time

vs

index=dcos sourcetype=dcos:node:metrics 
| rename datapoints{}.name as name, datapoints{}.value as value 
| eval x=mvzip(name,value) 
| mvexpand x 
| eval x=split(x,",") 
| eval name=mvindex(x,0) 
| eval value=mvindex(x,1) 
| table _time, name, value, host 
| sort - _time 
| search name=system.uptime

gives different results

[kamlesh_vaghela]

Both searches returning the same result for my sample data. But the variation in events might impact the results. So below are my some inputs for queries.

Maybe my approach is different. Here I have converted all the data points into _raw So I can easily extract the name and other sibling fields easily without any data loss.

index=dcos sourcetype=dcos:node:metrics | spath datapoints{} output=datapoint will give me each multivalued datapoint JSON.

| mvexpand datapoint | eval _raw=datapoint will split multivalued datapoint to the single raw event.

and finally, kv command will extract JSON fields from each datapoint raw events. Even you can easily apply filters also.

Your search is also working fine but I have a confusion. In your search, you have used mvzip for combining the multivalued fields to keep sibling values together (see note 1). And then mvexpand, which will expand zipped value, which is used to extract name and value. This name field is used for searching "system.update" value.

Note 1: If any data have no value or no field then for that event mvzip will not return any value or will return an unexpected value. This might be a reason for missing data.

If my search is working properly and it is perfect as per your requirement then I suggest you use this.

[whrg]

Hello @sboogaar,

This search should give you the value for "network.in.errors":

your base search
| spath path=datapoints{} | mvexpand datapoints{} | spath input=datapoints{}
| search name="network.in.errors" | table name,value