KQL Query - logins after a url click

Question

I am trying to build a query that will show user logons after a successful url click.

I want to be able to add the email and recipient and then show if the recipient clicked on the url within the email and then list, for example, the next 10 logins performed by the user that performed the click.

I think I am almost there.

Wierdly everytime I run this I get different results. Can anyone assist?

EmailEvents
| where SenderFromAddress contains "[email protected]"
| where RecipientEmailAddress contains "bob.smith"
| project TimeEmailRecieved = Timestamp, Subject, SenderFromAddress, AccountName =       tostring(split(RecipientEmailAddress, "@")[0]), NetworkMessageId
| join ( 
UrlClickEvents 
| where AccountUpn contains "bob.smith"
| where Workload == "Email"
| where ActionType == "ClickAllowed" or IsClickedThrough != "0"
| project Url, UrlChain, ActionType, NetworkMessageId, TimeOfClick = Timestamp ) on NetworkMessageId
| join (
//Merge EmailEvents table with IdentityLogonEvents table a
IdentityLogonEvents
| project UserLogons = Timestamp, AccountName, DeviceName, IPAddress, Location, LogonType
) on AccountName
//Check any logons within 30 minutes of the user recieving the email
| where (UserLogons - TimeOfClick) between (0min.. 15m)

I think I am almost there,

Wierdly everytime I run this I get different results. Can anyone assist?

Answer 1

I can't reproduce the KQL you have because I have no access to the data, but there is a smart way to calculate between timestamps. I think you can move on from here using these extend after your query:

| extend minutes_ago = datetime_diff('minute', UserLogons, TimeOfClick)
| extend clicked_in_15min = minutes_ago < 15

You'd have to check the output of the column minutes_ago because this either might be negative or positive. According to this, change the clicked_in_15_min to the right boolean condition.