Splunk Search

How to get STATS or CHART result values as headers in table by _time

Explorer

I have seen several posts that seem to dance around this use case.

I'm writing into a summary index (sisumdata), summarizing by minute the number of transactions APPROVED, STAGED, DECLINED, and TOTAL. I run this ever 5 minutes to summarize the last 5 minutes, skipping the past 5 minutes to account for any queued data that may be 1 - 3 minutes late.

I need to add a sum of each RESPONSECODE to the data, we have a USE case that needs each RESPONSECODE to be a KPI.

I can add RESPONSECODE to the stats BY however this creates a new event for each RESPONSECODE for the minute time. It roughly in creases the number of events by 30%, and we already generate 3.9 million summary events a day.

The Question :
How can I create a new field for each RESPONSECODE (eval RESPONSECODE{RESPONSECODE}=RESPONSECODE as an example) and have it populate the column headers? This will allow a single event to look like the following (example1).

EXAMPLE:
time CUSTOMERNUMBER CUSTOMERSESSION APPROVED STAGED DECLINED TOTAL RESPONSECODE01 RESPONSECODE_02
2019-11-08 15:35:00 12345 XI298Y1 20 2 6 28
11 6

index=raw_data_index earliest=-10m@s latest=-5m@s
| bucket _time span=1m
| eval RESPONSE_CODE = coalesce(PLATFORM1_CODE,PLATFORM2_CODE,PLATFORM3_CODE)
| eval CUSTOMER_NUMBER = coalesce(PLATFORM1_CUSTOMER_NBR,PLATFORM2_CUSTOMER_NBR)
| eval CUSTOMER_SESSION = coalesce(PLATFORM1_CUSTOMER_SESSION,PLATFORM2_CUSTOMER_SESSION,PLATFORM2_CUSTOMER_SESSION)
| stats 
count(eval(RESPONSE_CODE == "00" OR RESPONSE_CODE == "08" OR RESPONSE_CODE == "10" OR RESPONSE_CODE == "11" OR RESPONSE_CODE == "85")) as "APPROVED",
count(eval(RESPONSE_CODE == "04" OR RESPONSE_CODE == "41" OR RESPONSE_CODE == "43")) as "STAGED"
count(eval(1=1)) as "TOTAL" by _time,CUSTOMER_NUMBER,CUSTOMER_SESSION
|  eval DECLINED=(TOTAL-(APPROVAL+STAGED))
| table _time CUSTOMER_NUMBER CUSTOMER_SESSION APPROVED STAGED DECLINED TOTAL
0 Karma

Esteemed Legend

Like this:

index=_* 
| bucket _time span=1h 
| eval RESPONSE_CODE = case(
date_second<=7, "00",
date_second<=14, "08",
date_second<=21, "10",
date_second<=28, "11",
date_second<=35, "85",
date_second<=42, "04",
date_second<=47, "41",
date_second<=52, "43",
true(), "XX")
| rename date_hour AS CUSTOMER_NUMBER, date_minute AS CUSTOMER_SESSION

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| stats count(eval(RESPONSE_CODE == "00")) AS RC00 count(eval(RESPONSE_CODE == "08")) AS RC08 count(eval(RESPONSE_CODE == "10")) AS RC10 count(eval(RESPONSE_CODE == "11")) AS RC11 count(eval(RESPONSE_CODE == "85")) AS RC85 count(eval(RESPONSE_CODE == "04")) AS RC04 count(eval(RESPONSE_CODE == "41")) AS RC41 count(eval(RESPONSE_CODE == "43")) AS RC43 count AS TOTAL BY _time CUSTOMER_NUMBER CUSTOMER_SESSION
| eval APPROVAL = RC00 + RC08 + RC10 + RC11 + RC85
| eval STAGED = RC04 + RC41 + RC43
| eval DECLINED = (TOTAL - (APPROVAL + STAGED))
| table _time CUSTOMER_NUMBER CUSTOMER_SESSION APPROVED STAGED DECLINED TOTAL
0 Karma