Splunk: Search SPL with multiple Stats

Question

I have below events

event_a has time_a and MAS_A fields

event_b has time_b and MAS_B fields

event_c has time_c and MAS_C fields

sourcetype="app" eventtype in (event_a,event_b,event_c) 
| stats avg(time_a) as "Avg_Res_Time_a" BY MAS_A 
| eval Avg_Res_Time_a=round('Avg_Res_Time_a',2)

Output I am getting from above search is two fields MAS_A and Avg_Res_Time_a

How can i get 4 fields output as below

MAS_A_B_C (should contain values from MAS_A, MAS_C, MAS_C)

Avg_Res_Time_a (stats avg(time_a) as "Avg_Res_Time_a" BY MAS_A)

Avg_Res_Time_b (stats avg(time_b) as "Avg_Res_Time_b" BY MAS_B)

Avg_Res_Time_c (stats avg(time_c) as "Avg_Res_Time_c" BY MAS_C)

Sample Events:

04-03-2020 11.31.19 OFF   performance [WebContainer : 78]: USER_ID=HEIS MAS_A=3 TIME_A=5.898
04-03-2020 02.33.42 OFF   performance [WebContainer : 29]: USER_ID=MONA MAS_B=2 TIME_B=1.18 MAS_C=2 TIME_C=2.87
04-03-2020 12.31.19 OFF   performance [WebContainer : 30]: USER_ID=HEIB MAS_A=2 TIME_A=1.22
04-03-2020 02.38.42 OFF   performance [WebContainer : 33]: USER_ID=MONA MAS_B=3 TIME_B=2.20 MAS_C=20 TIME_C=29.03

Expected output

MAS_A_B_C   Avg_Res_Time_a   Avg_Res_Time_b   Avg_Res_Time_c
2           1.22             1.18             2.87
3           5.898            2.20       
20                                            29.03

Simon Duff Simon Duff · Accepted Answer · 2020-03-17T23:36:36

Its a bit difficult without sufficient sample events, but you may be able to try using appendpipe.

sourcetype="app" eventtype in (event_a,event_b,event_c) 
| appendpipe [ where isnotnull(MAS_a) | stats avg(count) AS Avg_Res_Time_a by MAS_a ]
| appendpipe [ where isnotnull(MAS_b) | stats avg(count) AS Avg_Res_Time_b by MAS_b ]
| appendpipe [ where isnotnull(MAS_c) | stats avg(count) AS Avg_Res_Time_c by MAS_c ]
| stats values(Avg*) as Avg*

Splunk: Search SPL with multiple Stats

1 Answers