KQL Search

name : Azure AD user risk check
description : 
- It helps to identify accessed countries, password reset and change events, and alerts for each product.
table :
- CloudAppEvents, AlertEvidence, AlertInfo, AADSignInEventsBeta
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-cloudappevents-table?view=o365-worldwide
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-alertevidence-table?view=o365-worldwide
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-alertinfo-table?view=o365-worldwide
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-aadsignineventsbeta-table?view=o365-worldwide
query: |
    let Login = CloudAppEvents
    | where Timestamp > ago(30d)
    | where Application == "Office 365"
    | where ActionType in ("Reset user password.", "Change user password.")
    | summarize Reset_PW = countif(ActionType == "Reset user password."), Change_PW =countif(ActionType == "Change user password.") by AccountDisplayName, AccountId;
    let Alerts = AlertEvidence
    | where Timestamp > ago(30d) 
    | where ServiceSource in ("AAD Identity Protection", "Microsoft Defender for Identity", "Microsoft Cloud App Security")
    | where EntityType == "User"
    | where isnotempty(AccountObjectId)
    | join kind=inner AlertInfo on AlertId
    | summarize AAD_Alert = countif(ServiceSource == "AAD Identity Protection"), 
    MDI_Alert = countif(ServiceSource == "Microsoft Defender for Identity"),
    MDA_Alert = countif(ServiceSource == "Microsoft Cloud App Security") by AccountObjectId;
    AADSignInEventsBeta 
    | where Timestamp>ago(30d) 
    | where ApplicationId == "4765445b-32c6-49b0-83e6-1d93765276ca"
    | where ClientAppUsed == "Browser" 
    | where LogonType has "interactiveUser" 
    | summarize Countries = make_set(Country) by AccountObjectId, AccountDisplayName
    | join kind=leftouter Login on $left.AccountObjectId == $right.AccountId
    | join kind=leftouter Alerts on AccountObjectId 
    | project AccountObjectId, AccountDisplayName, Countries, Reset_PW, Change_PW, AAD_Alert, MDI_Alert, MDA_Alert