How to compare time in lookup.

[SplunkDash]

Hello,

I have a lookup table called account_audit.csv and have a timestamp field UPDATE_DATE=01/05/24 04:49:26. How can I find all events within that lookup with UPDATE_DATE  >= 01/25/24. Any recommendations will be highly appreciated. Thank you! 

[gcusello]

Hi @SplunkDash,

at first, why are you using a lookup is you must use a timestamp?

a lookup is a static table. if you need to associate a timestamp to each row, it's easier to store these csv data in an index.

Anyway, you can also create a time based lookup, but I never used this option because, in this situation, I prefer to use the previous solution.

At least, directly answering to your question, you should transform the timestamp fields in epochtime, using "eval strptime", to elaborate the timestamp and compare with a time picker.

Ciao.

Giuseppe

[SplunkDash]

Hello @gcusello 

Thank you so much for your quick response. Here what I did.

| inputlookup account_audit.csv

| eval t=strftime(relative_time(now(),"-30d"), "%m/%d/%y" %H:%M:%S)

|eval updatedate=strptime(UPDATE_DATE, "%m/%d/%y" %H:%M:%S)

|eval comparetdate =strtime(t, "%m/%d/%y" %H:%M:%S)

|where updatedate >comparedate

|table account_id    Name   Org_Code    UPDATE_DATE

But I am not getting result as expected. It's coming like as follow (duplicate account id comes under same event as a group)

account_id    Name   Org_Code    UPDATE_DATE 

121                          test      Y                 01/24/2024 04:52:10

121                           

121

123                          test2    A                01/30/2024  12:50:10

123      

126                           test3    B              02/01/2024    11:12:02

126

Total events :3 

How can I remove duplicate account Id, I tried with dedup, but not working.

[SplunkDash]

Hello @gcusello 

Thank you so much again for your quick reply. I tried with that before not working, all duplicate  account_id  group together within one event. For Example, we should have three separate events for account_id 121. But when I use |where updatedate >comparedate or |search updatedate >comparedate Name=*; those group together in one event. I couldn't remove that or separate them. Is there any way we can do that. Thank you again.

[gcusello]

Hi @SplunkDash,

let me understand: do you have in each event the four fields : account_id, Name, Org_Code, UPDATE_DATE?

because it shouldn't be possible that some fields aren't visualizes unless they are missing in the lookup.

In this case, to assign the values where missing you coud use the join (even if I hate this command!), is this your requirement, put in the empty cells the values from other rows?

Ciao.

Giuseppe

[SplunkDash]

Hello @gcusello ,

Thank you so much for your response truly appreciate it.

It's a pretty interesting issue. When I use [without condition] :

| inputlookup account_audit.csv
| eval
updatedate=strptime(UPDATE_DATE, "%m/%d/%y" %H:%M:%S),
comparetdate=now()-86400*30
| table account_id Name Org_Code UPDATE_DATE

getting (7 independent events)

121 test Y 01/24/2024 04:52:10

121 test Y 01/24/2024 04:52:12

121 test Y 01/24/2024 04:52:11

123 test2 A 01/30/2024 12:50:11

123 test2 A 01/30/2024 12:50:20

126 test3 B 02/01/2024 11:12:23

126 test3 B 02/01/2024 11:12:21

But when I use (with condition)

| inputlookup account_audit.csv
| eval
updatedate=strptime(UPDATE_DATE, "%m/%d/%y" %H:%M:%S),
comparetdate=now()-86400*30
| search updatedate>comparedate Name=*
| table account_id Name Org_Code UPDATE_DATE

getting (3 events and each event separated by dotted line for clear understanding)

121 test Y 01/24/2024 04:52:10

121

121

---------------------

123 test2 A 01/30/2024 12:50:10

123

------------

126 test3 B 02/01/2024 11:12:02

126

[gcusello · Accepted solution]

Hi @SplunkDash,

to remove the empty rows you sould use a command "Name=*"

Anyway, I'd use a simpler search:

| inputlookup account_audit.csv
| eval 
   updatedate=strptime(UPDATE_DATE, "%m/%d/%y" %H:%M:%S),
   comparetdate=now()-86400*30
| search updatedate>comparedate Name=*
| table account_id Name Org_Code UPDATE_DATE

Even if, I'd use an index and not a lookup.

Ciao.

Giuseppe