Remove Duplicate USB user name events from Advance Hunting Query, 365 Defender Portal

[AmjadGov]

Hi,

I posted this a while back, and it was resolved, but then noticed for larger clients the duplicates are back :(

Topic:  Microsoft Defender for Endpoint Device Control Removable Storage

Section: MS 365 Defender Portal / Advance Hunting

To clarify:

What I would possibly like to achieve, is to have a list of all users that have used removable media (by domain name) for the last 30 days, but only 1 row per user, so that I can maximise the 10,000 row limitation when I export from 365 Defender to Excel. At the moment, due to duplicates, I am currently only seeing about 15/20 users, with instances of some users with over 8000 events. 

The end goal here is the really just see the type of Removeable Media Device used and not the files they are copying.

Also, if possible to list the USBSTOR label of that Device as well.

The script that I am using is as follows: (I was given an alteration on line '| summarize arg_max(MountTime, *) by FileName' which helped somewhat, but now I would really like help to solve the above.

Any help will be much appreciated.

let UsbDriveMount = DeviceEvents

| where ActionType=="UsbDriveMounted"

| extend ParsedFields=parse_json(AdditionalFields)

| project DeviceId, DeviceName, DriveLetter=ParsedFields.DriveLetter, MountTime=Timestamp,

ProductName=ParsedFields.ProductName,SerialNumber=ParsedFields.SerialNumber,Manufacturer=ParsedFields.Manufacturer

| order by DeviceId asc, MountTime desc;

let FileCreation = DeviceFileEvents

| where InitiatingProcessAccountName != "system"

| where ActionType == "FileCreated"

| where FolderPath !startswith "C:\\"

| where FolderPath !startswith "\\"

| where InitiatingProcessAccountDomain == "<DomainName>"

| project ReportId,DeviceId,InitiatingProcessAccountDomain,

InitiatingProcessAccountName,InitiatingProcessAccountUpn,

FileName, FolderPath, SHA256, Timestamp, SensitivityLabel, IsAzureInfoProtectionApplied

| order by DeviceId asc, Timestamp desc;

FileCreation | lookup kind=inner (UsbDriveMount) on DeviceId

| where FolderPath startswith DriveLetter

| where Timestamp >= MountTime

| summarize arg_max(MountTime, *) by FileName, tostring(ProductName), tostring(SerialNumber)

| project Timestamp, DeviceId, DeviceName, InitiatingProcessAccountDomain,

InitiatingProcessAccountName,InitiatingProcessAccountUpn,

FileName, FolderPath, SHA256, SensitivityLabel, IsAzureInfoProtectionApplied, DriveLetter, MountTime, ProductName, SerialNumber, Manufacturer

| order by DeviceId asc, Timestamp desc

Cheers

[#2 · Accepted solution]

so basically, what you want is something similar to the below.
I do not quite know how the actual output of your query looks so I cant tell you what needs to go and what needs to stay, but maybe this will give you a hint.

| summarize make_set(SerialNumber) by InitiatingProcessAccountUpn

This would give you one entry per UPN, and then an array of unique serial numbers of the drives said user has used.

If you need info from multiple fields and not just the Serial Numbers, you can append multiple values like below.
| extend StorageInfo = strcat_delim(';', SerialNumber,Manufacturer)
| summarize make_set(StorageInfo) by InitiatingProcessAccountUpn

Not exactly what you requested, but if you want to 1 entry per user and unique device (if 1 user used 3 different devices, you get 3 entries) you can do it like below.
| distinct InitiatingProcessAccountUpn,SerialNumber,Manufacturer