topic How are compare time in lookup in Splunk Search

How to compare time in lookup.

SplunkDash — Sat, 17 Feb 2024 05:36:05 GMT

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!

Re: How to compare time in lookup.

gcusello — Sat, 17 Feb 2024 05:59:19 GMT

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

Re: How to compare time in lookup.

SplunkDash — Sat, 17 Feb 2024 13:06:19 GMT

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

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.

Re: How to compare time in lookup.

gcusello — Sat, 17 Feb 2024 16:09:41 GMT

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

How are compare time in lookup

SplunkDash — Sat, 17 Feb 2024 17:58:33 GMT

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.

Re: How are compare time in lookup

gcusello — Sun, 18 Feb 2024 06:30:53 GMT

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

How are compare time in lookup

SplunkDash — Sun, 18 Feb 2024 16:05:43 GMT

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

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

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

123

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

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

126