Splunk ESCU

//Calculate the percentage for all your Azure AD users considered risky. Those requiring single factor authentication, coming from an unknown location and from an unknown device

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
//Include only member accounts if you want to ignore guest signins
| where UserType == "Member"
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| summarize
    ['Total Signins']=count(),
    ['At Risk Signins']=countif(NetworkLocationDetails == '[]' and isempty(DeviceTrustType) and AuthenticationRequirement == "singleFactorAuthentication")
    by UserPrincipalName
| extend ['At Risk Percentage']=(todouble(['At Risk Signins']) * 100 / todouble(['Total Signins']))
| sort by ['At Risk Percentage'] desc

//Find which users are failing the most Conditional Access policies, retrieve the total failure count, distinct policy count and the names of the failed policies

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (30d)
| project TimeGenerated, ConditionalAccessPolicies, UserPrincipalName
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize
    ['Total Conditional Access Failures']=count(),
    ['Distinct Policy Failure Count']=dcount(CAPolicyName),
    ['Policy Names']=make_set(CAPolicyName)
    by UserPrincipalName
| sort by ['Distinct Policy Failure Count'] desc

//Create a pivot table showing all conditional access policy outcomes over the last 30 days

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| extend CA = parse_json(ConditionalAccessPolicies)
| mv-expand bagexpansion=array CA
| evaluate bag_unpack(CA)
| extend
    ['CA Outcome']=tostring(column_ifexists('result', "")),
    ['CA Policy Name'] = column_ifexists('displayName', "")
| evaluate pivot(['CA Outcome'], count(), ['CA Policy Name'])

//Find Azure AD conditional access policies that have no hits for 'success' or 'failure' over the last month

//Data connector required for this query - Azure Active Directory - Signin Logs

