In a JOIN SPL query, how to make earliest 60 minutes prior for the right side than the left side

[rrahbbari]

In my SPL JOIN query, I want to get the events for, let's say, between T1 and T2; however, the relevant events on the right side of the query happened between T1-60m and T2. I can't figure out how to do it in the dashboard or just a report. Using relative_time won't work for some reason. I appreciate any help.

index=myindex 
| fields a, b, c
| join type=inner left=l right=r where l.keyid=r.keyid
  [search index=myindex ```<- how to change the earliest to earliest-60m?```
     |fields d, f
  ]
| table l.a, l.b, l.c, r.d, r.f

[ITWhisperer · Accepted solution]

As @PickleRick says, join is rarely the best option and something based on the approach outlined may be your way forward. Having said that, if you still want to go forward with a join, to answer your question about how to get earliest - 60m, you could do this

index=myindex 
| fields a, b, c
| join type=inner left=l right=r where l.keyid=r.keyid
  [search index=myindex [ | makeresults
                          | addinfo
                          | eval latest=info_max_time
                          | eval earliest=relative_time(info_min_time, "-60m")
                          | table earliest latest]
     |fields d, f
  ]
| table l.a, l.b, l.c, r.d, r.f

 The addinfo command tells you what time parameters were used for the initial search.

[rrahbbari]

Thank you, @ITWhisperer! Your solution works great for me. I understand that JOIN is not a perfect solution, especially with a 50k limitation on the right side. I need to read @PickleRick's guidance a few times to absorb it, I am looking for methods other than JOINs. Thank you!

[PickleRick]

As always - join is very rarely the way to go. The typical way is the stats approach of course.

(one set of conditions) OR (another set of conditions)
| conditional eval/rename to get common field(s)
| optional renaming so that field names don't clash
| optional filtering
| stats values(*) as * by common field(s)

That's the typical setup. Sometimes it might be easier to generate events by using multisearch instead of generating a single stream of events and selectively modify it.

The problem here is that the common field(s) in both sets must be exactly the same while you want your _time to be within some range, not exactly equal between two events. I don't have an exact answer but I'd try to save the original times in some temporary fields and then try to offset _time by an hour and then use bin command to check if they both fall within acceptable  "time bucket". And then filter with direct comparison of left values vs right values.

The only issue I see here (for which I have no solution at the moment) is that you might have multiple values matching this way - that would need some mvexpanding probably.