Search code examples
kqlwindows-defender

Why is my KQL Advance query erroring out?

This query is meant to show me a short amount of time where a file was created and deleted/rename itself, but it just throwing an error "An unexpected error occurred during query execution. Please try again in a few minutes.":

DeviceFileEvents
| where Timestamp > ago(3d)
| where ActionType == "FileCreated" or ActionType == "FileRenamed"
| where FileName endswith ".exe"
| join kind=inner (DeviceProcessEvents)
on DeviceId
| where Timestamp1 > ago(3d)
| where FolderPath == FolderPath1
| where InitiatingProcessFileName1 == "explorer.exe" or InitiatingProcessParentFileName1 == "explorer.exe"
| where (Timestamp1 - Timestamp) < timespan(5m) and (Timestamp1 - Timestamp) > timespan(0m)
| extend Difference = Timestamp1 - Timestamp
| project-rename Created=Timestamp, Executed=Timestamp1, ProcessFileName=FileName1, ProcHash=SHA2561
| project Created, PreviousFileName, Executed, Difference, DeviceName, ProcessFileName, FolderPath, AccountName, ProcessCommandLine, ProcHash
| order by Created desc

Rewrote it multiple times but still erroring, it looks like there no syntax error so the query is correct right?

Solution

  • I think its just an temporary problem with computed resources or memory. Your query is running at my side:

    let DeviceFileEvents = datatable(Timestamp: string, ActionType: string, FileName: string, DeviceId: string, FolderPath: string)
[
   "2024-06-30T10:00:00", "FileCreated", "xyz.exe", "A", "C:\\abc\\e",
   "2024-06-30T10:00:01", "FileCreated", "xyz.exe", "B", "C:\\abc\\f",
   "2024-06-30T10:00:02", "FileCreated", "xyz.exe", "C", "C:\\abc\\g",
   "2024-06-30T10:00:03", "FileCreated", "xyz.exe", "C", "C:\\abc\\h",
   "2024-06-30T10:00:04", "FileCreated", "xyz.exe", "D", "C:\\abc\\i",
   "2024-06-30T10:00:05", "FileCreated", "xyz.exe", "D", "C:\\abc\\j",
];
let DeviceProcessEvents = datatable(Timestamp: string, ActionType: string, FileName: string, DeviceId: string, FolderPath: string)
[
   "2024-06-30T10:01:00", "FileCreated", "xyz.exe", "A", "C:\\abc\\e",
   "2024-06-30T10:02:00", "FileCreated", "xyz.exe", "B", "C:\\abc\\f",
   "2024-06-30T10:03:00", "FileCreated", "xyz.exe", "C", "C:\\abc\\g",
   "2024-06-30T10:04:00", "FileCreated", "xyz.exe", "C", "C:\\abc\\h",
   "2024-06-30T10:05:00", "FileCreated", "xyz.exe", "D", "C:\\abc\\i",
   "2024-06-30T10:06:00", "FileCreated", "xyz.exe", "D", "C:\\abc\\j",
];
DeviceFileEvents
| where todatetime(Timestamp) > ago(3d)
| where ActionType == "FileCreated" or ActionType == "FileRenamed"
| where FileName endswith ".exe"
| join kind=inner (DeviceProcessEvents)
on DeviceId
| extend Timestamp = todatetime(Timestamp)
| extend Timestamp1 = todatetime(Timestamp1)
| where Timestamp1 > ago(3d)
| where FolderPath == FolderPath1
| where (Timestamp1 - Timestamp) < timespan(5m) and (Timestamp1 - Timestamp) > timespan(0m)
| extend Difference = Timestamp1 - Timestamp
| project-rename Created=Timestamp, Executed=Timestamp1, ProcessFileName=FileName1
| project Created, Executed, Difference, ProcessFileName, FolderPath
| order by Created desc

    Here is a demo.