topic Re: Search and lookup table fields comparison in Splunk Search

Search and lookup table fields comparison

DomenicoFumarol — Thu, 23 Jul 2020 22:44:59 GMT

Hello everyone,
I have the challenge to compare two date fields, one coming from a search and the other one is reported in a lookup table. Of these two dates I would like to get the most recent one.
Let's say that the search can give me the "last time a specific user came into our shop" and in the lookup table instead we report "last time the user bought something in our shop"
Assuming that the two dates always exist, I would like to get the most recent of the twos.

Appreciated your help.

Re: Search and lookup table fields comparison

gcusello — Wed, 30 Sep 2020 04:28:30 GMT

Hi @DomenicoFumarola,
I haven't your logs, so I put some assumptions:

the field in search is last_time_1
the column in lookup is last_time_2
please, don't use (if possible) fields with spaces!
the time format is the same in both search and lookup and it's yyyy-mm-dd hh:mm:ss

So I can suppose something like this:

index=your_index
| lookup your_lookup.csv user OUTPUT last_time_2
| eval last_time_1=strptime(last_time_1,"%Y-%m-%d %H:%M:%S"), last_time_2=strptime(last_time_2,"%Y-%m-%d %H:%M:%S")
| stats latest(last_time_1) AS last_time_1 latest(last_time_2) AS last_time_2 BY user
| eval recent=if(last_time_1>last_time_2,last_time_1,last_time_2)
| table user recent

Ciao.
Giuseppe

Re: Search and lookup table fields comparison

DomenicoFumarol — Thu, 05 Mar 2020 11:31:15 GMT

worked like a charm!

Grazie