Splunk Search

How to use timebased lookup table?

samlinsongguo
Communicator

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?

Tags (1)
0 Karma

samlinsongguo
Communicator

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.

0 Karma

hardikJsheth
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...