After some research I came up with what seems a good enough (not the best) solution for this problem. The issue was that I was framing the problem in the context of how I would solve it in an object oriented programming language and not SPL.I had two arrays of dates that I would need to loop through and create date pairs as my solution.
To reframe the problem in Splunk terms I had to combine the two arrays into one and keep them in their original chronological events and add a new column to the event to indicate whether it is a Start Time or an End Of Start event.So my list of events would now look like this:
Equipment EventList ctonline cteos
CT1 6/1/2014 7:43 StartTime
CT1 6/1/2014 8:42 End Of Start
CT1 6/1/2014 8:52 End Of Start
CT1 6/1/2014 9:16 End Of Start
CT1 6/2/2014 6:31 StartTime
CT1 6/2/2014 7:30 End Of Start
CT1 6/3/2014 5:22 StartTime
CT1 6/3/2014 6:19 End Of Start
CT1 6/3/2014 10:33 End Of Start
CT1 6/3/2014 18:08 End Of Start
CT1 6/4/2014 6:27 StartTime
CT1 6/4/2014 7:05 End Of Start
CT1 6/4/2014 7:13 End Of Start
CT1 6/5/2014 6:22 StartTime
CT1 6/5/2014 6:58 End Of Start
Given this I can then use the streamstats command to mark a valid StartTime event as one that occurs after an non-StartTime event and a valid End of Start event as one that occurs after a non-End of Start event.The result of these can be stored in two new columns as shown below:
Equipment EventList ctonline cteos prevctonline prevcteos
CT1 6/1/2014 7:43 StartTime 1 0
CT1 6/1/2014 8:42 End Of Start 0 1
CT1 6/1/2014 8:52 End Of Start 0 0
CT1 6/1/2014 9:16 End Of Start 0 0
CT1 6/2/2014 6:31 StartTime 1 0
CT1 6/2/2014 7:30 End Of Start 0 1
CT1 6/3/2014 5:22 StartTime 1 0
CT1 6/3/2014 6:19 End Of Start 0 1
CT1 6/3/2014 10:33 End Of Start 0 0
CT1 6/3/2014 18:08 End Of Start 0 0
CT1 6/4/2014 6:27 StartTime 1 0
CT1 6/4/2014 7:05 End Of Start 0 1
CT1 6/4/2014 7:13 End Of Start 0 0
CT1 6/5/2014 6:22 StartTime 1 0
CT1 6/5/2014 6:58 End Of Start 0 1
Now we can apply the transact command to group events that only fall between a valid StartTime and a valid End of Start event. Below is the code to achieve this.
...|streamstats current=f window=1 last(ctonline) as prevctonline last(cteos) as prevcteos | reverse | transaction Equipment startswith=eval(ctonline!=coalesce(prevctonline,"") and ctonline="Start") endswith=eval(cteos!=coalesce(prevcteos,"") and cteos="EndOfStart") | sort TimeStamp | timechart avg(duration) by Equipment
The result of the query above gives you the correct grouping for the Start Time and End of Start events .. without having to loop through the arrays as before.Hopefully this helps someone else.
... View more