Splunk Search

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

Builder

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.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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:
alt text

hope it helps

View solution in original post

SplunkTrust
SplunkTrust

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:
alt text

hope it helps

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!