topic Joining two data sets using time windows in Splunk Search

Joining two data sets using time windows

mmanfred — Mon, 28 Sep 2020 15:24:13 GMT

I have two data sets that I want to join:

Set A: _time, field1, field2, field3...

via search: eventtype=mystats | fields _time, field1, field2, field3...

Set B: _raw

via search: eventype=mydata | tranaction .... | closed_txn = 0 | where _time >= "Set A _time - 5 seconds" AND _time <= "Set A _time + 5 seconds" | fields _raw

The result should be simply appending all Set B _raw that match to the rows of Set A.

Set A+B: _time, field1, field2, field3, _raw

Is this possible with a subsearch to iterate over time or join by time while passing the _time around? I am not sure what the right approach is but I am trying to find open transactions in set B around a window of events in Set A (which ticks every 30 seconds)

Re: Joining two data sets using time windows

davidpaper — Mon, 28 Sep 2020 15:24:19 GMT

May give you exactly what you are looking for.

Re: Joining two data sets using time windows

lguinn2 — Tue, 03 Dec 2013 06:32:56 GMT

This will join only when the timestamps are identical to the millisecond.

Re: Joining two data sets using time windows

mmanfred — Tue, 03 Dec 2013 15:45:37 GMT

agree with lguinn that it will match identically. I am looking for something that is more akin to a fuzzy match on the window. If first event is 12:00:00 I need the joined events to be between 11:59:55 and 12:00:05 in this example. (this is a one to many match and I would probably want to count the number of events as well with the results as a drill down later for example)