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