Unable to do simple calculation of aggregate durations between 2 states by host

[smahoney]

Given that per host there are 2 events logged, one indicating transition to active and one indicating transition to inactive.  I cant figure out a query that can accurately do this per host given the following stipulations.

Given the first event within the query time range, it can be assumed the host was in the opposite state prior.

Only calculate transitions between the 2 states, if there are multiple same events within transitions, calculate of the time of the first occuring.

Include the latest condition up until the time the search is run.

[ITWhisperer]

"simple" is a subjective term!

Assuming you can evaluate state based on the events as being either 0 or 1 (I have used a random number to simulate different events), then you could try something like this

| eval state=if(random()%5 == 0, 0, 1)
| streamstats range(state) as changed count as host_event by host global=f window=2
| eval changed = if(host_event == 1, 1, changed)
| where changed == 1
| streamstats range(_time) as interval last(state) as state by host global=f window=2
| appendpipe
    [| stats last(state) as state last(_time) as last_event by host
    | addinfo
    | eval _time=info_max_time
    | eval interval=_time-last_event
    | fields - info_* last_event]