How do you calculate the time between events with the streamstats command?

[splunkreal]

Hello guys,

I have data like this using Splunk 7.1 and I would like to calculate minutes between start and end of each event :

example (order can change) :

_time field1 status
2018-11-15 00:10 AAA1 start
2018-11-15 00:15 AAA2 start
2018-11-15 00:17 AAA1 end
2018-11-15 00:20 AAA2 end
2018-11-15 00:25 AAA3 start
2018-11-15 00:26 AAA3 end

I think I should group by field1 and time then calculate using streamstats.

Thanks for your help.

[adonio · Accepted solution]

hello there,

here is one way using stats
run the example below anywhere:

| makeresults count=1
| eval data = "2018-11-15 00:10 AAA1 start;
2018-11-15 00:15 AAA2 start;
2018-11-15 00:17 AAA1 end;
2018-11-15 00:20 AAA2 end;
2018-11-15 00:25 AAA3 start;
2018-11-15 00:26 AAA3 end"
| makemv delim=";" data
| mvexpand data
| rex field=data "(?<event_time>\S+\s\S+)\s(?<event_id>[^\s]+)\s(?<start_or_end>\S+)"
| table event_time event_id start_or_end
| rename COMMENT as "the above generates fake data, below is the solution"
| eval start_time = if(start_or_end="start",event_time,null())
| eval end_time = if(start_or_end="end",event_time,null())
| stats values(*_time) as *_time by event_id
| eval start_time_epoch = strptime(start_time, "%Y-%m-%d %H:%M")
| eval end_time_epoch = strptime(end_time, "%Y-%m-%d %H:%M")
| eval duration_in_secondes = end_time_epoch - start_time_epoch

screenshot:

hope it helps