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)
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
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.