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?
| 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?