Event that happened within 1-2 minutes of each other

[WillAda]

I have just started to learn KQL and have started to experiment with some of the tables and trying to work out some simple dynamics of it.  One of the concepts I am trying to get my head around is looking for 2 events that occur within 1 minute of each other.  Both of these events are in different tables and I am now trying to narrow the search down to within that 1 minute window as a proof of concept.

As an example, I have 2 queries with 1 being in the "DeviceRegistryEvents" table and the other being in the "DeviceNetworkEvents" table.  Basically when the network event occurs, the DeviceRegistryEvent occurs within a few seconds after the DeviceNetworkEvent.

My code is

let myquery1 = 
DeviceNetworkEvents
| where RemotePort == "135"
| project Timestamp, DeviceId, DeviceName, ActionType, RemoteIP, RemotePort, RemoteUrl, LocalIP, LocalPort, Protocol, InitiatingProcessCommandLine, InitiatingProcessCreationTime, InitiatingProcessFolderPath, InitiatingProcessAccountDomain, InitiatingProcessAccountName;
let myquery2 = 
DeviceRegistryEvents
| where ActionType  == "RegistryKeyCreated"
| where InitiatingProcessCommandLine contains "myfile.exe"
| project Timestamp, DeviceId, DeviceName, ActionType, RegistryKey, InitiatingProcessAccountDomain,InitiatingProcessAccountName,InitiatingProcessAccountSid, InitiatingProcessFileName, InitiatingProcessVersionInfoInternalFileName, InitiatingProcessVersionInfoOriginalFileName, InitiatingProcessId, InitiatingProcessCommandLine;
myquery1 | union myquery2
| sort by Timestamp

I get my results similar to the following (I have removed some fields from the table as they are not relevant)

Timestamp	DeviceName	RegistryKey	InitiatingProcessCommandLine	RemoteIP	RemotePort
10/26/2021  10:08:38	Server1	HKLM\Test	myfile.exe	192.168.10.1
10/26/2021  09:48:57	Server1	HKLM\Test	myfile.exe	192.168.10.1
10/26/2021  09:48:29	Server2	ConnectionSucccess		192.168.10.2	135
10/25/2021  16:37:20	Server1	HKLM\Test	myfile.exe	192.168.10.1

What I would like to do is only show the events that occurred within about 1 minute of each other.  In this scenario, that is the 09:48:29 and 09:48:57 timestamp as this is the expected behaviour I am looking for.  I then want to convert this into a single event that I can then turn into a custom detection.  

I have tried summarize by timespan(1min) but that doesn't seem to work.  I have also found some articles to do something like

.......
let myquery1 = 
........
| project ......, Start=Timestamp

.......
let myquery2 = 
........
| project ......, End=Timestamp

myquery | union myquery2
| where (End-Start) between (0min .. 1min)

but that tends to fail and I have not been able to proceed.  

[#2 · Accepted solution]

@WillAda

You are merging the tables with the union operator, which only puts all the events in one bucket so to speak. To be able to filter with "where (End-Start)", both the End and Start attributes need to exist in the same event, so you need to use the join operator to join the myquery and myquery2 events together, instead of union.

myquery | union myquery2
| where (End-Start) between (0min .. 1min)

In order to use join you will need a shared attribute that exists in both myquery and myquery2 though.

Maybe something like below.

I think it should work, though I am not sure if it is the ideal solution since I have not done anything like this myself. Also, since deviceID and deviceName etc exist in both tables, you will end up with duplicate attributes so should probably rename the attributes so they are not identical in myquery and myquery2. (for example rename DeviceID to DeviceID-1 in myquery, and DeviceID-2 in myquery2)

.......
let myquery1 = 
........
| project ......, Start=Timestamp, Join="1"

.......
let myquery2 = 
........
| project ......, End=Timestamp, Join="1"

myquery | join kind=inner myquery2 on Join
| where (End-Start) between (0min .. 1min)

[#3]

@Jonhed thanks for that.  I read on some of this more this morning.  I need to get to grips on union vs join in my statements (but will be a learning experience).  As both events log quite differently and what is exposed, I have to make an assumption based on 2 completely unrelated fields.  Thankfully they are just a fully qualified name (server1.domain) and just the host name (server1).  On my RegistryEvent, I will  use the Kusto extract to apply some regex to the field, create the same field as per the NetworkEvent schema and use that for the join.