Search and lookup table fields comparison

[DomenicoFumarol]

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.

[gcusello · Accepted solution]

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

[DomenicoFumarol]

worked like a charm!

Grazie