topic Trouble parsing JSON coming via REST in Getting Data In

Trouble parsing JSON coming via REST

chrzz — Sun, 04 Mar 2018 15:48:58 GMT

Hi!

I have a JSON that looks like this (the repeting elements have been removed):

  {  
       "data":{  
          "viewer":{  
             "homes":[  
                {  
                   "currentSubscription":{  
                      "status":"running",
                      "priceInfo":{  
                         "range":{  
                            "nodes":[  
                               {  
                                  "total":0.5375,
                                  "energy":0.43,
                                  "tax":0.1075,
                                  "startsAt":"2018-02-28T09:00:00+01:00",
                                  "currency":"NOK"
                               },
                               {  
                                  "total":0.5371,
                                  "energy":0.4297,
                                  "tax":0.1074,
                                  "startsAt":"2018-02-28T10:00:00+01:00",
                                  "currency":"NOK"
                               },
                               {  
                                  "total":0.526,
                                  "energy":0.4208,
                                  "tax":0.1052,
                                  "startsAt":"2018-02-28T11:00:00+01:00",
                                  "currency":"NOK"
                               }
                            ]
                         }
                      }
                   }
                }
             ]
          }
       }
    }

I'm really having problems getting this JSON parsed when the data comes into SPLUNK via REST.

I'm pulling the data once every day, so I want the "startsAt" to be the time for which I want to be the _time field. I want to have this done as a sourcetype.

In general, the data I want out of this JSON is like this:

            _time                   total   energy  tax currency
event   2018-02-28T09:00:00+01:00   0.5375  0.43    0.1075  NOK
event   2018-02-28T10:00:00+01:00   0.5371  0.4297  0.1074  NOK
event   2018-02-28T11:00:00+01:00   0.526   0.4208  0.1052  NOK

Re: Trouble parsing JSON coming via REST

niketn — Sun, 04 Mar 2018 19:31:12 GMT

@chrzz, following is a run anywhere search based on the sample data provided:

| makeresults
| eval _raw="{  
    \"data\":{  
       \"viewer\":{  
          \"homes\":[  
             {  
                \"currentSubscription\":{  
                   \"status\":\"running\",
                   \"priceInfo\":{  
                      \"range\":{  
                         \"nodes\":[  
                            {  
                               \"total\":0.5375,
                               \"energy\":0.43,
                               \"tax\":0.1075,
                               \"startsAt\":\"2018-02-28T09:00:00+01:00\",
                               \"currency\":\"NOK\"
                            },
                            {  
                               \"total\":0.5371,
                               \"energy\":0.4297,
                               \"tax\":0.1074,
                               \"startsAt\":\"2018-02-28T10:00:00+01:00\",
                               \"currency\":\"NOK\"
                            },
                            {  
                               \"total\":0.526,
                               \"energy\":0.4208,
                               \"tax\":0.1052,
                               \"startsAt\":\"2018-02-28T11:00:00+01:00\",
                               \"currency\":\"NOK\"
                            }
                         ]
                      }
                   }
                }
             }
          ]
       }
    }
 }"
 | spath
 | rename data.viewer.homes{}.currentSubscription.priceInfo.range.nodes{}.* as *
 | eval data=mvzip(startsAt,mvzip(energy,mvzip(tax,mvzip(total,currency))))
 | fields - _*
 | fields data
 | mvexpand data
 | makemv data delim=","
 | eval _time=strptime(mvindex(data,0),"%Y-%m-%dT%H:%M:%S"), energy=mvindex(data,1), tax=mvindex(data,2), total=mvindex(data,3), currency=mvindex(data,4)
 | fields - data

Re: Trouble parsing JSON coming via REST

chrzz — Mon, 05 Mar 2018 08:19:32 GMT

Thanks! But, this is only after the data have been indexed. I want this to be done at index-time in a sourcetype. How can I write the correct way to get the data into splunk the correct way?