Oct 06 2022 07:53 AM
Oct 06 2022 07:53 AM
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
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,
| 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,
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,
FileName, FolderPath, SHA256, SensitivityLabel, IsAzureInfoProtectionApplied, DriveLetter, MountTime, ProductName, SerialNumber, Manufacturer
| order by DeviceId asc, Timestamp desc
Oct 11 2022 09:09 AMSolution