Splunk Search

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

lostbeatnik01
Explorer

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

I'm writing into a summary index (si_sum_data), 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 RESPONSE_CODE to the data, we have a USE case that needs each RESPONSE_CODE to be a KPI.

I can add RESPONSE_CODE to the stats BY however this creates a new event for each RESPONSE_CODE 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 RESPONSE_CODE (eval RESPONSE_CODE{RESPONSE_CODE}=RESPONSE_CODE as an example) and have it populate the column headers? This will allow a single event to look like the following (example1).

EXAMPLE:
_time CUSTOMER_NUMBER CUSTOMER_SESSION APPROVED STAGED DECLINED TOTAL RESPONSE_CODE_01 RESPONSE_CODE_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

woodcock
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...