Splunk Search

Join with calculated earliest

szabolcs
Explorer

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

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...