CODEWITHSUNDEEP
azureazure-data-explorerkql
MarkA
MarkA

Reputation: 11

Implementing an Outer Join using "greater than" operator

I'm writing a Kusto query to return logging events from AppInsights. I have a custom event representing a "Password Reset Request" and this should be followed by a "Password Reset Confirmed" event. But of course a customer can request the reset of their password many times, even within the same hour. I am trying to create a Kusto query that shows me the date/time for all "Password Reset Request" events, along with the date/time of the "Password Reset Confirmed" event that followed it.

I'm using an Outer Join because not all customers will complete the password reset so the "Password Reset Confirmed" event is optional:

customEvents
| where name == "Password Reset Request"
| project CustomerId = tostring(customDimensions.["CustomerId"]), RequestDateTime = timestamp
| join kind = leftouter
    (
    customEvents
    | where name == "Password Reset Confirmed"
    | project CustomerId = tostring(customDimensions.["CustomerId"]), ConfirmDatetime = timestamp
    )
    on CustomerId

The problem with this is that if a customer requests to reset their password twice I get 4 matching records because each of the two "Password Reset Request" events is joining onto the two "Password Reset Confirmed" events.

What I want to do is constrain the outer join with an additional condition so that I only select the records where the Confirm event is after the Request event, something like this:

on CustomerId, $right.ConfirmDatetime > $left.RequestDateTime

But it seems that the Kusto query language only supports join conditions using the equality operator, nothing else. Is it possible to implement this query? I've seen this which talks about time-limiting a query join using bin and mv-expand but I'm looking for something that is not restricted by a time window but simply looks for the corresponding event using a "greater than" operator.

Thanks.

Upvotes: 1

Views: 1400

Answers (1)

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

This can be achieved by adding filter after the join clause:

customEvents
| where name == "Password Reset Request"
| project CustomerId = tostring(customDimensions.["CustomerId"]), RequestDateTime = timestamp
| join kind = leftouter
    (
    customEvents
    | where name == "Password Reset Confirmed"
    | project CustomerId = tostring(customDimensions.["CustomerId"]), ConfirmDatetime = timestamp
    )
    on CustomerId
| where ConfirmDatetime > RequestDateTime

Upvotes: 2

Related Questions