topic Re: Extract json values from string json array in Splunk Search

Extract json values from string json array

mipa04 — Fri, 31 May 2024 11:38:47 GMT

Hi, my splunk search results in two fields - Time and Event. Inside Event field there are multiple searchable fields, one of which is json array as string like this:
params="[{'field1':'value1','field2':'value2','field3':'value3'}]"

Above json array always has one json object like in example. I need to extract values for given fields from this json object - how can i do that? I figured spath is the way to do this, but none of solutions I found so far worked - maybe because all examples were operating on json as string only and in my case it is in Event as splunk shows in search - can you help?

Re: Extract json values from string json array

ITWhisperer — Fri, 31 May 2024 12:43:39 GMT

This isn't JSON as JSON uses double quotes not single quotes. Please post an accurate representation of the field you want to extract the data from.

Having said that, you should look at the json functions new to 9.x as these would probably be the basis of a solution.

Re: Extract json values from string json array

mipa04 — Fri, 31 May 2024 12:50:18 GMT

I pasted it the same as I see in splunk search results, field params is in double quotes, but inside there are single quotes. I know that json requires double quotes, but don't know if it is only matter of displaying in splunk search, or actually it is not proper json for splunk (source for this is database table, in which it is proper json array with double quotes)

Re: Extract json values from string json array

kamlesh_vaghela — Fri, 31 May 2024 12:53:40 GMT

I agree with @ITWhisperer .

and your sample JSON looks like an array of objects also. So please share a masked event here.

Meanwhile, I'm sharing a sample query so that you can start on the solution.

| makeresults | eval params="[{'field1':'value1','field2':'value2','field3':'value3','field4':'value4'}]" | rename comment as "upto now is sample data only" | eval params = replace(params, "'","\"") | eval _raw = params | spath

I have handled a single quote here. if you have valid JSON then just remove `| eval params = replace(params, "'","\"")` .

Thanks
KV

Re: Extract json values from string json array

ITWhisperer — Fri, 31 May 2024 15:13:00 GMT

Rather than pasting search results, please paste the raw event data, preferably in a code block </> to preserve original formatting.

Re: Extract json values from string json array

mipa04 — Fri, 31 May 2024 15:32:43 GMT

I don't know if that it is in Event top level field matters, so I'm pasting screenshot of raw data. Field in question is tlogParameters

Re: Extract json values from string json array

kamlesh_vaghela — Fri, 31 May 2024 16:22:53 GMT

@mipa04

I assume tlogParameters filed getting extracted properly.

Can you please try the below search?

YOUR_SEARCH | eval tlogParameters = replace(tlogParameters, "'","\"") | eval _raw = tlogParameters | spath | rename {}.* as *

My Sample Search :

| makeresults | eval tlogParameters="[{'triggeredEventName': 'CustomerLoggedIn', 'owner': 'communicationcenter', 'channel': 'SiteMessage', 'sentOrNotSent': 'Sent', 'reasonNotSent':null}]" | append [| makeresults | eval tlogParameters="[{'triggeredEventName': 'CustomerLoggedIn', 'owner': 'communicationcenter', 'channel': 'SiteMessage', 'sentOrNotSent': 'Sent', 'reasonNotSent':null}]"] | rename comment as "upto now is sample data only" | eval tlogParameters = replace(tlogParameters, "'","\"") | eval _raw = tlogParameters | spath | rename {}.* as *

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract json values from string json array

mipa04 — Mon, 03 Jun 2024 06:56:20 GMT

Thanks for response, but unfortunately it doesn't work -

YOUR_SEARCH
| eval tlogParameters = replace(tlogParameters, "'","\"")

this doesn't change anything - ie tlogParameters is still displayed in raw as single quotes and surrounded by double quotes as original.

YOUR_SEARCH
| eval tlogParameters = replace(tlogParameters, "'","\"")
| eval _raw = tlogParameters

this makes empty result (the same as full query you proposed):

Re: Extract json values from string json array

ITWhisperer — Mon, 03 Jun 2024 07:09:18 GMT

Please paste the raw event data, preferably in a code block </> to preserve original formatting.