Splunk Search
Highlighted

How to extract all occurrences of a field from JSON with a repeating structure?

New Member

I have the following JSON and am looking to extract all of the occurrences of Lat and Long. I have used a macro defined as

rex "(?<json_field>{[^}]+})" 

per previous answer http://answers.splunk.com/answers/79029/part-1-how-to-extract-a-json-portion-of-an-event-then-use-sp...
However, when I try to search with:

... | `my_macro`| mvexpand json_field | spath input=json_field | table   GpsUpdateRequest.GPSUpdateGroup{}.Lat GpsUpdateRequest.GPSUpdateGroup{}.Long

only the first occurrence is returned. How can I get all occurrences? Also, is there a way to get only the first and last as well?

{"GpsUpdateRequest":
 {"StartLocation":"ABC","UserId":"55555","DeviceId":"1234567890123","TripNo":0,"GPSUpdateGroup":
  [{"Lat":45.55714442,"Long":-122.70291961,"Speed":5,"Elevation":-16.4,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:24:37-00:00"},
   {"Lat":45.55657249,"Long":-122.70304847,"Speed":5,"Elevation":-59.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:25:43-00:00"},
   {"Lat":45.5562907,"Long":-122.69994322,"Speed":5,"Elevation":-52.5,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:26:04-00:00"},
   {"Lat":45.5559368,"Long":-122.6947226,"Speed":9,"Elevation":-32.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:26:35-00:00"},
   {"Lat":45.55589454,"Long":-122.68949185,"Speed":4,"Elevation":13.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:26:59-00:00"},
   {"Lat":45.55594183,"Long":-122.68454893,"Speed":2,"Elevation":45.9,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:27:25-00:00"},
   {"Lat":45.55605582,"Long":-122.68327135,"Speed":10,"Elevation":75.5,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:27:39-00:00"},
   {"Lat":45.55613833,"Long":-122.68113534,"Speed":5,"Elevation":118.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:28:22-00:00"},
   {"Lat":45.55606329,"Long":-122.678692,"Speed":6,"Elevation":137.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:28:37-00:00"},
   {"Lat":45.55590463,"Long":-122.67817822,"Speed":6,"Elevation":137.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:28:41-00:00"},
   {"Lat":45.55531425,"Long":-122.67833457,"Speed":7,"Elevation":108.3,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:29:06-00:00"},
   {"Lat":45.55894644,"Long":-122.67843673,"Speed":3,"Elevation":105.0,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:29:31-00:00"},
   {"Lat":45.56637296,"Long":-122.67849605,"Speed":5,"Elevation":108.3,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:30:07-00:00"},
   {"Lat":45.57367101,"Long":-122.67855006,"Speed":5,"Elevation":78.7,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:30:40-00:00"},
   {"Lat":45.5811205,"Long":-122.67842766,"Speed":5,"Elevation":39.4,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:31:14-00:00"},
   {"Lat":45.5841273,"Long":-122.67899733,"Speed":4,"Elevation":32.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:31:28-00:00"},
   {"Lat":45.59111136,"Long":-122.6821438,"Speed":5,"Elevation":-13.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:32:01-00:00"},
   {"Lat":45.59615269,"Long":-122.68411631,"Speed":6,"Elevation":-29.5,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:32:26-00:00"},
   {"Lat":45.59988899,"Long":-122.68355645,"Speed":3,"Elevation":-23.0,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:32:47-00:00"}
  ]
 }
}
0 Karma
Highlighted

Re: How to extract all occurrences of a field from JSON with a repeating structure?

Motivator

Hi Roshanon,

Check this one, The below search will help you to extract all the fields in the JSON, NOTE: This will work only all the fields are presents in the events.

index=blah | table _raw | spath | rename GpsUpdateRequest.* as * GpsUpdateRequest.GPSUpdateGroup{}.* as * | eval first_Lat=mvindex(Lat,0) | eval first_Long=mvindex(Long,0) | eval end_lat=mvindex(Lat,mvcount(Lat)-1) | eval end_long=mvindex(Long,mvcount(Long)-1)  | eval temp=mvzip(Elevation,mvzip(EventTimestamp,mvzip(Lat, mvzip(Long,mvzip(NodeSeq, Speed,"###"),"###"),"###"),"###"),"###") | mvexpand temp  | rex field=temp "(?<Elevation>.*)###(?<EventTimestamp>.*)###(?<Lat>.*)###(?<Long>.*)###(?<NodeSeq>.*)###(?<Speed>.*)" | fields - _raw, temp

I understand that you want to keep the first and last (lat and long) in each fields, if that is not the case remove the blow code,

| eval first_Lat=mvindex(Lat,0) | eval first_Long=mvindex(Long,0) | eval end_lat=mvindex(Lat,mvcount(Lat)-1) | eval end_long=mvindex(Long,mvcount(Long)-1)

Hope this will helps you.

Thanks,
V

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.