How do you go forward and backwards through the following records?

[w344423]

Hi all,

I need some help here. I have a sample records of 30 lines, and now would need to eval the endtime. However, I still need the event for door open and close.

if (current desc = "Valid Card Entry" AND next row of desc = "Valid Card Exit") AND (current id = next ID) then endtime for current row with desc = "Valid Card Entry" will have the datetime for next desc = "Valid Card Exit" datetime

datetime               id                 desc          location            starttime 
2018-11-13 18:46:42 STAFF-1001  Valid Card Entry    cca 2018-11-13 18:46:42
2018-11-13 18:46:43 STAFF-1001  Door is opened  cca na
2018-11-13 18:46:47 STAFF-1001  Door is closed  cca na
2018-11-13 18:46:49 STAFF-1001  Valid Card Exit cca 2018-11-13 18:46:49
2018-11-13 18:46:50 STAFF-1001  Door is opened  cca na
2018-11-13 18:46:55 STAFF-1001  Door is left open   cca na
2018-11-13 18:46:56 STAFF-1001  Door is closed  cca na
2018-11-13 18:47:22 STAFF-1001  Valid Card Entry    cca 2018-11-13 18:47:22
2018-11-13 18:47:23 STAFF-1001  Door is opened  cca na
2018-11-13 18:47:24 STAFF-1001  Door is closed  cca na
2018-11-13 18:47:28 STAFF-1001  Antipassback Lockout (Entry)    cca na
2018-11-01 11:11:14 STAFF-1006  Valid Card Entry    cca 2018-11-01 11:11:14
2018-11-01 11:11:15 STAFF-1006  Door is opened  cca na
2018-11-01 11:11:19 STAFF-1006  Door is closed  cca na
2018-11-01 11:12:25 STAFF-1006  Valid Card Exit cca 2018-11-01 11:12:25
2018-11-01 11:12:27 STAFF-1006  Door is opened  cca na
2018-11-01 11:12:31 STAFF-1006  Door is closed  cca na
2018-11-12 10:08:17 STAFF-1006  Valid Card Entry    cca 2018-11-12 10:08:17
2018-11-12 10:08:17 STAFF-1006  Door is opened  cca na
2018-11-12 10:08:33 STAFF-1006  Door is left open   cca na
2018-11-12 10:08:33 STAFF-1006  Door is closed  cca na
2018-11-12 10:18:45 STAFF-1006  Valid Card Exit cca 2018-11-12 10:18:45
2018-11-12 10:18:45 STAFF-1006  Door is opened  cca na
2018-11-26 13:53:57 STAFF-1006  Valid Card Entry    cca 2018-11-26 13:53:57
2018-11-26 13:53:57 STAFF-1006  Door is opened  cca na
2018-11-26 13:54:03 STAFF-1006  Door is left open   cca na
2018-11-26 13:54:07 STAFF-1006  Door is closed  cca na
2018-11-26 14:46:11 STAFF-1006  Valid Card Exit cca 2018-11-26 14:46:11
2018-11-26 14:46:13 STAFF-1006  Door is opened  cca na
2018-11-26 14:46:19 STAFF-1006  Door is left open   cca na

[w344423]

I found another way to do it but not sure if this is the norm but this works for me.

| streamstats current=f window=1 last(datetime) as prevtime last(id) as previd last(code) as prevcode
| reverse
| streamstats current=f window=1 last(datetime) as nexttime last(id) as nextid last(code) as nextcode
| reverse

[whrg · Accepted solution]

Hello @w344423,

You could use either the transaction command or the streamstats command.

your base search | sort -_time | streamstats current=true reset_after="("desc==\"Valid Card Entry\"")" first(_time) as endtime | convert ctime(endtime)

Here is the transaction command:

your base search | transaction startswith="Valid Card Entry" endswith="Valid Card Exit" | eval endtime=_time+duration | convert ctime(endtime)