Duration between first occurence of one event and occurence of another event

[ikey]

I want to get the duration between two different events.
In a simplified structure my events have a timestamp and a state (Online, Offline). Every minute a new event is added to the index that contains data like the following example

Time      State
01      Online
02      Online
03      Offline
04      Offline
05      Offline
06      Online
07      Online
08      Offline
09      Offline
10      Online
11      Online

What I want to achieve is the duration from the first occurence of an event thats State is "Offline" until it changes to "Online" again.

I tried to achive that using transactions, but when I use them with "startswith=Offline" and "endswith=Online" option I get multiple results because after the first Offline state of each block another Offline state follows until it changes back to online. Referring to my example I get for the timespan between 03 and 06 three results from the transaction (03 to 06, 04 to 06, 05 to 06), but I'd like to have only one result for the transaction (03 - 06).

My current query looks similar to this one:

* | transaction startswith=(state="Offline") endswith=("state"=Online) 

[woodcock]

Like this:

| makeresults 
| eval raw="Time=01,State=Online Time=02,State=Online Time=03,State=Offline Time=04,State=Offline Time=05,State=Offline Time=06,State=Online Time=07,State=Online Time=08,State=Offline Time=09,State=Offline Time=10,State=Online Time=11,State=Online" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw 
| kv 
| table Time State 
| reverse 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| streamstats count(eval(State=="Online")) AS sessionID 
| stats range(Time) AS duration BY sessionID

[jpolvino]

A little tough figuring out how you want the output to look, but it sounds like streamstats may be one way to achieve what you're trying to do. (This is a good go-to when you're looking to do some analysis on events where relative position or field change is important.)

I'm going to assume you only want the output to contain lines having Online and for each of those, the number of Offline events that precede. This solution discards cases where something has been online for several timestamps, because it sounds like you only want to see the first Online. You can play around with the eval State near the top, which is used to jumble the Online and Offline a bit.

| makeresults count=10 | eval Time=1 | accum Time| eval State=if(Time%6==0 OR Time%5==0,"Online","Offline") | table Time State
| streamstats reset_on_change=true count AS Minutes by State
| streamstats reset_before="("match(State,\"Online\")")" count AS changed by State
| autoregress Minutes
| where State="Online" AND Minutes==changed
| table Time State Minutes_p1

Output:

Time    State     Minutes_p1
5       Online    4
10      Online    3

If you strip out the last 2 lines, you'll see the underlying fields used for the logic:

Time    State      Minutes    Minutes_p1    changed
1       Offline    1                        1
2       Offline    2          1             2
3       Offline    3          2             3
4       Offline    4          3             4
5       Online     1          4             1
6       Online     2          1             1
7       Offline    1          2             1
8       Offline    2          1             2
9       Offline    3          2             3
10      Online     1          3             1

This "changed" field resets when it sees State go to Online.