WDATP Alert detection query

[DKTimGjerlufsen]

Hi Community

I really need some help trying to build this query correct in KQL. The Query is reporting users who has created files onto a drive that is not the local C:\

I try to detect and alert if users exfiltrate data into an external device like USB or removeable harddisk. The alert should get triggered if more that 50 files are exfiltrated (can bechanged i know).

The query:

DeviceFileEvents
| where InitiatingProcessAccountName in ((
DeviceFileEvents
| where ActionType == "FileCreated"
| where InitiatingProcessAccountName startswith "w"
| where InitiatingProcessAccountName != "webiadmin"
| where FolderPath !startswith @'C:\'
| where FolderPath !startswith @'\Device\Harddisk'
| where FolderPath !startswith @'\\'
| where FolderPath !contains "privat"
| where FileName !contains "privat"
| project LeftInitiatingProcessAccountName = InitiatingProcessAccountName, LeftDeviceName = DeviceName
| summarize count() by LeftInitiatingProcessAccountName, LeftDeviceName
| where count_ > 50))
| distinct DeviceName, InitiatingProcessAccountName

The problem here is the query works just fine, but in order to run is as a detection rule, the columns Timestamp, DeviceId and ReportId must be included. adding those to the existing query result in a Cartchic product. 

Any help is much appreciated.

Best regards Tim Gjerlufsen

[#2]

@DKTimGjerlufsen I was able to create a detection rule based on this KQL Query:

DeviceEvents

| where Timestamp > ago(1d)

| where ActionType == "UsbDriveMount"

| project USBMountTime = Timestamp, DeviceId, AdditionalFields

| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)

| join (

DeviceFileEvents

| where Timestamp > ago(1d)

| where ActionType == "FileCreated"

| parse FolderPath with DriveLetter '\\' *

| extend DriveLetter = tostring(DriveLetter)

)

on DeviceId, DriveLetter

| where (Timestamp - USBMountTime) between (0min .. 15min)

| summarize DistinctFilesCopied = dcount(SHA1), Events=makeset(pack("AccountName", InitiatingProcessAccountName, "Timestamp", Timestamp, "ReportId", ReportId, "FileName", FileName, "AdditionalDriveProperties", AdditionalFields)) by DeviceId, bin(Timestamp, 15m)

| where DistinctFilesCopied > 10

| mv-expand Events

| extend Timestamp = Events.Timestamp, FileName = Events.FileName, AccountName = Events.AccountName, ReportId = Events.ReportId, AdditionalDriveProperties = Events.AdditionalDriveProperties