topic Re: How to use timebased lookup table? in Splunk Search

How to use timebased lookup table?

samlinsongguo — Wed, 22 Nov 2017 04:05:26 GMT

I defined a lookup table as timebased config in transforms.conf as below

[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

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: How to use timebased lookup table?

hardikJsheth — Wed, 22 Nov 2017 06:00:44 GMT

There is no difference in the syntax for the lookup command. However the data returned by lookup command will also take the time in consideration. Try with the time range as per value of SwipeTime

Re: How to use timebased lookup table?

samlinsongguo — Thu, 23 Nov 2017 23:47:59 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.