//Check that these policies are configured correctly or still required
SigninLogs
| where TimeGenerated > ago (30d)
| project TimeGenerated, ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend ['Conditional Access Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| summarize ['Conditional Access Result']=make_set(CAResult) by ['Conditional Access Policy Name']
| where ['Conditional Access Result'] !has "success"
    and ['Conditional Access Result'] !has "failure"
    and ['Conditional Access Result'] !has "unknownFutureValue"
| sort by ['Conditional Access Policy Name'] asc

//Create a daily summary of Azure Active Directory group additions

//Data connector required for this query - Azure Active Directory - Audit Logs

let timerange=7d;
AuditLogs
| where TimeGenerated > ago (timerange)
| where OperationName == "Add member to group"
| extend Type = tostring(TargetResources[0].type)
| where Type == "User"
| extend ['Group Name'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend UserAdded = tostring(TargetResources[0].userPrincipalName)
| where isnotempty(UserAdded)
| summarize ['Users Added']=make_set(UserAdded) by ['Group Name'], startofday(TimeGenerated)
| sort by ['Group Name'] asc, TimeGenerated desc

//Detect when a user flags 3 or more distinct Azure AD risk events within a single day

//Data connector required for this query - Azure Active Directory - AAD User Risk Events

AADUserRiskEvents
| where TimeGenerated > ago(7d)
| where RiskState != "dismissed"
| summarize
    ['Distinct count of risk events']=dcount(RiskEventType),
    ['List of risk events']=make_set(RiskEventType)
    by UserPrincipalName, bin(TimeGenerated, 1d)
| where ['Distinct count of risk events'] >= 3

//Detects users who have accessed Azure AD Management interfaces, such as Azure AD PowerShell or Graph Explorer, who have not accessed in the previous timeframe. 

//Data connector required for this query - Azure Active Directory - Signin Logs

//Add additional applications to include them in the same query, i.e Defender for Cloud Apps portal.
//Select a time frame to look back on, i.e find users logging on for the first time today not seen in the prior 60 days
let timeframe = startofday(ago(60d));
let applications = dynamic(["Azure Active Directory PowerShell", "Microsoft Azure PowerShell", "Graph Explorer", "ACOM Azure Website", "Azure Portal", "Azure Advanced Threat Protection"]);
SigninLogs
| where TimeGenerated > timeframe and TimeGenerated < startofday(now())
| where AppDisplayName in (applications)
| project UserPrincipalName, AppDisplayName
| join kind=rightanti
    (
    SigninLogs
    | where TimeGenerated > startofday(now())
    | where AppDisplayName in (applications)
    )
    on UserPrincipalName, AppDisplayName
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, ResultType, AppDisplayName, IPAddress, Location, UserAgent

//Detect potential device code phishing by finding sign ins with both a error 50199 (additional approval required) and error code 0 (success)

//Depending on the size of your tenant - or if you have developers or devices using these flows you may get false positives. 
//The second query looks for new UserPrincipalNames triggering this sign on flow not previously seen in the last 30 days
//The third query searches for a new combination of both UserPrincipalName AND IPAddress not seen in the last 30 days

//Data connector required for this query - Azure Active Directory - Signin Logs

let suspiciousids=
SigninLogs
| where TimeGenerated > ago (1d)
| where ResultType in (0,50199)
| summarize Results=make_set(ResultType) by CorrelationId
| where Results has_all (0, 50199)
| distinct CorrelationId;
SigninLogs
| where CorrelationId in (suspiciousids)
| project TimeGenerated, UserPrincipalName, Location, IPAddress, UserAgent, ResultType


let knownusers=
SigninLogs
| where TimeGenerated > ago (30d) and TimeGenerated < ago(1d)
| where ResultType in (0,50199)
| summarize Results=make_set(ResultType) by CorrelationId, UserPrincipalName
| where Results has_all (0, 50199)
| distinct UserPrincipalName;
let suspiciousids=
SigninLogs
| where TimeGenerated > ago (1d)
| where ResultType in (0,50199)
| summarize Results=make_set(ResultType) by CorrelationId, UserPrincipalName
| where Results has_all (0, 50199)
| where UserPrincipalName !in (knownusers)
| distinct CorrelationId;
SigninLogs
| where CorrelationId in (suspiciousids)
| project TimeGenerated, UserPrincipalName, Location, IPAddress, UserAgent, ResultType


let suspiciousids=
SigninLogs
| where TimeGenerated > ago (30d) and TimeGenerated < ago(1d)
| where ResultType in (0, 50199)
| summarize Results=make_set(ResultType) by CorrelationId, UserPrincipalName, IPAddress
| where Results has_all (0, 50199)
| distinct UserPrincipalName, IPAddress
| join kind=rightanti (
    SigninLogs
    | where TimeGenerated > ago (1d)
    | where ResultType in (0, 50199)
    | summarize Results=make_set(ResultType) by CorrelationId, UserPrincipalName, IPAddress
    | where Results has_all (0, 50199)
    ) 
    on UserPrincipalName, IPAddress
    | distinct CorrelationId;
SigninLogs
| where CorrelationId in (suspiciousids)
| project TimeGenerated, UserPrincipalName, Location, IPAddress, UserAgent, ResultType

//Find Azure AD applications that have had no signins for over 30 days. May be a sign of an app no longer in use or users bypassing SSO.

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (365d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
| project
    AppDisplayName,
    ['Last Logon Time']=TimeGenerated,
    ['Days Since Last Logon']=datetime_diff("day", now(), TimeGenerated)
| where ['Days Since Last Logon'] > 30

//Calculate the percentage of signins failing against each of your Conditional Access policies. If the percentage is high it may be worth evaulating the policy if it is fit for purpose.

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| project ConditionalAccessPolicies
| extend CA = parse_json(ConditionalAccessPolicies)
| mv-expand bagexpansion=array CA
| extend ['CA Policy Name'] = tostring(CA.displayName)
| extend  ['CA Outcome'] = tostring(CA.result)
| summarize
    ['Total Signin Count']=count(),
    ['Total Failed Count']=countif(['CA Outcome'] == "failure")
    by ['CA Policy Name']
| extend ['Failed Percentage'] = todouble(['Total Failed Count']) * 100 / todouble(['Total Signin Count'])
| project-reorder
    ['CA Policy Name'],
    ['Total Signin Count'],
    ['Total Failed Count'],
    ['Failed Percentage']
| order by ['Failed Percentage']

//Find the guests in your tenant connecting to the most applications. They are the biggest risk and the best target for additional controls like Conditional Access.

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where UserType == "Guest"
//Exclude the Microsoft apps for guest account management
| where AppDisplayName !in ("My Apps", "Microsoft App Access Panel", "My Access", "My Profile", "Microsoft Invitation Acceptance Portal")
| summarize
    ['Count of Applications']=dcount(AppDisplayName),
    ['List of Application']=make_set(AppDisplayName)
    by UserPrincipalName
| sort by ['Count of Applications'] desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 0
| where IsGuestUser == 1
//Exclude the Microsoft apps for guest account management
| where Application  !in ("My Apps", "Microsoft App Access Panel", "My Access", "My Profile", "Microsoft Invitation Acceptance Portal")
| summarize
    ['Count of Applications']=dcount(Application),
    ['List of Application']=make_set(Application)
    by AccountUpn
| sort by ['Count of Applications'] desc

//Find Managed Identity service principals that have not successfully signed in in the last 30 days, for each Managed Identity list the Azure resources it has accessed
//Hopefully it means the resource has already been decommissioned, if not, check to see if it still requires the access it has been granted

//Data connector required for this query - Azure Active Directory - Managed Identity Signin Logs

//First find any Managed Identities that haven't successfully signed on for 30 days
AADManagedIdentitySignInLogs
| where TimeGenerated > ago(365d)
| where ResultType == "0"
| summarize arg_max(TimeGenerated, *) by AppId
| extend ['Days Since Last Signin'] = datetime_diff("day", now(), TimeGenerated)
| project
    ['Last Sign in Time']=TimeGenerated,
    ServicePrincipalName,
    ServicePrincipalId,
    ['Days Since Last Signin'],
    AppId
| where ['Days Since Last Signin'] > 30
//Join that list of Managed Identities back to the sign in data and retrieve the Azure resources (such as Key Vault or Storage) it has accessed
| join kind=inner (
    AADManagedIdentitySignInLogs
    | where TimeGenerated > ago(365d)
    | where ResultType == "0"
    | summarize ['Resources Accessed']=make_set(ResourceDisplayName) by AppId)
    on AppId
| project-reorder
    ['Last Sign in Time'],
    ['Days Since Last Signin'],
    ServicePrincipalName,
    ServicePrincipalId,
    AppId,
    ['Resources Accessed']
| order by ['Days Since Last Signin'] desc

//Find Azure AD Service Principals that have not successfully signed on for the last 30 days

//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs

AADServicePrincipalSignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
| project
    ['Last Successful Logon']=TimeGenerated,
    ServicePrincipalName,
    ServicePrincipalId,
    AppId
| join kind = leftanti (
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(30d)
    | where ResultType == 0
    | summarize arg_max(TimeGenerated, *) by AppId
    )
    on AppId
| extend ['Days Since Last Logon']=datetime_diff("day", now(), ['Last Successful Logon'])
| project-reorder ['Days Since Last Logon'], ['Last Successful Logon'], ServicePrincipalName, AppId, ServicePrincipalId
| sort by ['Last Successful Logon'] desc

//Find sign ins that have triggered multiple unique conditional access policy successes - maybe a chance to rationalize policy

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (30d)
| mv-apply ca=todynamic(ConditionalAccessPolicies) on (
    where ca.result == "success"
    | extend PolicyName = tostring(ca.displayName)
    )
| summarize
    ['Count of Poicies Applied']=dcount(PolicyName),
    ['List of Policies Applied']=make_set(PolicyName)
    by CorrelationId, UserPrincipalName
| where ['Count of Poicies Applied'] >= 2

//Find new applications your users are signing into in the last month vs the previous 6 months. For each find the first time the app was used, how many total signins and distinct users accessing each one

//Data connector required for this query - Azure Active Directory - Signin Logs

let knownapps=
    SigninLogs
    | where TimeGenerated > ago(180d) and TimeGenerated < ago (30d)
    | distinct AppId;
SigninLogs
| where TimeGenerated > ago(30d)
| where AppId !in (knownapps)
| where isnotempty(AppDisplayName)
| summarize
    ['First Time Seen']=min(TimeGenerated),
    Count=count(),
    ['User Count']=dcount(UserPrincipalName)
    by AppDisplayName
| sort by Count desc

//Find users who have successfully signed into Azure AD from 3 or more countries in the same day

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where isnotempty(Location)
| summarize
    ['Count of countries']=dcount(Location),
    ['List of countries']=make_set(Location)
    by UserPrincipalName, bin(TimeGenerated, 1d)
| where ['Count of countries'] >= 3

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Timestamp > ago(7d)
| where ErrorCode == 0
| where isnotempty(Country)
| summarize
    ['Count of countries']=dcount(Country),
    ['List of countries']=make_set(Country)
    by AccountUpn, bin(Timestamp, 1d)
| where ['Count of countries'] >= 3

//Find users who are only using text message as their MFA method

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
//You can exclude guests if you want, they may be harder to move to more secure methods, comment out the below line to include all users
| where UserType == "Member"
| mv-expand todynamic(AuthenticationDetails)
| extend ['Authentication Method'] = tostring(AuthenticationDetails.authenticationMethod)
| where ['Authentication Method'] !in ("Previously satisfied", "Password", "Other")
| where isnotempty(['Authentication Method'])
| summarize
    ['Count of distinct MFA Methods']=dcount(['Authentication Method']),
    ['List of MFA Methods']=make_set(['Authentication Method'])
    by UserPrincipalName
//Find users with only one method found and it is text message
| where ['Count of distinct MFA Methods'] == 1 and ['List of MFA Methods'] has "text"

//Create a temporary table of first party apps from the following sources which are updated daily in the following order
//        1. Microsoft Graph (apps where appOwnerOrganizationId is Microsoft)
//        2. Microsoft Learn doc (https://learn.microsoft.com/troubleshoot/azure/active-directory/verify-first-party-apps-sign-in)
//        3. Custom list of apps (./customdata/MysteryApps.csv) - Community contributed list of Microsoft apps and their app ids
//You can then look up / join this to any data that does not have the friendly name or just use it as a reference
//See https://github.com/merill/microsoft-info for the reference source

let FirstPartyApps = externaldata (AppId:guid,AppDisplayName:string,AppOwnerOrganizationId:guid,Source:string) [
    h@'https://raw.githubusercontent.com/merill/microsoft-info/main/_info/MicrosoftApps.json'
    ] with(format='multijson');
FirstPartyApps

//Find users that have connected successfully via legacy auth for the first time
//First find users with existing successful legacy auth connections

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel query
let knownusers=
    SigninLogs
    | where TimeGenerated > ago(90d) and TimeGenerated < ago(1d)
    | where ResultType == 0
    | where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
    | distinct UserPrincipalName;
//Find any new connections in the last day from users not in the existing list
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
| where isnotempty(ClientAppUsed)
| where UserPrincipalName !in (knownusers)
| distinct UserPrincipalName, AppDisplayName, ClientAppUsed, IPAddress

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

//First find users with existing successful legacy auth connections. Advanced Hunting only stores 30 days of data, but otherwise the same query works
let knownusers=
    AADSignInEventsBeta
    | where Timestamp > ago(30d) and Timestamp < ago(1d)
    | where LogonType == @"[""interactiveUser""]"
    | where ErrorCode == 0
    | where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
    | where isnotempty(ClientAppUsed)
    | distinct AccountUpn;
//Find any new connections in the last day from users not in the existing list
AADSignInEventsBeta
| where Timestamp > ago(1d)
| where LogonType == @"[""interactiveUser""]"
| where ErrorCode == 0
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
| where isnotempty(ClientAppUsed)
| where AccountUpn !in (knownusers)
| distinct AccountUpn, Application, ClientAppUsed, IPAddress

//Detect when a user adds someone to an Azure AD privileged role for the first time

//Data connector required for this query - Azure Active Directory - Audit Logs

//First build a set of known users who have completed this action previously
let knownusers=
    AuditLogs
    | where TimeGenerated > ago(90d) and TimeGenerated < ago(1d)
    | where OperationName == "Add member to role"
    //Exclude role additions made by the Azure AD PIM service
    | where Identity != "MS-PIM"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | distinct Actor;
//Find events in the last day by users not in the known list
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName == "Add member to role"
| where Identity != "MS-PIM"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Azure AD Role Name'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend Target = tostring(TargetResources[0].userPrincipalName)
| where Actor !in (knownusers)
| project TimeGenerated, Actor, Target, ['Azure AD Role Name']

//Detect the first time a service principal fails Conditional Access

//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs

//Microsoft Sentinel query
//First find service principals that have previously failed
let knownfailures=
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(30d) and TimeGenerated < ago (1d)
    | where ResultType == "53003"
    | distinct AppId;
//Find any new failures in the last day
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(1d)
| where ResultType == "53003"
| where AppId !in (knownfailures)
| project
    TimeGenerated,
    ServicePrincipalName,
    ServicePrincipalId,
    AppId,
    ConditionalAccessPolicies,
    IPAddress

//Detect the first time a service principal fails Conditional Access

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

//Advanced Hunting query
let knownfailures=
    AADSpnSignInEventsBeta
    | where Timestamp > ago(30d) and Timestamp < ago (1d)
    | where ErrorCode == "53003"
    | distinct ApplicationId;
AADSpnSignInEventsBeta
| where Timestamp > ago(1d)
| where ErrorCode == "53003"
| where ApplicationId  !in (knownfailures)
| project
    Timestamp,
    ServicePrincipalName,
    ServicePrincipalId,
    ApplicationId,
    IPAddress

//Detects when an Azure AD guest is added to an Azure AD role

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Add member to role"
| extend Target = tostring(TargetResources[0].userPrincipalName)
| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where Target contains "#ext#"
| project TimeGenerated, OperationName, Actor, Target, RoleAdded

//Visualizes the total guest invites sent from your Azure AD tenant vs those redeemed. Data is summarized per week.

//Data connector required for this query - Azure Active Directory - Audit Logs

let timerange=180d;
AuditLogs
| where TimeGenerated > ago (timerange)
| where OperationName in ("Redeem external user invite", "Invite external user")
| summarize
    InvitesSent=countif(OperationName == "Invite external user"),
    InvitesRedeemed=countif(OperationName == "Redeem external user invite")
    by startofweek(TimeGenerated)
| render columnchart
    with (
    title="Guest Invites Sent v Guest Invites Redeemed",
    xtitle="Invites",
    kind=unstacked)

//Adds logic to your SigninLogs to determine whether guest authentications are inbound (guests accessing your tenant) or outbound (your users accessing other tenants)

//Data connector required for this query - Azure Active Directory - Sign in Logs

SigninLogs
| where TimeGenerated > ago (1d)
| where UserType == "Guest"
| project TimeGenerated, UserPrincipalName, AppDisplayName, ResultType, IPAddress, HomeTenantId, ResourceTenantId, AADTenantId
| extend ['Guest Type']=case(AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId, strcat("Inbound Guest"),
                             AADTenantId == HomeTenantId and ResourceTenantId != AADTenantId, strcat("Outbound Guest"),
"unknown")

//Find when inbound Azure AD guests access applications for the first time

//Data connector required for this query - Azure Active Directory - Signin Logs

//First find applications that have previously had Azure AD guest signins
let knownapps=
    SigninLogs
    | where TimeGenerated > ago (90d) and TimeGenerated < ago(7d)
    | where ResultType == 0
    | where UserType == "Guest"
//Include only inbound guests (guests accessing your tenant)
    | where AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId
    | distinct AppDisplayName;
//Lookup signins from the last week and find guest sign ins to applications not on the known list
SigninLogs
| where TimeGenerated > ago (7d)
| where ResultType == 0
| where UserType == "Guest"
| where AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId
| where AppDisplayName !in (knownapps)
//Summarize the access to those applications by time first seen and who is accessing each application
| summarize
    ['First Logon Time']=min(TimeGenerated),
    ['Total Guest Signins']=count(),
    ['Distinct Guest Signins']=dcount(UserPrincipalName),
    ['List of Guest Users']=make_set(UserPrincipalName)
    by AppDisplayName

//Lists guests who have been invited but not yet redeemed their invites.

//Data connector required for this query - Azure Active Directory - Audit Logs

//Excludes newly invited guests (last 30 days).
let timerange=365d;
let timeframe=30d;
AuditLogs
| where TimeGenerated between (ago(timerange) .. ago(timeframe)) 
| where OperationName == "Invite external user"
| extend GuestUPN = tolower(tostring(TargetResources[0].userPrincipalName))
| project TimeGenerated, GuestUPN
| join kind=leftanti  (
    AuditLogs
    | where TimeGenerated > ago (timerange)
    | where OperationName == "Redeem external user invite"
    | where CorrelationId <> "00000000-0000-0000-0000-000000000000"
    | extend d = tolower(tostring(TargetResources[0].displayName))
    | parse d with * "upn: " GuestUPN "," *
    | project TimeGenerated, GuestUPN)
    on GuestUPN
| distinct GuestUPN

//Query to find high or medium real time risk events for users who have an assigned Azure AD role

//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Azure Active Directory - AAD User Risk Events
//Data connector required for this query - Microsoft Sentinel UEBA

let id=
    IdentityInfo
    | summarize arg_max(TimeGenerated, *) by AccountUPN;
let signin=
    SigninLogs
    | where TimeGenerated > ago (14d)
    | where RiskLevelDuringSignIn in ('high', 'medium')
    | join kind=inner id on $left.UserPrincipalName == $right.AccountUPN
    | extend SigninTime = TimeGenerated
    | where RiskEventTypes_V2 != "[]";
AADUserRiskEvents
| where TimeGenerated > ago (14d)
| extend RiskTime = TimeGenerated
| where DetectionTimingType == "realtime"
| where RiskDetail !has "aiConfirmedSigninSafe"
| join kind=inner signin on CorrelationId
| where AssignedRoles != "[]"
| extend TimeDelta = abs(SigninTime - RiskTime)
| project
    SigninTime,
    UserPrincipalName,
    RiskTime,
    TimeDelta,
    RiskEventTypes,
    RiskLevelDuringSignIn,
    City,
    Country,
    EmployeeId,
    AssignedRoles

//Find guest accounts that haven't signed in for a period of time, this example uses 45 days

//Data connector required for this query - Azure Active Directory - Signin Logs

let timerange=180d;
let timeframe=45d;
SigninLogs
| where TimeGenerated > ago(timerange)
| where UserType == "Guest" or UserPrincipalName contains "#ext#"
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| join kind = leftanti  
    (
    SigninLogs
    | where TimeGenerated > ago(timeframe)
    | where UserType == "Guest" or UserPrincipalName contains "#ext#"
    | where ResultType == 0
    | summarize arg_max(TimeGenerated, *) by UserPrincipalName
    )
    on UserPrincipalName
| project UserPrincipalName

//Find users who hold privileged Azure AD roles but haven't signed onto Azure for 30 days

//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Microsoft Sentinel UEBA

let applications = dynamic(["Azure Active Directory PowerShell", "Microsoft Azure PowerShell", "Graph Explorer", "ACOM Azure Website", "Azure Portal", "Azure Advanced Threat Protection"]);
IdentityInfo
| where TimeGenerated > ago(21d)
| where isnotempty(AssignedRoles)
| project-rename UserPrincipalName=AccountUPN
| where AssignedRoles != "[]"
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| join kind=leftanti (
    SigninLogs
    | where TimeGenerated > ago(30d)
    | where AppDisplayName in (applications)
    | where ResultType == "0"
    )
    on UserPrincipalName
| project UserPrincipalName, AssignedRoles

//Create a pivot table showing all your users who have signed in with legacy auth, which applications they are using (such as IMAP or ActiveSync) and the count of each

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
| where isnotempty(ClientAppUsed)
| evaluate pivot(ClientAppUsed, count(), UserPrincipalName)

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 0
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
| where isnotempty(ClientAppUsed)
| evaluate pivot(ClientAppUsed, count(), AccountUpn)

//Detect when MFA details for a user are changed, deleted or registered from an IP address that user has never signed in successfully from

//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Azure Active Directory - Audit Logs

//Cache all successful sign in data for users using materialize operator
let signindata=materialize (
    SigninLogs
    | where TimeGenerated > ago(180d)
    | where ResultType == 0
    | distinct UserPrincipalName, UserId, IPAddress);
//Search for audit events showing MFA registrations, deletions or changes in the last day
AuditLogs
| where TimeGenerated > ago(10d)
| where OperationName in ("User registered security info", "User deleted security info", "User registered all required security info")
| where Result == "success"
| extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend UserId = tostring(TargetResources[0].id)
| project UserPrincipalName, UserId, IPAddress, OperationName
//Join those events back to our summarized sign in data looking for users who register MFA from an IP they have never signed in from
| where isnotempty(IPAddress)
| join kind=leftanti (signindata) on IPAddress, UserId
| distinct UserPrincipalName, IPAddress, OperationName

//Calculate how often your users are actively challenged for MFA vs when it was previously satisfied per day
//Return users who are challenged over the threshold per day

//Data connector required for this query - Azure Active Directory - Signin Logs

let threshold = 5;
SigninLogs
| where TimeGenerated > ago(90d)
| where AuthenticationRequirement == "multiFactorAuthentication"
| extend x=todynamic(AuthenticationDetails)
| mv-expand x
| project TimeGenerated, x, UserPrincipalName
| extend MFAResultStep = tostring(x.authenticationStepResultDetail)
| summarize MFARequired=countif(MFAResultStep == "MFA completed in Azure AD"), PreviouslySatisfied=countif(MFAResultStep == "MFA requirement satisfied by claim in the token") by UserPrincipalName, startofday(TimeGenerated)
| where MFARequired >= threshold

//Create a pivot table of all non password authentication methods by user. This is useful to migrate users from less secure methods like text message to more secure methods.

//Data connector required for this query - Azure Active Directory - Signin Logs

let isGUID = "[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}";
SigninLogs
| where TimeGenerated > ago(30d)
| mv-expand todynamic(AuthenticationDetails)
| extend ['Authentication Method'] = tostring(AuthenticationDetails.authenticationMethod)
//Exclude previously satisifed, passwords and other data and any UserPrincipalName that comes through as a guid
| where ['Authentication Method'] !in ("Previously satisfied", "Password", "Other")
    and isnotempty(['Authentication Method'])
    and not(UserPrincipalName matches regex isGUID)
//Create pivot table of each method and the count by user
| evaluate pivot(['Authentication Method'], count(), UserPrincipalName)
| sort by UserPrincipalName asc

//Alert when a user successfully signs in from both a new location and using a new MFA method

//Data connector required for this query - Azure Active Directory - Signin Logs

//Cache all authentication methods and locations to memory using the materialize function for the last 6 months
let mfahistory = materialize  (
    SigninLogs
    | where TimeGenerated > ago (180d) and TimeGenerated < ago(1d)
    | where ResultType == 0
    | where AuthenticationRequirement == "multiFactorAuthentication"
    | extend AuthMethod = tostring(MfaDetail.authMethod)
    | where isnotempty(AuthMethod)
    | distinct UserPrincipalName, AuthMethod, Location);
//Find sign ins from the last day that have both a new location and MFA method
mfahistory
| join kind=rightanti  (
    SigninLogs
    | where TimeGenerated > ago (1d)
    | where ResultType == 0
    | where AuthenticationRequirement == "multiFactorAuthentication"
    | extend AuthMethod = tostring(MfaDetail.authMethod)
    | where isnotempty(AuthMethod)
    | distinct 
        UserPrincipalName,
        AuthMethod,
        AppDisplayName,
        Location,
        IPAddress)
    on UserPrincipalName, Location

//Calculate the percentage of signins to each of your Azure AD applications that used MFA

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| summarize
    ['Total Signin Count']=count(),
    ['Total MFA Count']=countif(AuthenticationRequirement == "multiFactorAuthentication"),
    ['Total non MFA Count']=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| project
    AppDisplayName,
    ['Total Signin Count'],
    ['Total MFA Count'],
    ['Total non MFA Count'],
    MFAPercentage=(todouble(['Total MFA Count']) * 100 / todouble(['Total Signin Count']))
| sort by ['Total Signin Count'] desc, MFAPercentage asc

//Detects when a user registers MFA details on their account and then completes self service password reset with a short timeframe

//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Azure Active Directory - Audit Logs

let timeframe=4h;
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName in~ ("User changed default security info", "User registered all required security info", "User registered security info","Admin registered security info")
| extend User = tostring(TargetResources[0].userPrincipalName)
| project SecurityInfoTime=TimeGenerated, User, OperationName
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago(1d)
    | where OperationName in ("Reset password (self-service)", "Change password (self-service)")
    | where Result == "success"
    | extend User = tostring(TargetResources[0].userPrincipalName)
    | project PasswordResetTime=TimeGenerated, OperationName, User)
    on User
| where (PasswordResetTime - SecurityInfoTime) between (0min .. timeframe)

//Detect when an Azure AD managed identity accesses a resource for the first time, i.e an identity that previously only accessed storage accesses a key vault

//Data connector required for this query - Azure Active Directory - Managed Identity Signin Logs

AADManagedIdentitySignInLogs
| where TimeGenerated > ago (60d) and TimeGenerated < ago(1d)
| where ResultType == "0"
| distinct ServicePrincipalId, ResourceIdentity
| join kind=rightanti (
    AADManagedIdentitySignInLogs
    | where TimeGenerated > ago (1d)
    | where ResultType == "0"
    )
    on ServicePrincipalId, ResourceIdentity
| project
    ['Service Principal DisplayName']=ServicePrincipalName,
    ['Service Principal Id']=ServicePrincipalId,
    ['Azure Resource Identity Id']=ResourceIdentity,
    ['Azure Resource DisplayName']=ResourceDisplayName
| distinct
    ['Service Principal DisplayName'],
    ['Service Principal Id'],
    ['Azure Resource DisplayName'],
    ['Azure Resource Identity Id']

//Summarize the Azure resources that each of your managed identities are accessing. The higher the count the higher the potential blast radius.

//Data connector required for this query - Azure Active Directory - Managed Identity Signin Logs

AADManagedIdentitySignInLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| summarize
    ['List of Azure Resources Accessed']=make_set(ResourceDisplayName),
    ['Distinct Resources Accessed']=dcount(ResourceDisplayName)
    by ServicePrincipalName
| sort by ['Distinct Resources Accessed'] desc

//Alert when a user fails Azure AD Conditional Access policies to 5 or more unique applications within a short time period, this example uses 1 hour.

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (1d)
| project TimeGenerated, ConditionalAccessPolicies, UserPrincipalName, AppDisplayName
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize
    ['List of Failed Application']=make_set(AppDisplayName),
    ['Count of Failed Application']=dcount(AppDisplayName)
    by UserPrincipalName, bin(TimeGenerated, 1h)
| where ['Count of Failed Application'] >= 5

//Data connector required for this query - Advanced Hunting - AADSignInEventsBeta

AADSignInEventsBeta
| where Timestamp > ago (1d)
| project Timestamp, ConditionalAccessPolicies, AccountUpn, Application
| extend ConditionalAccessPolicies = parse_json(ConditionalAccessPolicies)
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize
    ['List of Failed Application']=make_set(Application),
    ['Count of Failed Application']=dcount(Application)
    by AccountUpn, bin(Timestamp, 1h)
| where ['Count of Failed Application'] >= 5

//Detect when a user is blocked by Conditional Access after failing 3 unique CA policies or 3 unique applications over a 2 hour period

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "53003"
| mv-expand ConditionalAccessPolicies
| extend ['CA Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| where ConditionalAccessPolicies.result == "failure"
| summarize
    ['Total count of logon failures']=count(),
    ['Count of failed applications']=dcount(AppDisplayName),
    ['List of failed applications']=make_set(AppDisplayName),
    ['Count of failed policy names']=dcount(['CA Policy Name']),
    ['List of failed policy names']=make_set(['CA Policy Name'])
    by UserPrincipalName, bin(TimeGenerated, 2h)
| where ['Count of failed applications'] >= 3 or ['Count of failed policy names'] >= 3

//Detect when a user who holds an Azure AD privilege role fails MFA multiple times in a short time period. This example uses 2 failures within 20 minutes.

//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Microsoft Sentinel UEBA

let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | where isnotempty(AssignedRoles)
    | where AssignedRoles != "[]"
    | distinct AccountUPN;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "500121"
| where UserPrincipalName in (privusers)
| mv-expand todynamic(AuthenticationDetails)
| extend ['MFA Failure Type'] = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| where ['MFA Failure Type'] startswith "MFA denied"
| summarize
    ['MFA Failure Count']=count(),
    ['MFA Failure Reasons']=make_list(['MFA Failure Type'])
    by UserPrincipalName, bin(TimeGenerated, 20m)
| where ['MFA Failure Count'] >= 2

//Query to parse IP information from Security Alerts

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)

SecurityAlert
| where AlertName in ("Impossible travel activity", "Atypical Travel", "Anonymous IP address", "Anomalous Token")
| parse Entities with * 'AadUserId": "' aadid_ '",' *
| extend ep_ = parse_json(ExtendedProperties)
| extend s = tostring(ep_["IP Addresses"])
| extend ipv4_ = extract_all(@"(([\d]{1,3}\.){3}[\d]{1,3})", dynamic([1]), s)
| extend ipv4Add_ = translate('["]', '', tostring(ipv4_))
| extend ipv6_ = extract_all(@"(([\d|\w]{1,4}\:){7}[\d|\w]{1,4})", dynamic([1]), s)
| extend ipv6Add_ = translate('["]', '', tostring(ipv6_))
| project TimeGenerated, AlertName, ipv4Add_, ipv6Add_, CompromisedEntity

//Parses the user agent into its various components to allow hunting on specific browser versions or patch levels

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| extend UserAgentDetail = todynamic(parse_user_agent(UserAgent, "browser"))
| extend UserAgentFamily = tostring(parse_json(tostring(UserAgentDetail.Browser)).Family)
| extend UserAgentMajorVersion = toint(parse_json(tostring(UserAgentDetail.Browser)).MajorVersion)
| extend UserAgentMinorVersion = toint(parse_json(tostring(UserAgentDetail.Browser)).MinorVersion)
| extend UserAgentPatch = toint(parse_json(tostring(UserAgentDetail.Browser)).Patch)
| project
    TimeGenerated,
    UserPrincipalName,
    AppDisplayName,
    ResultType,
    IPAddress,
    Location,
    UserAgentFamily,
    UserAgentMajorVersion,
    UserAgentMinorVersion,
    UserAgentPatch,
    UserAgent

//Looks for potential AiTM phishing by finding sign ins with the following properties - has error codes 50074 (MFA required), 50140 (keep me signed in prompt) and 0 (success)
//It also looks for high or medium risk events and where there are multiple session id's per correlation id (per single sign in flow)

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel doesn't track SessionId like Advanced Hunting does so you may end up with a few more false positives

SigninLogs
| where AppDisplayName == "OfficeHome"
| where UserPrincipalName has "@"
| summarize
    ErrorCodes=make_set(ResultType),
    RiskLevels=make_set_if(RiskLevelDuringSignIn, RiskLevelDuringSignIn != "none"),
    RiskTypes=make_set_if(RiskEventTypes, RiskEventTypes != "[]"),
    IPs = make_set(IPAddress)
    by CorrelationId, UserPrincipalName
| where ErrorCodes has_all (0, 50140, 50074) // If conditional Access Blocks the SignIn attempt change the has_all to has_all (53000, 50074)
    and RiskLevels has_any ("medium", "high") // Depending on your organisation low can be included since some AiTM attempts are only classified as low.
| extend ['Count of RiskTypes']=array_length(RiskTypes)
| where ['Count of RiskTypes'] > 0

//Advanced Hunting query, includes SessionId's 

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Application == "OfficeHome"
| where AccountUpn has "@"
| summarize
    ErrorCodes=make_set(ErrorCode),
    RiskLevels=make_set_if(RiskLevelDuringSignIn, isnotempty(RiskLevelDuringSignIn)),
    RiskTypes=make_set_if(RiskEventTypes, isnotempty(RiskEventTypes)),
    SessionIds=make_set_if(SessionId, isnotempty(SessionId)),
    IPs = make_set_if(IPAddress, isnotempty(IPAddress))
    by CorrelationId, AccountUpn
| where ErrorCodes has_all (0, 50140, 50074) // If conditional Access Blocks the SignIn attempt change the has_all to has_all (53003, 50074)
    and RiskLevels has_any ("50", "100") // Depending on your organisation a lower risk level can be included since some AiTM attempts are only classified as low.
| extend ['Count of SessionIds']=array_length(SessionIds)
| extend ['Count of RiskTypes']=array_length(RiskTypes)
| where ['Count of SessionIds'] >= 2 and ['Count of RiskTypes'] > 0

//If you want to make a detection rule for this in Advanced Hunting you will just need to first find the suspicious correlationIds, then go back and find them so M365 Defender can map the fields properly
//Advanced Hunting needs a timestamp to create a detection. You could alternatively add a first/last event time to the query, but I prefer this way

let ids=
AADSignInEventsBeta
| where Application == "OfficeHome"
| where AccountUpn has "@"
| summarize
    ErrorCodes=make_set(ErrorCode),
    RiskLevels=make_set_if(RiskLevelDuringSignIn, isnotempty(RiskLevelDuringSignIn)),
    RiskTypes=make_set_if(RiskEventTypes, isnotempty(RiskEventTypes)),
    SessionIds=make_set_if(SessionId, isnotempty(SessionId)),
    IPs = make_set_if(IPAddress, isnotempty(IPAddress))
    by CorrelationId, AccountUpn
| where ErrorCodes has_all (0, 50140, 50074)
    and RiskLevels has_any ("50", "100")
| extend ['Count of SessionIds']=array_length(SessionIds)
| extend ['Count of RiskTypes']=array_length(RiskTypes)
| where ['Count of SessionIds'] >= 2 and ['Count of RiskTypes'] > 0
| distinct CorrelationId;
AADSignInEventsBeta
| where CorrelationId in (ids)
| summarize arg_min(Timestamp, *) by CorrelationId //grab the first event per correlationid to allow Advanced Hunting field mapping

//Find potentially compromised accounts trying to pivot into other apps by detecting 3 or more distinct Conditional Access failures or 3 or more failures to apps the account has no access to

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (1d)
| where ResultType in ("50105", "53003")
| summarize
    TotalCount=count(),
    ['Total Distinct Failed Apps']=make_set(AppDisplayName),
    ['List of distinct failed CA Apps']=make_set_if(AppDisplayName, ResultType == 53003),
    ['List of distinct no access Apps']=make_set_if(AppDisplayName, ResultType == 50105)
    by UserPrincipalName, bin(TimeGenerated, 1h)
| extend
    ['Count of distinct failed CA Apps']=array_length(['List of distinct failed CA Apps']),
    ['Count of distinct failed no access Apps']=array_length(['List of distinct no access Apps'])
| where ['Count of distinct failed CA Apps'] >= 3 or ['Count of distinct failed no access Apps'] >= 3

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Timestamp > ago (1d)
| where ErrorCode in ("50105", "53003")
| summarize
    TotalCount=count(),
    ['Total Distinct Failed Apps']=make_set(Application),
    ['List of distinct failed CA Apps']=make_set_if(Application, ErrorCode == 53003),
    ['List of distinct no access Apps']=make_set_if(Application, ErrorCode == 50105)
    by AccountUpn, bin(Timestamp, 1h)
| extend
    ['Count of distinct failed CA Apps']=array_length(['List of distinct failed CA Apps']),
    ['Count of distinct failed no access Apps']=array_length(['List of distinct no access Apps'])
| where ['Count of distinct failed CA Apps'] >= 3 or ['Count of distinct failed no access Apps'] >= 3

//Detect when a user has been potentially comrpomised but is stopped by MFA number matching or otherwise denied. Even if stopped by MFA the users credentials need to be rotated.

//Data connector required for this query - Azure Active Directory - Signin Logs

//This query finds any time a user denies an authentication, enters the wrong number or just doesn't respond three or more times in a single sign in event
SigninLogs
| project
    TimeGenerated,
    AuthenticationRequirement,
    AuthenticationDetails,
    UserPrincipalName,
    CorrelationId,
    ResultType
| where ResultType == 500121
| mv-expand todynamic(AuthenticationDetails)
| extend AuthResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| where AuthResult in ("MFA denied; user did not select the correct number", "MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification")
//Create a list of denied MFA challenges by sign in attempt (single CorrelationId)
| summarize ['Result Types']=make_list(AuthResult) by CorrelationId, UserPrincipalName
//Find results where there are at least 3 failures within the same sign in, i.e three denies, three did not respond events or three did not select the correct number
| where array_length( ['Result Types']) > 2

//This is the same query but grouped by username and 10 minute period, in case the attacker is starting a new authentication flow and generates a new CorrelationId
SigninLogs
| project
    TimeGenerated,
    AuthenticationRequirement,
    AuthenticationDetails,
    UserPrincipalName,
    CorrelationId,
    ResultType
| where ResultType == 500121
| mv-expand todynamic(AuthenticationDetails)
| extend AuthResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| where AuthResult in ("MFA denied; user did not select the correct number", "MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification")
//Create a list of denied MFA challenges by UserPrincipalName and 10 minute window (to account for multiple sign in attempts)
| summarize ['Result Types']=make_list(AuthResult) by UserPrincipalName, bin(TimeGenerated, 10m)
//Find results where there are at least 3 failures within the same sign in, i.e three denies, three did not respond events or three did not select the correct number
| where array_length( ['Result Types']) > 2

//Detect when a user denies MFA several times within a single sign in attempt and then completes MFA.
//This could be a sign of someone trying to spam your users with MFA prompts until they accept.

//Data connector required for this query - Azure Active Directory - Signin Logs

//Select your threshold of how many times a user denies MFA before accepting
let threshold=2;
SigninLogs
| project
    TimeGenerated,
    AuthenticationRequirement,
    AuthenticationDetails,
    UserPrincipalName,
    CorrelationId
//Include only authentications that require MFA
| where AuthenticationRequirement == "multiFactorAuthentication"
//Extend authentication result description
| mv-expand todynamic(AuthenticationDetails)
| extend AuthResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
//Find results that include both denined and completed MFA
| where AuthResult in ("MFA completed in Azure AD", "MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification","MFA successfully completed")
//Create a list of completed and denied MFA challenges per correlation id
| summarize ['Result Types']=make_list(AuthResult) by CorrelationId, UserPrincipalName
//Ensure the list includes both completed and denied MFA challenges
| where ['Result Types'] has_any ("MFA completed in Azure AD","MFA successfully completed") and ['Result Types'] has_any ("MFA denied; user declined the authentication", "MFA denied; user did not respond to mobile app notification")
| mv-expand ['Result Types'] to typeof(string)
//Expand and count all the denied challenges and then return CorrelationId's where the MFA denied count is greater or equal to your threshold
| where ['Result Types'] has_any ("MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification")
| summarize ['Denied MFA Count']=count()by ['Result Types'], CorrelationId, UserPrincipalName
| where ['Denied MFA Count'] >= threshold

//Alternate query, instead of grouping signins by CorrelationId we group them by UserPrincipalName and 10 minute blocks of time.
//In case the bad actor is starting a whole new sign in each time and generating a new CorrelationId for each attempt.
//Select your threshold of how many times a user denies MFA before accepting
let threshold=2;
SigninLogs
| project
    TimeGenerated,
    AuthenticationRequirement,
    AuthenticationDetails,
    UserPrincipalName,
    CorrelationId
//Include only authentications that require MFA
| where AuthenticationRequirement == "multiFactorAuthentication"
//Extend authentication result description
| mv-expand todynamic(AuthenticationDetails)
| extend AuthResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
//Find results that include both denined and completed MFA
| where AuthResult in ("MFA completed in Azure AD", "MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification","MFA successfully completed")
//Create a list of completed and denied MFA challenges per user principal name over 10 minute periods
| summarize ['Result Types']=make_list(AuthResult) by UserPrincipalName, bin(TimeGenerated, 10m)
//Ensure the list includes both completed and denied MFA challenges
| where ['Result Types'] has_any ("MFA completed in Azure AD","MFA successfully completed") and ['Result Types'] has_any ("MFA denied; user declined the authentication", "MFA denied; user did not respond to mobile app notification")
| mv-expand ['Result Types'] to typeof(string)
//Expand and count all the denied challenges and then return UserPrincipalNames where the MFA denied count is greater or equal to your threshold
| where ['Result Types'] has_any ("MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification")
| summarize ['Denied MFA Count']=count()by ['Result Types'], UserPrincipalName
| where ['Denied MFA Count'] >= threshold

//Simple query to count users being spammed with denies or not responding in one hour time windows
SigninLogs
| project
    TimeGenerated,
    AuthenticationRequirement,
    AuthenticationDetails,
    UserPrincipalName,
    CorrelationId
| where AuthenticationRequirement == "multiFactorAuthentication"
//Extend authentication result description
| mv-expand todynamic(AuthenticationDetails)
| extend AuthResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| where AuthResult in ("MFA denied; user declined the authentication","MFA denied; user did not respond to mobile app notification")
| summarize ['Result Types']=make_list(AuthResult), ['Result Count']=count() by UserPrincipalName, bin(TimeGenerated, 60m)
//Find hits with greater than 3 failures in an hour
| where ['Result Count'] > 3

// Detects when a user flags an Azure AD risk event followed by changes to their MFA profile - potentially detecting a bad actor changing MFA details

//Data connector required for this query - Azure Active Directory - AAD User Risk Events
//Data connector required for this query - Azure Active Directory - Audit Logs

// Timeframe = the minutes between flagging a risk event and MFA details being changed
let timeframe = 120;
//Search for real time risk events only and retrieve Correlation Id
AADUserRiskEvents
| where TimeGenerated > ago (1d)
| where DetectionTimingType == "realtime"
| where RiskDetail <> "aiConfirmedSigninSafe"
| project CorrelationId
//Join Correlation Id back to sign in data to retrieve the initial sign in time that was flagged for risk
| join kind=inner(
SigninLogs
| where TimeGenerated > ago (1d))
on CorrelationId
| summarize ['Risky Signin Time']=min(TimeGenerated) by CorrelationId, UserPrincipalName
//Join risky sign in UserPrincipalName to audit log for MFA events
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago (1d)
    | where OperationName in ("User registered security info", "User deleted security info","User registered all required security info")
    | where Result == "success"
    | extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
//Find the latest event in the MFA registration process
    | summarize arg_max(TimeGenerated, *) by UserPrincipalName
    | project
        ['MFA Change Time']=TimeGenerated,
        OperationName,
        UserPrincipalName)
    on UserPrincipalName
//Calculate the time between the initial sign in event and the MFA change time
| extend ['Minutes Between Events']=datetime_diff("minute",['MFA Change Time'], ['Risky Signin Time'])
| project-away UserPrincipalName1
| project-reorder ['Risky Signin Time'], ['MFA Change Time'], ['Minutes Between Events'], UserPrincipalName, OperationName, CorrelationId
//Find events where the time between the two events was less than 120 minutes
| where ['Minutes Between Events'] < timeframe

//Detects when a user has a medium or high risk sign in requiring MFA registration (error 50079/50072) followed by successful MFA registration within 2 hours
//This may detect an adversary registering MFA on behalf of your users

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (7d)
| where RiskLevelDuringSignIn in ("medium", "high")
| where ResultType in ("50079","50072")
| project
    RiskTime=TimeGenerated,
    UserPrincipalName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription
| join kind=inner(
    AuditLogs
    | where TimeGenerated > ago (7d)
    | where OperationName == "User registered security info"
    | where Result == "success"
    | extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
    )
    on UserPrincipalName
| project-rename MFATime=TimeGenerated, MFAResult=ResultDescription1
| where (MFATime - RiskTime) between (0min .. 2h)
| extend TimeDelta=MFATime-RiskTime
| project
    RiskTime,
    MFATime,
    TimeDelta,
    UserPrincipalName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription,
    MFAResult

//Detects when a user has a medium or high risk sign in followed by that user successfully registering MFA on another user within 4 hours
//This may detect an adversary registering MFA on behalf of your users using a compromised admin account

//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Azure Active Directory - Audit Logs

SigninLogs
| where RiskLevelDuringSignIn in~ ("medium", "high")
| project
    ['Risky Signin Time']=TimeGenerated,
    UserPrincipalName,
    SigninIP=IPAddress,
    RiskLevelDuringSignIn,
    RiskEventTypes,
    SigninResult=ResultType
| join kind=inner (
    AuditLogs
    | where OperationName == "Admin registered security info" and Result == "success"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | project
        ['MFA Change Time']=TimeGenerated,
        OperationName,
        ResultReason,
        Actor,
        Target,
        TargetResources
    )
    on $left.UserPrincipalName == $right.Actor
| where ['MFA Change Time'] between (['Risky Signin Time'] .. timespan(4h))
| project
    ['Risky Signin Time'],
    ['MFA Change Time'],
    Actor,
    Target,
    SigninIP,
    SigninResult,
    OperationName,
    ResultReason,
    TargetResources