Getting Data In

How to extract values from a nested _JSON?

jverheijden
New Member

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!

Tags (3)
0 Karma

javiergn
SplunkTrust
SplunkTrust

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:

alt text

Hope that helps,
J

0 Karma

jverheijden
New Member

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.

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

jverheijden
New Member

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!

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...