topic Re: How to extract fields from JSON which is stored in another field? in Splunk Search

How to extract fields from JSON which is stored in another field?

pwunderlich — Fri, 24 Jun 2016 15:41:19 GMT

I am new here and I have an issue which is unsolvable for me. I hope some of you can help me.

The result of my Splunk search looks like the following:

2016-06-24 14:42:29,892 ERROR: eventId=3, incoming="{"eventId":5,"gameId":1,"networkId":1,"instanceId":1,"zoneId":1,"playerId":"123","date":14...}", transformed="null",....

Now I want to get access to the fields in the incoming field so that I can search the data later with R. For this reason, I need something like: extract pairdelim="," kvdelim=":", but I have absolutely no idea how I can do that.

Given that I am not a Splunk expert, it would be great if you can formulate your answer simple and understandable.

Thank you in advance for your help, and let me know if you need further information!

Re: How to extract fields from JSON which is stored in another field?

sundareshr — Fri, 24 Jun 2016 18:10:30 GMT

Have you looked at the spath command http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Spath

... | spath incoming |

Re: How to extract fields from JSON which is stored in another field?

somesoni2 — Fri, 24 Jun 2016 18:18:18 GMT

Does the field incoming in your event contains full json string that we see in the example? If yes, then use the spath option as suggested by @sundareshr below. If not, that needs to fixed (field extraction need to be set to capture full json string) before using spath.

Re: How to extract fields from JSON which is stored in another field?

pwunderlich — Mon, 27 Jun 2016 12:42:08 GMT

Does the field incoming in your event contains full json string that we see in the example?...

Yes it does, but the spath command does not work because the value of the incoming field is only { and not the whole JSON. I think the problem are the 2 quotation marks "{" but I am not sure.

Re: How to extract fields from JSON which is stored in another field?

pwunderlich — Mon, 27 Jun 2016 15:38:02 GMT

Thank you for your answer, but spath does not working (see my comment above).

Re: How to extract fields from JSON which is stored in another field?

somesoni2 — Mon, 27 Jun 2016 15:42:04 GMT

So, due to double quotes in the value of the incoming field, the default field extraction is not capturing the whole string. In this case, you'd have to setup a custom field extraction to do that. Give this a try

your base search | rex "incoming=\"(?<incoming>.+)\", transformed=" | spath incoming

Re: How to extract fields from JSON which is stored in another field?

pwunderlich — Tue, 28 Jun 2016 14:17:27 GMT

Hi, thanks for you answer.

...| spath incoming does not work but ...| spath input=incoming works perfect!

Re: How to extract fields from JSON which is stored in another field?

lshengfa — Tue, 28 Jun 2016 14:56:18 GMT

This is awesome. This is exactly what I need. I found out all my fields of json are extracted as "{" which is awkward.
Could you explain how your regex works if possible?
Thanks!!!