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

[adomenico]

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

[bowesmana · Accepted solution]

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