topic Re: search event base on given time in Splunk Search

search event base on given time

samlinsongguo — Tue, 21 Nov 2017 03:19:16 GMT

I have two information door swipe card record and user logon record
door swipe card record
user swipetime result
user1 21/11/17 8:39AM success
user2 21/11/17 7:39AM success
user3 21/11/17 8:30AM success
user1 20/11/17 9:50AM success

user logon record
user logontime
user1 21/11/17 8:42AM
user2 21/11/17 7:45AM
user1 20/11/17 3:25AM

what I want to do is when I see a user logon event I can go search user swipe card record to see whether there is a swipe card event happened within 10 mins before.

for example user1 logoned on at 21/11/17 8:42 so I want to check between 21/11/17 8:32AM and 21/11/17 8:42AM whether there is swipe card record, the return result will be success
another case is user logoned on at 20/11/17 3:25AM so I want to check between 20/11/17 3:15AM and 20/11/17 3:25AM whether there is swipe card record, the return result will be nothing because there is no record.

Anyone can give me some suggestion how should I write this?
Thanks

Re: search event base on given time

HiroshiSatoh — Tue, 21 Nov 2017 06:42:20 GMT

Make 「door swipe card record」 lookup and use time-based lookup.
https://docs.splunk.com/Documentation/Splunk/7.0.0/Knowledge/Defineatime-basedlookupinSplunkWeb

Name of time field：swipetime
Maximum offset：600

The point of note is that time logontime must be _time.

Re: search event base on given time

samlinsongguo — Tue, 29 Sep 2020 16:52:04 GMT

Hi Hiroshisatoh
Thank you for your suggestion.
just a question on how to user the time-based lookup
in the transforms.conf I have following config
[swipeR.csv]
batch_index_query = 0
case_sensitive_match = 0
filename = swipeR.csv
max_offset_secs = 600
time_field = SwipeTime
time_format = %d/%m/%y %H:%M
however when I try to run following queue
index=main sourcetype="csv"
|lookup swipeR.csv SwipeTime AS LogonAt OUTPUT Action
it shows me error message

Error in 'lookup' command: You cannot use timefield as a lookup field.

any suggestion on what is may go wrong?
Thanks
Sam

Re: search event base on given time

samlinsongguo — Wed, 22 Nov 2017 05:16:41 GMT

I think it may because I use the time as a lookup field
I change my queue to
index=main sourcetype="csv"
| lookup swipe FullUserName OUTPUT Action
it doesnt return any result, eventhough I am sure there is time match my queue. What is the syntax to use time based lookup? is that different as normal lookup?

Re: search event base on given time

samlinsongguo — Thu, 23 Nov 2017 23:48:53 GMT

Found the problem. my lookup table define the time format as

%d/%m/%y %H:%M
but when I open the .csv file from excel it auto changed the time format to

%d/%m/%Y %H:%M
so the lookup table doesn't work. once I edit it in the notepad and change from 2017 to 17 and recreate the lookup table everything work as expected.

Re: search event base on given time

samlinsongguo — Tue, 28 Nov 2017 01:23:35 GMT

Hi HiroshiSatoh
Your direction certain give me the right direction. but I still have some quetions on how to use time based lookup.
in your answer you mentioned "The point of note is that time logontime must be _time." is that means the time to match is only works for default timestemp field _time and I cant make a different field? are you able to have a look another question i have relate with this exercise
https://answers.splunk.com/answers/594399/troubleshooting-timebased-lookup-table.html
Thank you for your help
Sam