Splunk Search

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

gauravkumar85
Path Finder

{"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 . 

Labels (5)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

ERRORERROR_1ERROR_INDFUNCTION_NAMEPROCESSEDREMAININGSKIPPEDTARGET_SYSTEMTOTALidseveritymessage{}
0 (0%)00CPW_02170121257035 (0%)SEQ1212570Information{"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%)00CPW_0217126434019 (0%)CPW264340Information{"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%)00CPW_021722647812019 (0%)SEQ233430Information{"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 ```

 

 

Tags (2)

marnall
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...