topic Re: Not counting events in between event pairs in Splunk Search

Not counting events in between event pairs

ellenbytech — Thu, 27 Jul 2017 14:30:14 GMT

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?

Re: Not counting events in between event pairs

somesoni2 — Thu, 27 Jul 2017 16:00:39 GMT

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

Re: Not counting events in between event pairs

ellenbytech — Thu, 27 Jul 2017 20:57:48 GMT

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?

Re: Not counting events in between event pairs

somesoni2 — Thu, 27 Jul 2017 21:09:16 GMT

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

Re: Not counting events in between event pairs

DalJeanis — Thu, 27 Jul 2017 21:25:30 GMT

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"

Re: Not counting events in between event pairs

ellenbytech — Thu, 27 Jul 2017 21:47:50 GMT

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?

Re: Not counting events in between event pairs

DalJeanis — Thu, 27 Jul 2017 23:58:35 GMT

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