Splunk Search

How to transform a table

dtakacssplunk
Explorer

If I have the data in following format:

time        session           event
t1          session1            actionA
t2          session1            actionB
t3          session1            actionC
t4          session1            actionA
t5          session2            actionB
t6          session2            actionC

want to write a splunk query to transform it to this format:

from         to           count    timetaken
actionA      actionB      1        (t2-t1)
actionB      actionC.     2        (t3-t2) + (t5+t6)
actionC      actionA      1        (t4-t3)

can someone recommend an expression for this?

Tags (1)
0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw=" time        session           event
 t1          session1            actionA
 t2          session1            actionB
 t3          session1            actionC
 t4          session1            actionA
 t5          session2            actionB
 t6          session2            actionC" 
| multikv forceheader=1 
| streamstats count as time_args 
| eval time_args="+".time_args."h@h-1d" 
| eval time=relative_time(_time,time_args) 
| eval _time=time 
| table _time session event 
| rename COMMENT as "this is sample you provide. from here, the logic"
| streamstats window=2 range(_time) as duration list(event) as event_list count(event) as counts by session 
| where counts=2 
| eval event_list=mvjoin(event_list,",") 
| stats sum(counts) as count sum(duration) as timetaken by event_list 
| eval from=mvindex(split(event_list,","),0), to=mvindex(split(event_list,","),1), timetaken=tostring(round(timetaken),"duration") 
| table from to count timetaken

How about this?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...