topic Re: How to extract values from a nested _JSON? in Getting Data In

How to extract values from a nested _JSON?

jverheijden — Mon, 14 May 2018 05:55:41 GMT

I have the below json, I would like to be able to extract values that are in the email, name and provider fields.

Currently my Splunk query is sourcetype=_json | spath msg | rex field=msg mode=sed "s/\\\//g"
which gets rid of all the extra slashes.

When viewing the events in the list view, I can see the name, pid and msg fields with the msg field containing all of the nested JSON. How do I get it out?

{"name":"master",
"pid":0,
"msg":"INPUT-USER: {
    \"_id\":\"testId\",
    \"email\":\"secret@secret.com\",
    \"name\":\"sameAsEmail\",
    \"picture\":\"beautifulPic\",
    \"user_id\":\"randomStuff\",
    \"nickname\":\"emailUserName\",
    \"identities\":[{
        \"user_id\":\"yetAnotherSecret\",
        \"provider\":\"email\",
        \"connection\":\"email\"}]
    }
}

Further information: I am not an admin and do not have access to the props.conf file

Thanking you in advance!

Re: How to extract values from a nested _JSON?

javiergn — Mon, 14 May 2018 07:37:19 GMT

I think the following line is going to cause you problems:

"msg": "INPUT-USER: {

Because of the two colons and the lack of double quotes after INPUT-USER.
Assuming the lack of double quotes was a mistake and the colon after INPUT-USER is needed, you could try to "extract" your msg field first with regex, build a json and use spath.

For instance, the following works fine from my own lab:

| makeresults
| eval my_json = "
    {\"name\":\"master\",
     \"pid\":0,
     \"msg\":\"INPUT-USER\": {
         \"_id\":\"testId\",
         \"email\":\"secret@secret.com\",
         \"name\":\"sameAsEmail\",
         \"picture\":\"beautifulPic\",
         \"user_id\":\"randomStuff\",
         \"nickname\":\"emailUserName\",
         \"identities\":[{
             \"user_id\":\"yetAnotherSecret\",
             \"provider\":\"email\",
             \"connection\":\"email\"}]
         }
     }
"
| rex field=my_json "(?msi)\"msg\":\s*(?<inputuser>.+)$"
| eval inputuser = "{" . inputuser
| spath input=inputuser
| rename INPUT-USER.* AS *
| table email, name, "identities{}.provider"

Output:

Hope that helps,
J

Re: How to extract values from a nested _JSON?

jverheijden — Tue, 15 May 2018 02:45:55 GMT

Thank you very much for your answer javiergn. The closing double quote after INPUT-USER was indeed missing, thanks for pointing that out!
I would like to utilise the rest of the answer you have given but unsure where to put it. Where do I place the code you have included? (Pasting it into a new search returns eval (malformed) error). Your assistance is greatly appreciated.

Re: How to extract values from a nested _JSON?

javiergn — Thu, 17 May 2018 13:44:37 GMT

Hi, sorry for the late reply.
Based on your initial question and the format you indicated there, I would assume this is what you are looking for:

sourcetype=_json
| rex field=msg "(?msi)\"msg\":\s*(?<inputuser>.+)$"
| eval inputuser = "{" . inputuser
| spath input=inputuser
| rename INPUT-USER.* AS *
| table email, name, "identities{}.provider"

If not please provide some details about the errors you are seeing.

Re: How to extract values from a nested _JSON?

javiergn — Tue, 29 May 2018 10:04:01 GMT

@jverheijden please do not forget to accept the answer if you are happy with it

Re: How to extract values from a nested _JSON?

jverheijden — Wed, 30 May 2018 02:01:36 GMT

Thanks very much for your feedback. Unfortunately, I have not been able to test the search that you submitted, some major changes were requested regarding our logging etc. Once the changes are filtered through to production, I hope to spend some time on this request. I will not forget your suggestion or your help!

Re: How to extract values from a nested _JSON?

javiergn — Tue, 07 Aug 2018 18:36:53 GMT

Hi @jverheijden, did the answer above help? If so, please don't forget to mark it as answered. If not please provide some feedback.