topic How to Extract JSON format as fields? in Splunk Search

How to Extract JSON format as fields?

karthi2809 — Thu, 24 Feb 2022 14:41:04 GMT

Need to extract json file in fields

{ "AAA": { "modified_files": [ "\"b/C:\\\\/HEAD\"", "\"b/C:\\\\/dev\"", "\"b/C:\\\\HEAD\"" ] }, "BBB": { "modified_files": [ "\"b/C:\\\\/HEAD\"", "\"b/C:\\\\/dev\"", "\"b/C:\\\\HEAD\"" ] } }

Expected Output as:

AAA,BBB is application name

eg:

Application: AAA

Thanks in advance

Re: Need to Extract JSON formate as fields?

venky1544 — Thu, 24 Feb 2022 12:35:27 GMT

if you already ingested this data and have the sourcetype as _json

you can use the following query

sourcetype=_json |rex max_match=0 field=_raw "\"(?<application>\w+)\":\s\n"

Re: Need to Extract JSON formate as fields?

karthi2809 — Thu, 24 Feb 2022 15:51:26 GMT

@venky1544 Thanks for the solution .But i am not getting application name as fields.

Re: How to Extract JSON format as fields?

venky1544 — Thu, 24 Feb 2022 16:36:50 GMT

@karthi2809 Hey

it could be that the JSON you pasted here is bit different than your original data. formats usually get changed like an added space or newlines when you copy paste if you could attach a sample file here probably can see why the query is not working and what is the sourcetype of your data is it _json ??

Re: How to Extract JSON format as fields?

yuanliu — Fri, 25 Feb 2022 07:52:11 GMT

Is this something you are looking for?

| foreach *.modified_files{} [ eval application=mvappend(application,"<<MATCHSTR>>" . "|" . mvjoin('<<MATCHSTR>>.modified_files{}', "|"))] | fields application | mvexpand application | eval application = split(application, "|") | eval application.modified_files=mvindex(application,1,-1), application.name=mvindex(application,0) | table application.*

Here, I assume that your indexed JSON has already been extracted, i.e., "flattened" into fields like AAA.modified_files{} and BBB.modified_files{}. If not, add |spath to do so.

The sample input gives the following:

application.modified_files	application.name
"b/C:\/HEAD" "b/C:\/dev" "b/C:\HEAD"	AAA
"b/C:\/HEAD" "b/C:\/dev" "b/C:\HEAD"	BBB

Re: How to Extract JSON format as fields?

karthi2809 — Mon, 28 Feb 2022 11:43:54 GMT

@yuanliu i have another query by using your query its giving dynamic fields and how can i get the counts of the dynamic fields.

Re: How to Extract JSON format as fields?

karthi2809 — Mon, 28 Feb 2022 12:41:38 GMT

@yuanliu In my json file i have another application which modified field is empty but now the application ccc is not extracting as application.

Thanks

{ "AAA": { "modified_files": [ "\"b/C:\\\\/HEAD\"", "\"b/C:\\\\/dev\"", "\"b/C:\\\\HEAD\"" ] }, "BBB": { "modified_files": [ "\"b/C:\\\\/HEAD\"", "\"b/C:\\\\/dev\"", "\"b/C:\\\\HEAD\"" ] }, "CCC": { "modified_files": [ ] } }

Re: How to Extract JSON format as fields?

yuanliu — Mon, 28 Feb 2022 17:32:24 GMT

Splunk doesn't output anything with empty array (bug?). In the past, I used sed to add a distinct string to represent empty arrays. This will work if you accept the cost of extra extraction.

| rex mode=sed "s/\[\s*\]/[\"\"]/" | spath | foreach *.modified_files{} [ eval application=mvappend(application,"<<MATCHSTR>>" . "|" . mvjoin('<<MATCHSTR>>.modified_files{}', "|"))] | fields application | mvexpand application | eval application = split(application, "|") | eval application.modified_files=mvindex(application,1,-1), application.name=mvindex(application,0) | table application.*

Here, AAA.modified_files{} and BBB.modified_files{} are already extracted when you do your search, but they are discarded. They get extracted once more in SPL after you fake the empty array.

Output from sample data:

application.modified_files	application.name
"b/C:\/HEAD" "b/C:\/dev" "b/C:\HEAD"	AAA
"b/C:\/HEAD" "b/C:\/dev" "b/C:\HEAD"	BBB
	CCC

Re: How to Extract JSON format as fields?

karthi2809 — Tue, 01 Mar 2022 04:53:28 GMT

@yuanliu perfect Thanks.