Splunk Search

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

w344423
Explorer

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
Tags (1)
0 Karma
1 Solution

whrg
Motivator

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)

View solution in original post

0 Karma

w344423
Explorer

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

0 Karma

whrg
Motivator

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)
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...