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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...