Skip to content

Instantly share code, notes, and snippets.

@alexmags
Last active December 20, 2022 20:37
Show Gist options
  • Save alexmags/845ce04e23596d4219f9798c2231e008 to your computer and use it in GitHub Desktop.
Save alexmags/845ce04e23596d4219f9798c2231e008 to your computer and use it in GitHub Desktop.
Defender For Endpoint KQL to report on files uploaded to cloud from Edge & Chrome by Sensitivity label. https://blog.alexmags.com/posts/kql-for-file-uploaded-to-cloud/
// https://blog.alexmags.com/tags/kql/
// lookup table for AIP label GUIDs
let AIPLabels=datatable(SensitivityGUID:string,Classification:string,SubClassification:string)
[
// AIP O365 sensitivity label GUID, parent label name, sub label name
"aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee", "Public", "Public",
"ffffffff-gggg-hhhh-iiii-jjjjjjjjjjjj", "Internal", "Internal",
"kkkkkkkk-llll-mmmm-nnnn-oooooooooooo", "Secret", "Secret",
"pppppppp-qqqq-rrrr-ssss-tttttttttttt", "Super secret", "Super secret"
];
// build your AIP label guid lookup table with Compliance Center PowerShell like this:
// foreach ($label in get-label) { Write-Output """$($label.guid)"", ""$($label.ParentLabelDisplayName)"", ""$($label.DisplayName)"",")
// Query CloudAppEvents table in O365 unified log
let Uploads =
(
CloudAppEvents
//| where Timestamp > ago(60d) // last 60 days max
| where ActionType == @"FileUploadedToCloud"
| evaluate bag_unpack(RawEventData,'Event_') // unbundle the JSON in RawEventData
| project Timestamp,ActionType,AccountDisplayName,AccountObjectId,IPAddress,ISP,IPCategory,IPTags,tostring(Event_Application),Event_ObjectId, FileName=parse_path(Event_ObjectId).Filename,tostring(Event_DeviceName),tostring(Event_FileExtension),tostring(Event_FileType),Event_FileSize,Event_TargetDomain,AccountUpn=Event_UserId,Event_Workload,SensitivityGUID=tostring(Event_SensitivityLabelEventData.SensitivityLabelId)
// you might want other columns. Explore the CloudAppEvents data yourself. Parsing file path to filename was useful
// https://docs.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-cloudappevents-table?view=o365-worldwide
);
// Lookup AIP labels in uploads. Adds the two extra sensitivity columns to results table
Uploads | lookup kind=leftouter AIPLabels on SensitivityGUID
// filter the joined up data
// filter out approved file storage domains
| where Event_TargetDomain !in~ (
"companyname.sharepoint.com", // SharePoint Online tenant
"companyname-my.sharepoint.com", // SharePoint Online tenant for OneDrive
"companyname.largefilesender.com", // some large file transfer system
"resourcedomain.companyname.com" // intranet sites
)
// Enrich results with Azure AD user account properties
| join (IdentityInfo | distinct *) on AccountObjectId // joining on AccountUPN unreliable because of case sensitivity. ObjectID better.
// Filter on user/domain and/or classification
//| where AccountUpn =~ 'suspect@companyname.com' // case insensitive compare
//| where Event_TargetDomain contains 'whatsapp'
//| where Classification in ('Super secret','Top secret')
// summarize data and make charts if you like
//| summarize count() by bin(Timestamp, 1d)
//| render timechart
// make a bar chart by upload domain
//| summarize count() by Event_TargetDomain
//| order by count_ desc
//| render columnchart
// make a bar chart by Country
//| summarize count() by Country
//| order by count_ desc
//| render columnchart
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment