Query Details
DCA Exchange Online Eventsduring Risky Signin
Query
//Create a pivot table of all actions taken during a risky sign in
//Data connector required for this query - Advanced Hunting license
//First find the SessionId of any medium or high risk sign ins (where risk level is 50 or 100)
//Advanced Hunting query, this query doesn't work in Sentinel because SessionId isn't sent over currently
let riskysignins=
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where RiskLevelDuringSignIn in (50, 100)
| distinct SessionId;
CloudAppEvents
| where Timestamp > ago(7d)
| extend RawEventData = parse_json(RawEventData)
| extend SessionId = RawEventData.SessionId
| where isnotempty(SessionId)
//Match on the risky sign in SessionId from above
| where SessionId in (riskysignins)
| extend Activity = strcat(Application, " - ", ActionType)
//Create pivot table of all actions by each user
| evaluate pivot(Activity, count(), AccountDisplayName)Explanation
This query creates a pivot table that shows all actions taken during risky sign-ins. It first finds the SessionId of medium or high-risk sign-ins within the past 7 days. Then, it retrieves CloudAppEvents data and matches it with the risky sign-in SessionIds. Finally, it creates a pivot table that shows the count of each action taken by each user.
Details
Matt Zorich
Released: June 17, 2022
Tables
AADSignInEventsBetaCloudAppEvents
Keywords
Devices,Intune,User
Operators
whereagoindistinctextendparse_jsonisnotemptystrcatevaluatepivotcount