Splunk Search

How do I merge lookup table and index results?

snipedown21
Path Finder

Hi.
To start with, I have a lookup table like so.
keyValue.csv
date key value
01/01/2017 EE Enterprise Edition
01/03/2017 EE Edited Edition
01/05/2017 EE Epsilon Edition

Now, we see that the value for the key EE changes twice.
For events coming from an index, I have _time and a field called 'Name'.
Like this.
index=event_container
_time Name
01/12/2016 EE
01/02/2017 EE
01/04/2017 EE
01/12/2017 EE

What I'm looking to do is, retrieve the value from the lookup for the "Name" in the event, and display it along side the Name, but with the time in consideration. Hence:

_time Name Description
01/12/2016 EE (whatever previous value if it existed)
01/02/2017 EE Enterprise Edition
01/04/2017 EE Edited Edition
01/12/2017 EE Epsilon Edition

what I have so far is: index=event_container | lookup keyValue.csv date key value | join type=inner _time | table _time, Name, value | rename value as Description
Thank you.
-SnipeDown21

0 Karma

somesoni2
Revered Legend

You would need to setup time-based lookup. See this for more information.

https://docs.splunk.com/Documentation/Splunk/7.0.1/Knowledge/Configureatime-boundedlookup

Your lookup should have a time field with epoch format value (not string). Once it's configured, you just need to do a |lookup (no joins required).

snipedown21
Path Finder

Yes. I read that link and looks like I need that, but I need help with the query as well. What I have doesn't seem to work currently.

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi @snipedown21,

I am bit confused about what you want to achieve because you have mentioned that Name and _time field should be match with lookup table key and date fields then your output will be something like this

_time Name Description
01/12/2016 EE NULL
01/01/2017 EE Enterprise Edition
01/03/2017 EE Edited Edition
01/05/2017 EE Epsilon Edition

Can you please clarify on this?

0 Karma

snipedown21
Path Finder

The Name and _time will be variables looked up in the lookup table called keyValue.csv and the appropriate value(for the key and the date range) will be picked and returned to the table.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.