Find events that occur after the time returned from a subsearch for a specific field

[loeweps]

I have two sets of data. Both have account number and date along with a list of other fields. I want to search for account numbers in dataset 1 and take those account number and find all records that occur after the event for each account number.

I assume a subsearch is the best way to do that.

If I have
Dataset1:

Account_Num      Complete_Date
1                 1/5/2015
2                 2/3/2015
3                 2/6/2015
1                 2/9/2015

Dataset2:

Account       Create_Date
1               1/1/2015
2               2/6/2015
3               2/8/2015
1               2/14/2015

I want to search for and return the account number and completed date and then search the second data set for accounts with a created date of within 14 days of the date from the first search.

I was assuming that I should do a subsearch that would return the Account_Num and Complete_Date but I am not sure of the best way to have it only search on the Account_Num and then search within a 14 day window of the Complete_Date against the Create_Date. I looked up foreach but wasn't sure if that solves my issue.

I want it to return only the three records listed below from Dataset2 because they have a matching account number and have a create date of within 14 days of the data from Dataset1

Account       Create_Date
2               2/6/2015
3               2/8/2015
1               2/14/2015

Thanks in advance for any help with this as it is appreciated.

[woodcock · Accepted solution]

Try to avoid subsearches whenever possible. Try this (assuming you are timestamping the events based on the date field values within the events):

sourcetype=Dataset1 | stats first(_time) AS latestTime by Account_Num | map search="sourcetype=Dataset2 Account=$Account_Num$ | eval delta=_time - $latestTime$ | where abs(delta)<1209600"

[loeweps]

Thank you, This gets me much closer to what I want to do. The only issue I have now is I have to convert the completed_date to epoch to use it as my comparison value. Dataset2 _time is based off create_date but Dataset1 _time value is not based off of completed_date.

Thanks again,
Paul