Looking for KQL query when high volume of USB writes happens by a user

[Sohel68]

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 of data to an external USB drive.

your help is much appreciated.

thanks.

[#2]

In Microsoft Defender for Endpoint, you can use the following KQL query to show a high volume of USB writes by a single user. Modify the Threshold value to define what you consider a "high volume" of USB writes.

DeviceFileEvents
| where ActionType == "FileWrite" and InitiatingProcessFileName == "explorer.exe" and FileName contains ".usb"
| summarize USBWriteCount = count() by AccountName
| where USBWriteCount > Threshold // Replace Threshold with a specific value to define "high volume"
| order by USBWriteCount desc

[#3]

Thank you for quick response.
I just ran the query got error on "Account Name" - see below
"The name 'AccountName' does not refer to any known column, table, variable or function"
---------------
DeviceFileEvents
| where ActionType == "FileWrite" and InitiatingProcessFileName == "explorer.exe" and FileName contains ".usb"
| summarize USBWriteCount = count() by AccountName
| where USBWriteCount > 20 // if someone copies more than 20 files
| order by USBWriteCount desc
------------

any idea?

[#4]

Replace that with InitiatingProcessAccountName.

Here's the schema for that table: https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-devicefileevents-...

[#5]

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
=====================

[#6]

I 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

[#7]

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.

[#8]

The 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

[#9]

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
=============