How to join two searches with specific times

[saikumarmacha]

I am trying to list failed jobs during an outage with respect to serverIP

The first search(Search1) gives us the outage period for different servers:

Index = test1 AND (ERROR OR CRITICAL)|  stats latest(_time) AS latest_time, earliest(_time) AS earliest_time by ServerIP

The second search(Search2) gives the list of jobs with start time and end time

Index = test2 |table job_name, start_time, end_time

My question:
The job_name(from Search1) that will fail is the jobs that will be in between latest_time and earliest_time (from Search2). How can we group the job_name that will fail with respect to ServerIP?

Can someone please give me at least an idea to resolve this issue?

[woodcock · Accepted solution]

You need to create a time-based lookup (https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Defineatime-basedlookupinSplunkWeb) from your second search like this:

index = test2
| table job_name, start_time, end_time
| rename start_time AS _time
| outputlookup append=t YourTimeBasedLookup.csv

Then use it like this:

(index = test1 AND (ERROR OR CRITICAL)) OR (index = test2)
|stats max(_time) AS latest_time, min(_time) AS earliest_time BY ServerIP
| lookup _time YourTimeBasedLookup.csv