Forum Discussion
Looking for KQL query when high volume of USB writes happens by a user
Hello, I did some online search, but I couldn't find any working one yet. I'm looking for query which I can use in Advance threat hunting in MDE to generate an alert when a user copies huge number...
thank you again. so that seems to do the trick but I'm not getting any results, even when I changed the value to "1" file.
I'm looking to see if someone copies more than 20 files in last 24 hrs.
==========================
DeviceFileEvents
| where ActionType == "FileWrite" and InitiatingProcessFileName == "explorer.exe" and FileName contains ".usb"
| summarize USBWriteCount = count() by InitiatingProcessAccountName
| where USBWriteCount > 1
| order by USBWriteCount desc
=====================
I'm looking to see if someone copies more than 20 files in last 24 hrs.
==========================
DeviceFileEvents
| where ActionType == "FileWrite" and InitiatingProcessFileName == "explorer.exe" and FileName contains ".usb"
| summarize USBWriteCount = count() by InitiatingProcessAccountName
| where USBWriteCount > 1
| order by USBWriteCount desc
=====================
Rod_Trent
Microsoft
MicrosoftI don't have a lot of USB data in my tenant and KQLSearch.com doesn't have much for this. Try the following (filemodified instead of filewrite):
DeviceFileEvents
| where ActionType == "FileModified"
| summarize USBWriteCount = count() by InitiatingProcessAccountName
| where USBWriteCount > 1
| order by USBWriteCount desc
DeviceFileEvents
| where ActionType == "FileModified"
| summarize USBWriteCount = count() by InitiatingProcessAccountName
| where USBWriteCount > 1
| order by USBWriteCount desc
- ok, this seems to return some values, so thank you again.
Do you know if this goes back to last 24 hours? curious since I'm seeing huge file modified action by number of users, for example over 4K files by 30+ users.- Rod_TrentThe default is usually 24 hours, but you can set it in the query. Here it is for the past 2 days...
DeviceFileEvents
| where Timestamp > ago(2d)
| where ActionType == "FileModified"
| summarize USBWriteCount = count() by InitiatingProcessAccountName
| where USBWriteCount > 1
| order by USBWriteCount desc- Query seems to return lot less this time; however, number doesn't match when I go to "Microsoft Purview -> DLP -> Activity Explorer", where I set a filter to show all activities related to "FileCopiedtoRemovableMedia".
I got below query from online searching, it fails with "'summarize' operator: Failed to resolve scalar expression named 'UserId'"...any idea how to fix it? sorry, I'm not a KQL expert.
thanks again.
====
DeviceFileEvents
| where ActionType == "FileCopiedToRemovableMedia"
| summarize FileCount = count() by DeviceId, UserId
| where FileCount >= 20
| join kind = inner (
DeviceInfo
| project DeviceId, DeviceName
) on DeviceId
| join kind = inner (
DeviceUser
| project UserId, UserDisplayName
) on UserId
| project DeviceName, UserDisplayName, FileCount
=============