kqlazure-data-explorer

Kusto - fetch data from one table where matching records do not exist in another table

I have two tables in azure data explorer db, first table is Users and the second one is Heartbeat. Users table has certain fields including email. Heartbeat table has email and Heartbeat send date/time column.

I need to select all the users who are NOT sending heartbeat between a time range.

I am using the following query but it doesn't return the records.

Users
| join kind=leftouter Heartbeat on $left.userEmail == $right.userEmail
| where userEmail1 !has (userEmail)
| where HeartBeatDate between (datetime('2024-07-14 16:04:10')..datetime('2024-07-15 16:04:10'))
| distinct userLoginName,userEmail,userFullName,userPicture,userCreateDate,userPhoneNumber

If I remove where clause of date/time range I get the data but I need to filter and search only between the given time range.

Thanks.

Solution

  • In order to get the user details who did not send their heartbeat details within the specified time zone, you can modify your code as below,

    Heartbeat
| where HeartBeatDate between (datetime('2024-07-14 16:04:10')..datetime('2024-07-15 16:04:10')) //give the timezone here
| distinct userEmail
| join kind=rightouter (Users) on $left.userEmail == $right.userEmail
| where isempty(userEmail)
| project-away userEmail

    demo

    This query first filters the Heartbeat table to include only the rows where the HeartBeatDate is within the specified range. It then selects the distinct userEmail values from the Heartbeat table. These values are then used to perform a rightouter join with the Users table. The result of join includes all the rows from the Users table and only the matching rows from the Heartbeat table. The where clause filters out all the rows where there is a matching row in the Heartbeat table (i.e., where userEmail is not null). Finally, the project-away operator is used to remove the duplicate userEmail column from the output.

    Edit:

    
Heartbeat
| where HeartBeatDate between (datetime('2022-01-01 16:04:10')..datetime('2022-01-03 16:04:10'))//give the timezone here
| distinct userEmail 
| join kind=rightouter (Users) on  $left.userEmail == $right.userEmail
| where isempty(userEmail)
| project-away userEmail
| join kind=leftouter (Geolocation) on $left.userEmail1 == $right.userEmail
| where isempty(userEmail)
| project-away userEmail1