Join with calculated earliest

[szabolcs]

Hi,

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.

I have a similar log record and query:
Log record:

myField=abc, collectionTimeEpoch=1626358999, maxDurationInSeconds=10, items=[id=00000000-00000000-00000000-00000000#content=123,id=myId2#content=456]

The query is similar to the following:

index="..." sourcetype="..." myField=abc
| sort -_time
| head 1
| eval itemList=split(items,",")
| mvexpand itemList
| rex field=itemList "(?<id>[-\w\d]+)#content=(?<content>[-\w\d]+)"
| eval start=(collectionTimeEpoch-maxDurationInSeconds)
| join type=left id [search earliest=-2d@d index="..." sourcetype="..." someField=someValue ]

I would like to replace earliest=-2d@d  to something like earliest=start, but that is not working. I have also tried

| join type=left id [search earliest=[stats count | eval earliest=(collectionTimeEpoch-maxDurationInSeconds) |fields earliest ]  index="..." sourcetype="..." someField=someValue ]

Could you help me with this?

Thanks in advance

[ITWhisperer]

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. collectionTimeEpoch and maxDurationInSeconds.

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

index="..." sourcetype="..." someField=someValue [search index="..." sourcetype="..." myField=abc
| sort -_time
| head 1
| eval itemList=split(items,",")
| mvexpand itemList
| rex field=itemList "(?<id>[-\w\d]+)#content=(?<content>[-\w\d]+)"
| eval earliest=(collectionTimeEpoch-maxDurationInSeconds)
| stats min(earlliest) as earliest]

[szabolcs]

Thanks, that is a great idea! The following seems to be mostly working.

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 "(?<id>[-\w\d]+)#content=(?<content>[-\w\d]+)"
  | eval earliest=(collectionTimeEpoch-maxDurationInSeconds)]

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. 

[ITWhisperer · Accepted solution]

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

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 "(?<id>[-\w\d]+)#content=(?<content>[-\w\d]+)"
  | eval start=(collectionTimeEpoch-maxDurationInSeconds)]
| stats values(*) as * by id

[szabolcs]

This became a bit more complex than I had expected, but it works after taking care of the multivalue fields. Thanks again.