Query Details
DCA Find New Events
Query
//Find new events in Defender for Cloud Apps seen in the last week vs the previously 90 days
//Data connector required for this query - M365 Defender - CloudAppEvents
//Microsoft Sentinel query
//First find all the activities from the last 90 days prior to this week
let knownactivities=
CloudAppEvents
| where TimeGenerated > ago(90d) and TimeGenerated < ago (7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
| distinct Activity;
//Find activities from the last week
CloudAppEvents
| where TimeGenerated > ago(7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
//Exclude activities we have already seen
| where Activity !in (knownactivities)
//Find the time the new activity was first seen and how many counts seen this week
| summarize ['First Time Seen']=min(TimeGenerated), Count=count() by Activity
| sort by Count desc
//Advanced Hunting query, only 30 days of data is retained in Advanced Hunting so we can instead look at events new in the last 3 compared to the prior 30, but you can change the times around if needed
//Data connector required for this query - Advanced Hunting license
//First find all the activities from the last 30 days prior to this week
let knownactivities=
CloudAppEvents
| where Timestamp > ago(30d) and Timestamp < ago (3d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
| distinct Activity;
//Find activities from the last week
CloudAppEvents
| where Timestamp > ago(3d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
//Exclude activities we have already seen
| where Activity !in (knownactivities)
//Find the time the new activity was first seen and how many counts seen this week
| summarize ['First Time Seen']=min(Timestamp), Count=count() by Activity
| sort by Count desc Explanation
The query is looking for new events in Defender for Cloud Apps that have been seen in the last week compared to the previous 90 days. It uses a data connector called "M365 Defender - CloudAppEvents" to retrieve the necessary data. The query first identifies all activities from the last 90 days prior to the last week and stores them in a variable called "knownactivities". Then, it retrieves activities from the last week, excluding those that are already known. Finally, it summarizes the results by finding the first time each new activity was seen and the count of how many times it occurred in the last week, sorting the results by count in descending order.
There is also an alternative query for Advanced Hunting, which retains only 30 days of data. In this case, it looks for activities from the last 30 days prior to the last 3 days and compares them to activities from the last 3 days. The rest of the query is similar to the previous one.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators