topic Re: Why is my nested JSON event not formatted correctly? in Getting Data In

Why is my nested JSON event not formatted correctly?

Branden — Mon, 13 Mar 2017 14:59:42 GMT

Can Splunk be configured to allow for interpreting JSON objects with multiple-levels of depth?

Here's an example:

{  
    level:  warn 
    message:  {"invalidPublication":"Publication is valid for indexing at Elasticsearch and will be updated, but has warnings.","authors":[{"lastName":"foo","initials":"fb","firstName":"bar","authorResourceID":99999}],"title":"Some Title","warningReasons":["Invalid value for 'publicationDate' field [Sat Apr 01 2006 00:00:00 GMT-0500 (EST)], year not found in citation - dateComponents: [{\"year\":\"2008\",\"month\":\"6\",\"day\":\"2\"}].]"]} 
    pid:  2888 
    sourceHostname:  somehostname.somewhere.com
    timestamp:  2017-03-13 09:55:40 
}

In the above example, I would like the “messages” field to be interpreted by Splunk so that I can expand/collapse each section inside the message. Right now, it just displays nested JSON as a single string. Is this possible? Thanks!

Re: Why is my nested JSON event not formatted correctly?

somesoni2 — Mon, 13 Mar 2017 15:11:39 GMT

Have a look at spath command. Passing a field that contains json to this command will parse the json and extract fields.

Re: Why is my nested JSON event not formatted correctly?

Branden — Mon, 13 Mar 2017 16:14:39 GMT

I checked out spath: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath

Maybe I'm missing something fundamental, but all that seems to do is extract the nested JSON into another field containing a single string of text. That doesn't help. Here's what I tried:

| spath output=test path=message{}

I had hoped it would parse the JSON nested within 'message', but it's not doing that...

Re: Why is my nested JSON event not formatted correctly?

somesoni2 — Mon, 13 Mar 2017 16:32:25 GMT

Can you try like this. Use the exact field name in input from your current output.

| spath input=message{}

Re: Why is my nested JSON event not formatted correctly?

Branden — Mon, 13 Mar 2017 17:50:14 GMT

Tried that, but no change... doesn't appear to do anything.

Re: Why is my nested JSON event not formatted correctly?

somesoni2 — Mon, 13 Mar 2017 19:00:35 GMT

Something like this works for me (based on sample value for message field, everything except the last line is to generate sample data).

| gentimes start=-1 | eval message="{\"invalidPublication\":\"Publication is valid for indexing at Elasticsearch and will be updated, but has warnings.\",\"authors\":[{\"lastName\":\"foo\",\"initials\":\"fb\",\"firstName\":\"bar\",\"authorResourceID\":99999}],\"title\":\"Some Title\",\"warningReasons\":[\"Invalid value for 'publicationDate' field [Sat Apr 01 2006 00:00:00 GMT-0500 (EST)], year not found in citation - dateComponents: [{\\\"year\\\":\\\"2008\\\",\\\"month\\\":\\\"6\\\",\\\"day\\\":\\\"2\\\"}].]\"]}" | table message 
| spath input=message

Can you confirm what's the actual field name under which your json data appears?

Re: Why is my nested JSON event not formatted correctly?

niketn — Mon, 13 Mar 2017 19:15:07 GMT

@Branden... While the message JSON structure seems valid, outer JSON seems to be missing proper formatting and commas after each Key Value pairs. Is that how the data looks or is it typo while keying in example here?

Following data for me loaded successfully as json sourcetype and Splunk was itself able to extract all required field including inner jSON like message.authors{}.authorResourceID, message.warningReasons{} and message.invalidPublication etc.

{
    "level": "warn",
    "message": {
        "invalidPublication": "Publication is valid for indexing at Elasticsearch and will be updated, but has warnings.",
        "authors": [ {
            "lastName": "foo",
            "initials": "fb",
            "firstName": "bar", 
            "authorResourceID": 99999 } ],
        "title": "Some Title",
        "warningReasons": [ "Invalid value for 'publicationDate' field [Sat Apr 01 2006 00:00:00 GMT-0500 (EST)], year not found in citation - dateComponents: [{\"year\":\"2008\",\"month\":\"6\",\"day\":\"2\"}].]" ]
    }, 
     "pid":  "2888", 
     "sourceHostname":  "somehostname.somewhere.com",
     "timestamp":  "2017-03-13 09:55:40" 
 }

Needless to say, spath is also able to extract the same.

Re: Why is my nested JSON event not formatted correctly?

Branden — Mon, 13 Mar 2017 19:31:30 GMT

It was a copy/paste error. I should have included the raw data in my post, sorry for the confusion. I believe I'm good now, thanks!

Re: Why is my nested JSON event not formatted correctly?

niketn — Tue, 14 Mar 2017 04:11:09 GMT

@Branden, I see that you have voted both Answers by @somesoni2 and me. Please accepted one of these which has helped you or else provide your own answer and accept so that the question is marked as solved.