Splunk Search

Join a lookup to events in an index where the date is one day prior?

adomenico
Explorer

I have an index that snapshots an inventory system every day.  The inventory is a list of all active circuits.  There is a timestamp and date of when the snapshot was taken, plus other details.  Only active circuits are included.  

I also have a lookup file where we tried to install a new piece of equipment.  This has the date and time of when we tried to install, which circuit we tried to install on and if it was successful or not.  

I'm trying to join the lookup to the index where the date in the index is the day prior to the date of the installation.  I only want 1 day prior, not closest date matching.  Time is not important, only the date.  

Here's my search so far:  

index="myindex" sourcetype="mysource"| fields identifier_1 identifier_2 |eval active_date=strftime(load_date, "%m/%d/%Y") | join type=inner "identifier_1" [|inputlookup mylookup.csv | rename ID_1 as identifier_1| eval fail_date=strftime(EVENT_TS, "%m/%d/%Y")| where active_date=fail_date-1]

I'm sure that this is possible, but I'm getting errors.  Any help or suggestions would be appreciated

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

A number of issues with your search.

  • You use fields command, which effectively removes load_date from your fields and you try to use that after the fields statement
  • Using join+inputlookup is not a good way to use lookup - the lookup command is for that
  • You can't do numeric calculations on string formatted dates, as in your where statement

I suspect something like this will do your trick

index="myindex" sourcetype="mysource" 
| fields identifier_1 identifier_2 load_date
| eval fail_date=strftime(relative_time(load_date, "+d"), "%m/%d/%Y") 
| lookup mylookup.csv ID_1 as identifier_1 EVENT_TS as fail_date

create the fail_date string based on the day after load_date and then lookup the ID_1 and EVENT_TS based on the data you have

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

A number of issues with your search.

  • You use fields command, which effectively removes load_date from your fields and you try to use that after the fields statement
  • Using join+inputlookup is not a good way to use lookup - the lookup command is for that
  • You can't do numeric calculations on string formatted dates, as in your where statement

I suspect something like this will do your trick

index="myindex" sourcetype="mysource" 
| fields identifier_1 identifier_2 load_date
| eval fail_date=strftime(relative_time(load_date, "+d"), "%m/%d/%Y") 
| lookup mylookup.csv ID_1 as identifier_1 EVENT_TS as fail_date

create the fail_date string based on the day after load_date and then lookup the ID_1 and EVENT_TS based on the data you have

 

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...