how to sum of items of array field of Splunk row

Question

I have following structure of data in Splunk. Each JSON block represents one splunk row or record

{
    "startTime": "2023-09-09T05:10:16.2360649Z",
    "version": "1.0.0",
    "duration": 64,
    "status": "Allow",
    "method": "POST",
    "recordId": "PF6ZN3ALJS9S",
    "setSpans": [0.31, 0.98, 0.9, 0.49, 1.02, 1.07, 0.41, 0.5, 1.01, 0.99, 0.49],
    "getSpans": [0.48, 1.76, 0.41, 0.31, 0.41, 0.31, 0.43, 0.91, 0.32, 0.4, 0.9]
}
{
    "startTime": "2023-09-09T05:10:16.6549716Z",
    "version": "1.0.0",
    "duration": 34,
    "status": "OK",
    "method": "GET",
    "recordId": "CU5WJKHHAAKM",
    "setSpans": [1.04],
    "getSpans": [0.46, 1.03, 0.41, 0.97, 0.41, 0.34, 0.94, 0.4, 0.39, 0.95]
}
{
    "startTime": "2023-09-09T05:10:17.6927429Z",
    "version": "1.0.0",
    "duration": 75,
    "status": "Allow",
    "method": "POST",
    "recordId": "764YR7FK7EZQ",
    "setSpans": [0.98, 0.9, 1.04, 1.01, 0.99, 1.01, 1.0, 1.02],
    "getSpans": [1.11, 1.82, 0.41, 0.31, 1.08, 0.37, 1.02, 0.33, 1.13, 0.9, 1.0, 0.93, 0.34, 0.33, 0.99, 0.9]
}

There are two fields setSpans and getSpans which are in the form of array. They have double numbers in array.

I need to calculate of sum of items of these arrays individually. I need to have an additional fields created using eval or something for each record so that I can perform stats or timechart on them.

or may be a table something like following.

recordId      | totalSetSpan | totalGetSpan
-------------------------------------------
PF6ZN3ALJS9S  |         7.68 |         5.74 
-------------------------------------------
CU5WJKHHAAKM  |         1.04 |         5.35
-------------------------------------------
764YR7FK7EZQ  |         7.95 |        12.07

I need to be able to run query something like following.

... my search ... | ... eval or stats or something to get setSpanSum and getSpanSum... | timechart span=1m p99(setSpanSum)

I hope I am able to explain my issue properly. Any help or direction towards solving this will be a great help.

Answer 1

Splunk can treat the JSON arrays as multi-value fields, but to add the contents of the multi-value fields you'll need the mvstats external command. Get from Splunkbase (https://splunkbase.splunk.com/app/5198) and install it. Then you can use this run-anywhere example as a guide.

| makeresults format=json data="[ {
    \"startTime\": \"2023-09-09T05:10:16.2360649Z\",
    \"version\": \"1.0.0\",
    \"duration\": 64,
    \"status\": \"Allow\",
    \"method\": \"POST\",
    \"recordId\": \"PF6ZN3ALJS9S\",
    \"setSpans\": [0.31, 0.98, 0.9, 0.49, 1.02, 1.07, 0.41, 0.5, 1.01, 0.99, 0.49],
    \"getSpans\": [0.48, 1.76, 0.41, 0.31, 0.41, 0.31, 0.43, 0.91, 0.32, 0.4, 0.9]
},
{
    \"startTime\": \"2023-09-09T05:10:16.6549716Z\",
    \"version\": \"1.0.0\",
    \"duration\": 34,
    \"status\": \"OK\",
    \"method\": \"GET\",
    \"recordId\": \"CU5WJKHHAAKM\",
    \"setSpans\": [1.04],
    \"getSpans\": [0.46, 1.03, 0.41, 0.97, 0.41, 0.34, 0.94, 0.4, 0.39, 0.95]
},
{
    \"startTime\": \"2023-09-09T05:10:17.6927429Z\",
    \"version\": \"1.0.0\",
    \"duration\": 75,
    \"status\": \"Allow\",
    \"method\": \"POST\",
    \"recordId\": \"764YR7FK7EZQ\",
    \"setSpans\": [0.98, 0.9, 1.04, 1.01, 0.99, 1.01, 1.0, 1.02],
    \"getSpans\": [1.11, 1.82, 0.41, 0.31, 1.08, 0.37, 1.02, 0.33, 1.13, 0.9, 1.0, 0.93, 0.34, 0.33, 0.99, 0.9]
}]"
``` Above creates test data.  Remove IRL ```
``` Convert the setSpans array into a Splunk multi-value field ```
| eval mvSetSpans=json_array_to_mv(setSpans, false())
``` Add the contents of the MV field ```
| mvstats sum mvSetSpans as totalSetSpan
``` Repeat for getSpans ```
| eval mvGetSpans=json_array_to_mv(getSpans, false())
| mvstats sum mvGetSpans as totalGetSpan
``` Display the results ```
| table recordId totalSetSpan totalGetSpan