Left Outer Join in KQL based on Date and Time

Question

I'm trying to perform a left outer join in Kusto Query Language (KQL) between two tables, trips and alerts, based on a datetime condition. The trips table contains information about unit trips with start and end dates, while the alerts table contains unit alerts with corresponding datetimes.I would like to retrieve all alert information along with the corresponding trip start and stop times.

Here are simplified versions of the tables:

let trips = datatable(unitId:int, StartDate:datetime, EndDate:datetime)[
  1, '2020-01-01 01:00:00', '2020-01-06 04:00:00',
  1, '2020-01-07 04:01:00', '2020-01-09 02:00:00'];

 let alerts = datatable(unitId:int, DateTime:datetime, description:string)[
  1, '2020-01-02 01:00:00', 'Speeding',
  1, '2020-01-16 01:00:00', 'CrashDetected',
  2, '2020-01-02 01:00:00', 'Speeding',
  2, '2020-01-16 01:00:00', 'CrashDetected'];

alerts
| join kind=leftouter trips on unitId and $left.DateTime >= $right.StartDate and $left.DateTime >= $right.EndDate

I want to perform a left outer join based on the unitId and datetime conditions, but it seems like the current query is not producing the expected results. I need to retrieve all alert information alongside the corresponding trip start and stop times, specifically when the alert occurs during the trip duration. Each row in the result represents a pairing of alert details and the associated trip start and stop times. In cases where there is no matching trip for an alert, the StartDate and EndDate columns will display (null) in the result.

Could someone please guide me on how to correctly perform a left outer join between these two tables based on the datetime conditions?

Thank you!

Answer 1

You can use below KQL query to use left outer join and get desired results:

let times = datatable(unitId:int, StartDate:datetime, EndDate:datetime)[
  1, '2024-01-01 01:00:00', '2024-01-06 04:00:00',
  1, '2024-01-07 04:01:00', '2024-01-09 02:00:00'];
 let works = datatable(unitId:int, DateTime:datetime, description:string)[
  1, '2024-01-02 01:00:00', 'Fasting',
  1, '2024-01-16 01:00:00', 'Eating',
  2, '2024-01-02 01:00:00', 'Doing',
  2, '2024-01-16 01:00:00', 'Sleeping'];
let x=works
| extend new_column = 0
| sort by new_column asc
| extend rn = row_number()
| join kind=leftouter times on unitId
| where DateTime >= StartDate and DateTime <= EndDate;
works
| extend new_column = 0
| sort by new_column asc
| extend rn = row_number()
|join kind = fullouter x on rn
|project-away rn, unitId,new_column,unitId2,new_column1,rn1,DateTime1,unitId1,description1

Output:

Fiddle.