Getting Data In

Trouble parsing JSON coming via REST

chrzz
Observer

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
0 Karma

niketnilay
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

chrzz
Observer

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?

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...