topic Re: Join 2 sourcetype on on field if time difference between 2 records is less than 3 seconds in Splunk Search

Join 2 sourcetype on on field if time difference between 2 records is less than 3 seconds

anujshah — Thu, 09 Nov 2017 17:48:49 GMT

So I have 2 different source types which I can join using DEVICE field. But I wan to join records if and only if time difference if less than 3 seconds between them. (If multiple records than take latest one).

Than I want to show records only if some field in one record contain some value but in other record it doesn't contain that value.

I have achieved 2nd part using following query but I want with time condition as well.

index="index1" sourcetype="source1"  | join  DEVICE [search index=index1 sourcetype=source2 STATE=state1 OR STATE=state2 ] | eval state1=if(like(STATE, "%state1%"), 1, 0) | eval state1Control = if(like(CONTROL, "%state1%"), 1, 0) | eval state2=if(like(STATE, "%state2%"), 1, 0) | eval state2Control = if(like(CONTROL, "%state2%"), 1, 0)  | where state1!=state1Control AND state2!=state2Control |  table  _time, DEVICE, STATE, CONTROL

Re: Join 2 sourcetype on on field if time difference between 2 records is less than 3 seconds

cmerriman — Thu, 09 Nov 2017 21:52:01 GMT

can you try to do something like index="index1" sourcetype="source1" |bucket _time as timespan span=3s| join DEVICE timespan [search index=index1 sourcetype=source2 STATE=state1 OR STATE=state2|bucket _time as timespan span=3s ]...
to join on a three second span?
i think there is probably a better way around that using streamstats i'm just trying to think it through

Re: Join 2 sourcetype on on field if time difference between 2 records is less than 3 seconds

anujshah — Thu, 09 Nov 2017 22:35:27 GMT

Thank you so much for answer. I will try this. But time condition here is a bit complex: If the CONTROL in source1 contains state1 than there should be an entry in source2 which contains state1, now that entry can be there before the time of source1 entry or maximum in 3 seconds not after that the entry of source1.

Re: Join 2 sourcetype on on field if time difference between 2 records is less than 3 seconds

acharlieh — Thu, 09 Nov 2017 22:50:06 GMT

This feels like a problem for transaction with a specified maxspan but I'd have to think about it more to come up with the exact syntax: http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/transaction