I want the results of search string 1 to be matched with search string 2 by the common field (which is field 1) and the results of this to be matched with search string 3 where the common field is field 2, then I want to get those results as output with the earliest of field 1 and latest of field 2.
I've tried the subsearch command with join but it doesn't generate the required results. Also tried append.
(index=foo1 "search terms 1") OR
(index=foo2 "search terms 2") OR
(index=foo3 "search terms 3")
| fields _time index key1 key3 data1 data2 data 3
| rename COMMENT as "find the _time of the earliest and latest records for each key1 value on each index."
| eventstats min(_time) as mintime1 max(_time) as maxtime1 by index key1
| where (index="foo1" AND _time=mintime1) OR (index="foo2" AND _time=maxtime1) OR index="foo3"
| rename COMMENT as "copy the value of data3 from search 3 onto the search2 record"
| eventstats latest(data3) as data3 by key3
| rename COMMENT as "copy the value of data1 from search 1 onto the search2 record. (there is only one data1 value)"
| eventstats earliest(data1) as data1 by key1
| rename COMMENT as "get rid of all but the type 2 record that now has all the data."
| where index="foo2"
Instead of index, you might use source or sourcetype, or something else. Ideally, right about the place where the fields command is, you narrow it down to a single record type field so that a single simple condition will differentiate between the records.