KQL Search

Query Details

HomeAI ToolsDevice Query

Device Visualize Volumeof Data Copiedto USB

Query

//Visualize how much data is being copied to USB drives per day in your environment over the time range.

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where TimeGenerated > ago (21d)
| project TimeGenerated, ActionType, AdditionalFields, DeviceId, FileName
| where ActionType == "UsbDriveMounted"
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner (DeviceFileEvents
    | where TimeGenerated > ago (21d)
    | project TimeGenerated, ActionType, FolderPath, DeviceId, FileName, FileSize
    | extend FileCopyTime = TimeGenerated
    | where ActionType == "FileCreated"
    | parse FolderPath with DriveLetter '\\' *
    | extend DriveLetter = tostring(DriveLetter)
    )
    on DeviceId, DriveLetter
| distinct FileCopyTime, FileName1, FileSize
| summarize DataCopiedinGB=sum(FileSize / 1024 / 1024 / 1024) by startofday(FileCopyTime)
| render columnchart
    with (
    kind=unstacked,
    xtitle="Data Copied in GB",
    ytitle="Day",
    title="Data Copied to USB per day")

Explanation

This query visualizes the amount of data being copied to USB drives in your environment on a daily basis. It uses the M365 Defender - Device* tables as the data source. The query retrieves events where USB drives are mounted and files are created, and then joins them based on the device ID and drive letter. It calculates the total data copied in gigabytes for each day and presents the results in a column chart. The chart shows the data copied to USB drives per day over the specified time range.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

DeviceEventsDeviceFileEvents

Keywords

DeviceEvents,DeviceFileEvents,TimeGenerated,ActionType,AdditionalFields,DeviceId,FileName,UsbDriveMounted,DriveLetter,FileCopyTime,FolderPath,FileSize,FileCreated,DataCopiedinGB,startofday,render,columnchart,kind,unstacked,xtitle,ytitle,title

Operators

whereprojectextendjoinondistinctsummarizerenderwithagotostringtodynamicparsebystartofdaycolumnchartunstackedxtitleytitletitle

Actions