topic How can I extract field's from given JSON array and display running total of numeric column in Splunk Search

How can I extract field's from given JSON array and display running total of numeric column

gauravkumar85 — Tue, 16 Apr 2024 19:13:05 GMT

{"id":"0","severity":"Information","message":[{"TARGET_SYSTEM":"SEQ","FUNCTION_NAME":"CPW_02170","TOTAL":"121257","PROCESSED":"121257","REMAINING":"0","ERROR":"0 (0%)","SKIPPED":"35 (0%)","ERROR_IND":"0","ERROR_1":"0"},{"TARGET_SYSTEM":"CPW","FUNCTION_NAME":"CPW_02171","TOTAL":"26434","PROCESSED":"26434","REMAINING":"0","ERROR":"0 (0%)","SKIPPED":"19 (0%)","ERROR_IND":"0","ERROR_1":"0"},{"TARGET_SYSTEM":"SEQ","FUNCTION_NAME":"CPW_02172","TOTAL":"23343","PROCESSED":"2647812","REMAINING":"0","ERROR":"0 (0%)","SKIPPED":"19 (0%)","ERROR_IND":"0","ERROR_1":"0"}]}

I want to extract all fields in the form of table from "message" which is holding JSON array .

And I want a total row for each column where total running total will display for each numeric column based on TARGET_SYSTEM .

Re: How can I extract field's from given JSON array and display running total of numeric column

yuanliu — Tue, 16 Apr 2024 19:32:21 GMT

Do not treat structured data such as JSON as text and be tempted to use rex for extraction. Use QA tested Splunk command such as spath to extract from structure, then mvexpand to handle array.

| spath path=message{} | mvexpand message{} | spath input=message{}

Your sample will give you

ERROR	ERROR_1	ERROR_IND	FUNCTION_NAME	PROCESSED	REMAINING	SKIPPED	TARGET_SYSTEM	TOTAL	id	severity	message{}
0 (0%)	0	0	CPW_02170	121257	0	35 (0%)	SEQ	121257	0	Information	{"TARGET_SYSTEM":"SEQ","FUNCTION_NAME":"CPW_02170","TOTAL":"121257","PROCESSED":"121257","REMAINING":"0","ERROR":"0 (0%)","SKIPPED":"35 (0%)","ERROR_IND":"0","ERROR_1":"0"}
0 (0%)	0	0	CPW_02171	26434	0	19 (0%)	CPW	26434	0	Information	{"TARGET_SYSTEM":"CPW","FUNCTION_NAME":"CPW_02171","TOTAL":"26434","PROCESSED":"26434","REMAINING":"0","ERROR":"0 (0%)","SKIPPED":"19 (0%)","ERROR_IND":"0","ERROR_1":"0"}
0 (0%)	0	0	CPW_02172	2647812	0	19 (0%)	SEQ	23343	0	Information	{"TARGET_SYSTEM":"SEQ","FUNCTION_NAME":"CPW_02172","TOTAL":"23343","PROCESSED":"2647812","REMAINING":"0","ERROR":"0 (0%)","SKIPPED":"19 (0%)","ERROR_IND":"0","ERROR_1":"0"}

Here is a data emulation. Play with it and compare with real data

| makeresults | eval _raw="{\"id\":\"0\",\"severity\":\"Information\",\"message\":[{\"TARGET_SYSTEM\":\"SEQ\",\"FUNCTION_NAME\":\"CPW_02170\",\"TOTAL\":\"121257\",\"PROCESSED\":\"121257\",\"REMAINING\":\"0\",\"ERROR\":\"0 (0%)\",\"SKIPPED\":\"35 (0%)\",\"ERROR_IND\":\"0\",\"ERROR_1\":\"0\"},{\"TARGET_SYSTEM\":\"CPW\",\"FUNCTION_NAME\":\"CPW_02171\",\"TOTAL\":\"26434\",\"PROCESSED\":\"26434\",\"REMAINING\":\"0\",\"ERROR\":\"0 (0%)\",\"SKIPPED\":\"19 (0%)\",\"ERROR_IND\":\"0\",\"ERROR_1\":\"0\"},{\"TARGET_SYSTEM\":\"SEQ\",\"FUNCTION_NAME\":\"CPW_02172\",\"TOTAL\":\"23343\",\"PROCESSED\":\"2647812\",\"REMAINING\":\"0\",\"ERROR\":\"0 (0%)\",\"SKIPPED\":\"19 (0%)\",\"ERROR_IND\":\"0\",\"ERROR_1\":\"0\"}]}" | spath ``` data emulation above ```

Re: How can I extract field's from given JSON array and display running total of numeric column

marnall — Tue, 16 Apr 2024 19:49:18 GMT

Just to add slightly to yuanliu's answer, you can use the "| addcoltotals" command if you would like to add another row containing the totals of the numerical columns. You'll have to convert the ones that contain non-numerical characters like the "(0%)" part.