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-spath-to-extract-key-value-pairs.html
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"}
]
}
}
... View more