User Profile
WillAda
Copper Contributor
Joined Oct 26, 2021
User Widgets
Recent Discussions
Kusto Combine to then Join
Is it possible to output 2 tables to then use in another join with a different query? For example say I have this let de1= DeviceNetworkEvents | where DeviceName contains "server1" | where ProcessId == "111"; let de2= DeviceNetworkEvents | where DeviceName contains "server1" | where ConnectionString == "Connection Successful"; de1 | union de2 by Something Can I then take the combined output of this and use the details from this to then do another union with a different table?2KViews0likes1CommentRe: Event that happened within 1-2 minutes of each other
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.3.6KViews0likes0CommentsEvent that happened within 1-2 minutes of each other
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.Solved4KViews0likes2Comments
Recent Blog Articles
No content to show