Splunk REST API JSON Parsing

[thufirtan]

Hi, I am querying a REST API which returns JSON data. The JSON contains multiple results which I would like to break up into events. The metadata provides general information about the API call. Please advise on how I can do this? I am not interested in the metadata information but just want the results broken down as events to index. Thanks!

{"_metadata":[{"totalCount":9940102,"count":5,"limit":5,"offset":0,"status":"ok"}],"results":[{ "_id" : "1", "type":"apple"}, {"_id" : "2", "type":"banana"}, "_id":"3", "type":"apple"}]}

[rturk]

Hi Thufirtan,

Splunk recognises JSON natively. Taking your sample event (and putting an opening brace '{' before the last result declaration) I am able to search and report on the events:

Sample event:

{"_metadata":[{"totalCount":9940102,"count":5,"limit":5,"offset":0,"status":"ok"}],"results":[{ "_id" : "1", "type":"apple"}, {"_id" : "2", "type":"banana"}, {"_id":"3", "type":"apple"}]}

Search:

source="json_sample.txt" | rename results{}._id AS id, results{}.type AS type | stats count by type

Results:

Now let's say you have multiple events, and a timestamp in the returned response.

Sample Events:

{"_metadata":[{"timestamp":1377581422,"totalCount":9940102,"count":5,"limit":5,"offset":0,"status":"ok"],"results":[{"_id" : "1", "type":"apple"},{ "_id" : "2", "type":"banana"},{ "_id":"3","type":"apple"}]}

{"_metadata":[{"timestamp":1377581622,"totalCount":9566547,"count":6,"limit":6,"offset":0,"status":"ok"],"results":[{"_id" : "4", "type":"apple"},{ "_id" : "5", "type":"banana"},{ "_id":"6","type":"apple"}]}

By setting up your props.conf, you should be able to parse this automatically:

props.conf

[fruity_json]
BREAK_ONLY_BEFORE=^{
SHOULD_LINEMERGE=true
TIME_FORMAT=$s
TIME_PREFIX="timestamp":

This will give you two distinct events and reliably extract the timestamp 🙂 I believe this would be considered best practice for defining events in Splunk.

References:
Index Multiline Events, spath (JSON field extraction). props.conf

This probably won't address all of your follow up questions, but has hopefully put you on the right track 🙂

[rturk]

If this has answered your question, please be sure to mark it as answered so people with similar issues can find the solution as well 🙂

[rturk]

Hi thufirtan - I've edited the answer to address this question for you.

[thufirtan]

hi, that sort of works. how about if one of the fields is a timestamp as well? is there a way to split up the records before indexing or is this the best method? thanks!