Filtered search from 2 searches

[AllenZhang]

I have 2 searches:
1. Search(AAA)|rename _time as TimeA|table TimeA host;

2. Search(BBB)|rename _time as TimeB|table TimeB host

How to create a new search:
Search(???)|table host; (or Search(???)|table TimeA TimeB host)

Which will only list the hosts that TimeB is older(or smaller) than TimeA
(there might be more than 1 results TimeA and TimeB for each host, in that case, just pick the latest one to compare)

[richgalloway · Accepted solution]

This might get you started. There may be other ways to do this, too.

search(AAA) | dedup host | rename _time as TimeA | join host [search (BBB) | dedup host | rename _time as TimeB] | where TimeB < TimeA | table TimeA TimeB host

[AllenZhang]

Thanks to Richgalloway, it works!
However, some expected records were not there in the result, if I the time window is not long enough.
Any way to list those hosts, which were in results of search(AAA) but not in results of Search(BBB) ?

[richgalloway]

This this search:

search(AAA) | dedup host | rename _time as TimeA | join type=outer host [search (BBB) | dedup host | rename _time as TimeB | fillnull value=0 TimeB] | where TimeB < TimeA | table TimeA TimeB host

[AllenZhang]

Great, it works like a charm! I am new to Splunk, and I have learnt a lot here. Thanks again!