Query Details
Risky Sign In Keyword Search CISA
Query
//Query is from CISA Playbook https://www.cisa.gov/sites/default/files/2025-01/microsoft-expanded-cloud-logs-implementation-playbook-508c.pdf
let keywordsOfInterest=dynamic(["vpn","password","anyconnect","pfx","credential","credentials","work from home","cisco","palo alto","virtualdesktop","key","secret","confidential"]);
AADSignInEventsBeta
| where RiskLevelDuringSignIn in ("50","100")
| project RiskySignInTime=Timestamp, AccountUpn, RiskLevelDuringSignIn,
SignInIPAddress=IPAddress
| join kind=inner(
CloudAppEvents
| where ActionType in("SearchQueryInitiatedSharePoint","SearchQueryInitiatedExchange")
| extend QueryText=tostring(RawEventData.QueryText)
| extend Workload=tostring(RawEventData.Workload)
| extend UserId=tostring(RawEventData.UserId)
| where QueryText has_any (keywordsOfInterest)
| project SearchTime=Timestamp, UserId, Workload, QueryText, IPAddress
) on $left.AccountUpn==$right.UserId
| extend ['Time Between Risky Sign in and search']=datetime_diff('minute',SearchTime,RiskySignInTime)
| where ['Time Between Risky Sign in and search'] between (-30 .. 30)
| project RiskySignInTime, SearchTime, AccountUpn, Workload, QueryText, ['Time Between Risky Sign in and search'], SignInIPAddress, SearchIPAddress=IPAddress
Explanation
This query is designed to identify potentially suspicious activities by correlating risky sign-ins with specific search queries in cloud applications. Here's a simplified breakdown:
-
Define Keywords: It starts by defining a list of keywords that are of interest, such as "vpn", "password", "credential", etc.
-
Filter Risky Sign-Ins: It looks for sign-in events with a risk level of 50 or 100 from the
AADSignInEventsBetadata. -
Project Relevant Sign-In Data: It extracts relevant information from these risky sign-ins, including the time of the sign-in, the user's account name, the risk level, and the IP address used.
-
Join with Cloud App Events: It then joins this data with
CloudAppEventswhere a user has initiated a search query in SharePoint or Exchange. -
Filter by Keywords: It filters these search queries to find those that contain any of the predefined keywords of interest.
-
Calculate Time Difference: It calculates the time difference between the risky sign-in and the search query, focusing on events that occurred within 30 minutes before or after the sign-in.
-
Project Final Data: Finally, it projects the relevant data, including the times of the risky sign-in and search, the user's account, the workload (application), the search query text, the time difference, and the IP addresses involved.
In essence, this query helps identify if a user who had a risky sign-in also performed a potentially sensitive search within a short time frame, which could indicate suspicious behavior.
Details
Jay Kerai
Released: January 20, 2025
Tables
Keywords
Operators