topic How to extract Json array ? in Splunk Search

How to extract Json array ?

karthi2809 — Wed, 06 Mar 2024 15:34:08 GMT

Thanks in Advance.

1.I have a json object as content.payload{} and need to extract the values inside the payload.Already splunk extract field as content.payload{} and the result as

AP Import flow related results : Extract has no AP records to Import into Oracle".

But I want to extract all the details inside the content.payload. How can extract from splunk query or from props.conf file.I tried spath but cant able to get it.

2.How to rename wildcard value of content.payload{}* ?

"content" : { "jobName" : "AP2", "region" : "NA", "payload" : [ { "GL Import flow processing results" : [ { "concurBatchId" : "4", "batchId" : "6", "count" : "50", "impConReqId" : "1", "errorMessage" : null, "filename" : "CONCUR_GL.csv" } ] }, "AP Import flow related results : Extract has no AP records to Import into Oracle" ] },

Re: How to extract Json array ?

gcusello — Wed, 06 Mar 2024 16:07:15 GMT

Hi @karthi2809,

for this sourcetype use INDEXED_EXTRACTIONS = json in the sourcetype definitions (for more infos see at http://docs.splunk.com/Documentation/Splunk/9.2.0/admin/Propsconf)

othrwise, use the spath command https://docs.splunk.com/Documentation/Splunk/9.2.0/SearchReference/Spath

Ciao.

Giuseppe

Re: How to extract Json array ?

yuanliu — Wed, 06 Mar 2024 17:51:54 GMT

I doubt if Splunk has truly extracted JSON array content.payload{}. As you observed, Splunk gives you a flattened structure of the array. As @gcusello said, spath is the right tool. The syntax is

| spath content.payload{} | mvexpand content.payload{}

Normally, you can then continue to use spath to extract content.payload{} after this. But your data has another layer of array. That's not usually a problem. But then, your developers did you a great injustice by using actual data values (e.g., "GL Import flow processing results") as JSON key. Not only is this data, but the key name included major SPL breakers. I haven't found a method to use spath to handle this. If you have any influence over your developers, insist that they change "GL Import flow processing results" to a value and assign it an appropriate key such as "workflow". Otherwise, your trouble will be endless.

Luckily, Splunk introduced from_json in 9.0. If you use 9+, you can work around this temporarily before your developers take action.

| spath path=content.payload{} | mvexpand content.payload{} | fromjson content.payload{} | mvexpand "GL Import flow processing results"

You sample data should give you

GL Import flow processing results	content.payload{}
{"concurBatchId":"4","batchId":"6","count":"50","impConReqId":"1","errorMessage":null,"filename":"CONCUR_GL.csv"}	{ "GL Import flow processing results" : [ { "concurBatchId" : "4", "batchId" : "6", "count" : "50", "impConReqId" : "1", "errorMessage" : null, "filename" : "CONCUR_GL.csv" } ] }
	AP Import flow related results : Extract has no AP records to Import into Oracle

(Scroll right to see other columns.)

This is an emulation for you to play with and compare with real data

| makeresults | eval _raw = "{ \"content\" : { \"jobName\" : \"AP2\", \"region\" : \"NA\", \"payload\" : [ { \"GL Import flow processing results\" : [ { \"concurBatchId\" : \"4\", \"batchId\" : \"6\", \"count\" : \"50\", \"impConReqId\" : \"1\", \"errorMessage\" : null, \"filename\" : \"CONCUR_GL.csv\" } ] }, \"AP Import flow related results : Extract has no AP records to Import into Oracle\" ] } }" ``` data emulation above ```

Re: How to extract Json array ?

karthi2809 — Mon, 11 Mar 2024 05:03:36 GMT

Thanks

Working as expected 😊.