https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559784#M159049 <P>Rather than using a join you could try append and stats by id - you may end up with some mutlivalue fields though depending on your data</P><LI-CODE lang="markup">index="..." sourcetype="..." someField=someValue [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval earliest=(collectionTimeEpoch-maxDurationInSeconds) | stats min(earliest) as earliest] | append [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval itemList=split(items,",") | mvexpand itemList | rex field=itemList "(?&lt;id&gt;[-\w\d]+)#content=(?&lt;content&gt;[-\w\d]+)" | eval start=(collectionTimeEpoch-maxDurationInSeconds)] | stats values(*) as * by id</LI-CODE> Fri, 16 Jul 2021 09:52:00 GMT ITWhisperer 2021-07-16T09:52:00Z https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559771#M159042 <P>Hi,</P><P>I don't know if it is possible, but I would like to specify the time range of a join subsearch from a calculated value.</P><P>I have a similar log record and query:<BR />Log record:</P><P>&nbsp;</P><LI-CODE lang="markup">myField=abc, collectionTimeEpoch=1626358999, maxDurationInSeconds=10, items=[id=00000000-00000000-00000000-00000000#content=123,id=myId2#content=456]</LI-CODE><P>&nbsp;</P><P>The query is similar to the following:</P><P>&nbsp;</P><LI-CODE lang="markup">index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval itemList=split(items,",") | mvexpand itemList | rex field=itemList "(?&lt;id&gt;[-\w\d]+)#content=(?&lt;content&gt;[-\w\d]+)" | eval start=(collectionTimeEpoch-maxDurationInSeconds) | join type=left id [search earliest=-2d@d index="..." sourcetype="..." someField=someValue ]</LI-CODE><P>&nbsp;</P><P>I would like to replace&nbsp;earliest=-2d@d&nbsp; to something like earliest=start, but that is not working. I have also tried</P><P>&nbsp;</P><LI-CODE lang="markup">| join type=left id [search earliest=[stats count | eval earliest=(collectionTimeEpoch-maxDurationInSeconds) |fields earliest ] index="..." sourcetype="..." someField=someValue ]</LI-CODE><P>&nbsp;</P><P>Could you help me with this?</P><P>Thanks in advance</P> Fri, 16 Jul 2021 07:08:38 GMT https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559771#M159042 szabolcs 2021-07-16T07:08:38Z https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559775#M159046 <P>The reason it isn't working is that the subsearch is executed independently from the first search and nothing from the first search is passed in or available to the subsearch e.g.&nbsp;collectionTimeEpoch and maxDurationInSeconds.</P><P>You might be able to invert your search (and to be honest I am not sure if this will work) such that you construct a search to return the earliest earliest time from all the events and return that as an argument to the search</P><LI-CODE lang="markup">index="..." sourcetype="..." someField=someValue [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval itemList=split(items,",") | mvexpand itemList | rex field=itemList "(?&lt;id&gt;[-\w\d]+)#content=(?&lt;content&gt;[-\w\d]+)" | eval earliest=(collectionTimeEpoch-maxDurationInSeconds) | stats min(earlliest) as earliest]</LI-CODE> Fri, 16 Jul 2021 07:25:26 GMT https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559775#M159046 ITWhisperer 2021-07-16T07:25:26Z https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559776#M159047 <P>Thanks, that is a great idea! The following seems to be mostly working.</P><LI-CODE lang="markup">index="..." sourcetype="..." someField=someValue [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval earliest=(collectionTimeEpoch-maxDurationInSeconds) | stats min(earlliest) as earliest] | join id [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval itemList=split(items,",") | mvexpand itemList | rex field=itemList "(?&lt;id&gt;[-\w\d]+)#content=(?&lt;content&gt;[-\w\d]+)" | eval earliest=(collectionTimeEpoch-maxDurationInSeconds)]</LI-CODE><P>The only problem is that Splunk does not support right(or full outer) join so if the main search does not find the value, I won't see the result of the subsearch either.&nbsp;</P> Fri, 16 Jul 2021 08:00:39 GMT https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559776#M159047 szabolcs 2021-07-16T08:00:39Z https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559784#M159049 <P>Rather than using a join you could try append and stats by id - you may end up with some mutlivalue fields though depending on your data</P><LI-CODE lang="markup">index="..." sourcetype="..." someField=someValue [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval earliest=(collectionTimeEpoch-maxDurationInSeconds) | stats min(earliest) as earliest] | append [search index="..." sourcetype="..." myField=abc | sort -_time | head 1 | eval itemList=split(items,",") | mvexpand itemList | rex field=itemList "(?&lt;id&gt;[-\w\d]+)#content=(?&lt;content&gt;[-\w\d]+)" | eval start=(collectionTimeEpoch-maxDurationInSeconds)] | stats values(*) as * by id</LI-CODE> Fri, 16 Jul 2021 09:52:00 GMT https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/559784#M159049 ITWhisperer 2021-07-16T09:52:00Z https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/560026#M159135 <P>This became a bit more complex than I had expected, but it works after taking care of the multivalue fields. Thanks again.</P> Mon, 19 Jul 2021 11:26:54 GMT https://community.splunk.com/t5/Splunk-Search/Join-with-calculated-earliest/m-p/560026#M159135 szabolcs 2021-07-19T11:26:54Z