How to join searches based on condition?

[AnujaJ]

I have two searches :
Duration for which a device uses the system

index=device | fields device_start_time,device_end_time,device_id, duration, system_id
Time for which system is running

index=system | fields system_start_time,system_end_time, system_id, system_spec1,sstem_spec2,system_spec3
Now each device runs at different time, each system runs at different time. The multiple devices can subscribe to one running system at a time and assumes the specifications of the system. System have different specifications for different system_start and system_end times. So the results should contain co-related events for each device such that like device_start_time>=system_start_time AND device_end_time<=system_end_time AND system_id matches

Final result:
device_id,system_id,system_spec1,system_spec2,system_spec3

[VatsalJagani · Accepted solution]

Hello @AnujaJ,

Try this query:

index=device | fields device_start_time,device_end_time,device_id, duration, system_id
| join system_id [| search index=system | fields system_start_time,system_end_time, system_id, system_spec1,sstem_spec2,system_spec3]
| where device_start_time>=system_start_time AND device_end_time<=system_end_time

Hope this helps!!! If this is not what you wanted describe question more here in the comment.

[AnujaJ]

Thank you that works perfectly! 🙂