Not counting events in between event pairs

[ellenbytech]

My search is
index=safes TransactionCode=DOPN OR TransactionCode=PWPL Details="*opened" OR Details="AC*"
| transaction open_ACpwr startswith=DOPN endswith=PWPL maxevents=2 | search eventcount=2

Essentially I want a list of events where someone opens a door and then encounters a power loss. This search, however is just pairing the last time the door was opened and the next power loss, whether or not the door was shut before the power loss occurred.
Would another way to do this be to pair every AC power loss event with the event immediately preceding it and then filtering out the ones that are not door open events? How would I go about doing this?

[DalJeanis]

Don't use transaction, use streamstats

You want to know when a door opened and has not yet closed when you get a power outage.

So, you need the _time and Location of every event where the door opens, every event where the door closes, and every event where there is a power outage. (I've assumed that Door Close events are TransactionCode="DCLO".)

your search that gets _time, Location, and DOPN or DCLO OR PWPL

| eval DoorState=case(TransactionCode="DOPN","Open",TransactionCode="DCLO","Closed",true(),null())
| eval DoorOpenTime=if(TransactionCode="DOPN",_time,null())
| sort 0 Location _time 
| rename COMMENT as "The above sets up the state of the door and a field to remember the time it opened"

| rename COMMENT as "Now we copy forward the state of the door and track it across time."
| streamstats current=t last(DoorState) as CurrState last(DoorOpenTime) as DoorOpenTime by Location

| rename COMMENT as "Get rid of any record that isn't a power loss with an open door."
| where CurrState="Open" AND TransactionCode="PWPL"

| rename COMMENT as "Now we format the results"
| table _time Location TransactionCode CurrState DoorOpenTime
| eval DoorOpenTime=strftime(DoorOpenTime,"%Y-%m-%d %H:%M:%S")

If there is any reason you want to see the entire event for the Door Open, then just use the same strategy with _raw that we did with DoorOpenTime.

---

updated to put quotes around the final filter for "PWPL"

[ellenbytech]

All the results disappear when the second half of line 12 is added. Since CurrState is defined as TransactionCode=DOPN, doesn't it automatically exclude events where the code might be PWPL?

[DalJeanis]

@ellenbytech - Sorry, updated to put quotes around "PWPL"

In answer to your question, NO, that's what the streamstats is tasked to do... copy the last open/closed status from the same location forward onto each record. Since a PWPL record has no status of its own, it gets the one from the immediate prior record of either an open or close type.

[somesoni2]

You can include maxpause option in your transaction command to incorporate 'immediate' constraint.

index=safes (TransactionCode=DOPN  Details="*opened") OR (TransactionCode=PWPLDetails="AC*") | transaction open_ACpwr startswith=DOPN endswith=PWPL maxevents=2 maxpause=2s | search eventcount=2

[ellenbytech]

Ok, that seems to clear up that issue but now I'm getting results from different locations as one event. For example:

Store# 935: Door opened 12:01:01
gets paired with
Store#2805: AC power loss 12:01:03

Can I restrict this further without running separate searches on every single store?

[somesoni2]

If store number is extracted as a field, add it to the transaction command along with open_ACpwr.