Kusto Query to join two datatables based on a list of dateranges

Question

I have two datatables in Azure Data Explorer:

1. List of events --> single events happening at a specific time
2. List of dateranges --> list of timeframes where a machine is active

My target is to find all events from table 1, which occured within the timeslots where the machine was active.

That is the query how I set up my two datatables:

let events = NOTIFICATIONTABLE
| where TimeStamp > ago(365d) 
| GUID == 'eventNotification' 
| project TimeStamp, Notification;
let timeslots = ACTIVITYTABLE
| where StartTime> ago(365d)
| where GUID == "machineActive"
| project StarTime, EndTime

What is the best way to combine these to queries? The result should be following: Give me all notifications from table events, where the TimeStamp is within one of the dateranges (StartTime to EndTime) in table timeslots.

Answer 1

This is a great question, and looks like your logic is pretty good so far. The way I'd approach it is with a Join and most specifically a Cross-join.

let NOTIFICATIONTABLE = datatable (TimeStamp:datetime, GUID:string, Notification:string) [
datetime(2022-8-1 09:00:00), 'eventNotification', 'Bad Network Traffic Detected',
datetime(2023-9-1 10:30:00), 'eventNotification', 'Bad Network Traffic Detected',
datetime(2023-10-1 17:30:00), 'eventNotification', 'Bad Network Traffic Detected'
];
let ACTIVITYTABLE = datatable (StartTime:datetime, EndTime:datetime, GUID:string, Computername:string) [
datetime(2023-4-1 08:30:00), datetime(2023-4-1 17:30:00), 'machineActive', 'WS01',
datetime(2023-5-1 08:30:00), datetime(2023-5-1 17:30:00), 'machineActive', 'WS01',
datetime(2023-6-1 08:30:00), datetime(2023-6-1 17:30:00), 'machineActive', 'WS02',
datetime(2023-7-1 08:30:00), datetime(2023-7-1 17:30:00), 'machineActive', 'WS03',
datetime(2023-8-1 08:30:00), datetime(2023-8-1 17:30:00), 'machineActive', 'WS04',
datetime(2023-9-1 08:30:00), datetime(2023-9-1 17:30:00), 'machineActive', 'WS04',
datetime(2023-9-1 08:30:00), datetime(2023-9-1 17:30:00), 'machineActive', 'WS03',
datetime(2023-10-1 08:30:00), datetime(2023-10-1 17:30:00), 'machineActive', 'WS05'
];
let events = NOTIFICATIONTABLE
| where TimeStamp > ago(365d)
| where GUID == 'eventNotification'
| project TimeStamp, Notification
| extend JoinHere = 1; //Cross join
let timeslots = ACTIVITYTABLE
| where StartTime > ago(365d)
| where GUID == 'machineActive'
| project StartTime, EndTime, Computername
| extend JoinHere = 1; //Cross join
events
| join kind=leftouter (timeslots) on JoinHere
| where TimeStamp between (StartTime .. EndTime)
| project-away JoinHere*

This should give you output like the following.

TimeStamp	Notification	StartTime	EndTime	ComputerName
2023-09-01T10:30:00.0000000Z	Bad Network Traffic Detected	2023-09-01T08:30:00.0000000Z	2023-09-01T17:30:00.0000000Z	WS04
2023-09-01T10:30:00.0000000Z	Bad Network Traffic Detected	2023-09-01T08:30:00.0000000Z	2023-09-01T17:30:00.0000000Z	WS03
2023-10-01T17:30:00.0000000Z	Bad Network Traffic Detected	2023-10-01T08:30:00.0000000Z	2023-10-01T17:30:00.0000000Z	WS05