topic Re: How do I merge lookup table and index results? in Splunk Search

How do I merge lookup table and index results?

snipedown21 — Tue, 29 Sep 2020 17:06:21 GMT

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

Re: How do I merge lookup table and index results?

harsmarvania57 — Thu, 07 Dec 2017 14:35:54 GMT

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?

Re: How do I merge lookup table and index results?

somesoni2 — Thu, 07 Dec 2017 15:36:29 GMT

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).

Re: How do I merge lookup table and index results?

snipedown21 — Fri, 08 Dec 2017 03:55:11 GMT

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.

Re: How do I merge lookup table and index results?

snipedown21 — Fri, 08 Dec 2017 03:57:43 GMT

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.