Last active
December 20, 2022 20:37
-
-
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/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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