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!
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
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.
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.
@jverheijden please do not forget to accept the answer if you are happy with it
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!
Hi @jverheijden, did the answer above help? If so, please don't forget to mark it as answered. If not please provide some feedback.