Forum Discussion
Event that happened within 1-2 minutes of each other
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)
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)
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.