Splunk ESCU

// Detects when a service principal has been added to an Azure AD role

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

AuditLogs
| where OperationName == "Add member to role"
| where TargetResources[0].type == "ServicePrincipal"
| extend ['Service Principal Object Id'] = tostring(TargetResources[0].id)
| extend ['Application Display Name'] = tostring(TargetResources[0].displayName) 
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Azure AD Role Added'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| project TimeGenerated, Actor, ['Azure AD Role Added'], ['Service Principal Object Id'], ['Application Display Name']

//Detect when a successful self service password (SSPR) event is followed by a medium or high risk sign in within 2 hours
//Threat actors are known to socially engineer helpdesk staff to update MFA methods to allow them to complete SSPR

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

//Looks back 7 days by default for events within 2 hours of each other, but you can update to suit

let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Reset password (self-service)"
| where ResultDescription == "Successfully completed reset."
| extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
| project-rename PasswordResetTime=TimeGenerated
| project UserPrincipalName, PasswordResetTime, OperationName
| join kind=inner(
SigninLogs
    | where TimeGenerated > ago(7d)
    | where ResultType in (successCodes)
    | where RiskLevelDuringSignIn in ("high", "medium")
    | project
        RiskTime=TimeGenerated,
        UserPrincipalName,
        ResultType,
        ResultDescription,
        AppDisplayName,
        IPAddress,
        Location,
        UserAgent
    )
    on UserPrincipalName
| extend ['Time Between Events']=datetime_diff('minute', PasswordResetTime, RiskTime)
| project-reorder
    PasswordResetTime,
    RiskTime,
    ['Time Between Events'],
    UserPrincipalName,
    OperationName,
    ResultType,
    ResultDescription,
    AppDisplayName
| where ['Time Between Events'] <= 120

//Query to identity Security Alerts where they were triggered by a new user agent not seen for the previous 7 days. Update known IP Addresses from "1.1.1" to your corporate IP addresses to exclude

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

let IPs=
    SecurityAlert
    | project
        TimeGenerated,
        Status,
        AlertName,
        CompromisedEntity,
        ExtendedProperties,
        ProviderName
    | where TimeGenerated > ago (1h)
    | where ProviderName in ('MCAS', 'IPC')
    | where AlertName in ('Impossible travel activity', 'Multiple failed login attempts', 'Unfamiliar sign-in properties', 'Anonymous IP address', 'Atypical travel')
    | where Status contains "New"
    | extend Properties = tostring(parse_json(ExtendedProperties))
    | extend UserPrincipalName = CompromisedEntity
    | extend ipv4Addresses = extract_all(@"(([\d]{1,3}\.){3}[\d]{1,3})", dynamic([1]), Properties)
    | extend ipv4Add = translate('["]', '', tostring(ipv4Addresses))
    | extend ipv4Split =split(ipv4Add, ",")
    | mv-expand ipv4Split
    | extend ipv4Split_s = tostring(ipv4Split);
SigninLogs
| project
    TimeGenerated,
    UserPrincipalName,
    IPAddress,
    AppDisplayName,
    ResultType,
    UserAgent,
    Location
| where TimeGenerated > ago(7d)
| where IPAddress !startswith "1.1.1."
| where ResultType == 0 or ResultType == 50158
| join kind=inner IPs on UserPrincipalName, $left.IPAddress == $right.ipv4Split_s
| summarize AgentCount = count()by UserPrincipalName, UserAgent
| where AgentCount == 1

//Detect when an Azure AD service principal is created for a managed identity

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

AuditLogs
| where OperationName == "Add service principal"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.app)).displayName)
| extend ['Service Principal DisplayName'] = tostring(TargetResources[0].displayName)
| extend ['Service Principal Id'] = tostring(TargetResources[0].id)
| where Actor == "Managed Service Identity"
| project TimeGenerated, ['Service Principal DisplayName'], ['Service Principal Id']

//Find Azure AD service principals that have both successful and failed sign ins because of an expired secret. For both results, find the IP addresses.

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

//If you are seeing results then the old secret is still in use somewhere and requires updating.
//Microsoft Sentinel query
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(1d)
| summarize
    ['All Error Codes']=make_set(ResultType),
    ['Successful IP Addresses']=make_set_if(IPAddress, ResultType == 0),
    ['Failed IP Addresses']=make_set_if(IPAddress, ResultType == "7000222")
    by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] has_all ("0", "7000222")

//Advanced Hunting query

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

AADSpnSignInEventsBeta
| where Timestamp > ago(1d)
| summarize
    ['All Error Codes']=make_set(ErrorCode),
    ['Successful IP Addresses']=make_set_if(IPAddress, ErrorCode == 0),
    ['Failed IP Addresses']=make_set_if(IPAddress, ErrorCode == "7000222")
    by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] has "0" and ['All Error Codes'] has "7000222"

//Adds a friendly error description to the AADServicePrincipalSignInLogs table for any non successful signins

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

AADServicePrincipalSignInLogs
| where ResultType != "0"
| extend ErrorDescription = case (
    ResultType == "7000215", strcat("Invalid client secret is provided"),
    ResultType == "7000222", strcat("The provided client secret keys are expired"),
    ResultType == "700027", strcat("Client assertion failed signature validation"),
    ResultType == "700024", strcat("Client assertion is not within its valid time range"),
    ResultType == "70021", strcat("No matching federated identity record found for presented assertion"),
    ResultType == "500011", strcat("The resource principal named {name} was not found in the tenant named {tenant}"),
    ResultType == "700082", strcat("The refresh token has expired due to inactivity"),
    ResultType == "90025", strcat("Request processing has exceeded gateway allowance"),
    ResultType == "500341", strcat("The user account {identifier} has been deleted from the {tenant} directory"),
    ResultType == "100007", strcat("AAD Regional ONLY supports auth either for MSIs OR for requests from MSAL using SN+I for 1P apps or 3P apps in Microsoft infrastructure tenants"),
    ResultType == "1100000", strcat("Non-retryable error has occurred"),
    ResultType == "90033", strcat("A transient error has occurred. Please try again"),
    ResultType == "53003", strcat("Access has been blocked by Conditional Access policies. The access policy does not allow token issuance."),
    "Unknown"
    )
| project
    TimeGenerated,
    ServicePrincipalName,
    ServicePrincipalId,
    ErrorDescription,
    ResultType,
    IPAddress

//Detect when an Azure AD service principal signs in from an IP previously not seen

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

AADServicePrincipalSignInLogs
| where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
| distinct AppId, IPAddress
| join kind=rightanti
    (
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(7d)
    | project TimeGenerated, AppId, IPAddress, ResultType, ServicePrincipalName
    )
    on IPAddress
| where ResultType == "0"
| distinct ServicePrincipalName, AppId, IPAddress

//Make a set of IP addresses that your Azure AD service principals have signed in from

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

AADServicePrincipalSignInLogs
| where TimeGenerated > ago(90d)
| where ResultType == "0"
| summarize ['List of IP Addresses']=make_set(IPAddress), ['Count of Unique IP Addresses']=dcount(IPAddress) by ServicePrincipalName, AppId
| sort by ['Count of Unique IP Addresses'] desc

//Create a summary of which resources each of your service principals are connecting to. The higher the count the potential higher blast radius.

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

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

//Find Azure AD service principals that are logging on from multiple locations, this should be less common than with user accounts

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

//Return the IP addresses used to sign in and see if you can build conditional access policies around them
//Microsoft Sentinel Query
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| where isnotempty(Location)
| summarize
    ['Count of Locations']=dcount(Location),
    ['List of Locations']=make_set(Location),
    ['Count of IP Addresses']=dcount(IPAddress),
    ['List of IP Addresses']=make_set(IPAddress)
    by ServicePrincipalName, AppId
| where ['Count of Locations'] >= 2

//Advanced Hunting query

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

AADSpnSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == "0"
| where isnotempty(Country)
| summarize
    ['Count of Locations']=dcount(Country),
    ['List of Locations']=make_set(Country),
    ['Count of IP Addresses']=dcount(IPAddress),
    ['List of IP Addresses']=make_set(IPAddress)
    by ServicePrincipalName, ApplicationId
| where ['Count of Locations'] >= 2

//Find any Azure AD Service Principals with only incorrect or expired secrets being used and no successful signins. These apps may no longer be in use and can be removed.

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

//Microsoft Sentinel query
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(14d)
| summarize
    ['All Error Codes']=make_set(ResultType)
    by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] !has "0" and ['All Error Codes'] has_any ("7000222", "7000215")

//Advanced Hunting query

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

AADSpnSignInEventsBeta
| where Timestamp > ago(14d)
| summarize
    ['All Error Codes']=make_set(ErrorCode)
    by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] !has "0" and ['All Error Codes'] has_any ("7000222", "7000215")

//Find Azure AD service principals that have only signed in from a single IP address in the last month. You should apply Conditional Access for workloads on them - https://docs.microsoft.com/en-us/azure/active-directory/conditional-access/workload-identity

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

//Microsoft Sentinel query
//First create a distinct count of IP addresses for each AppId and return any AppId with only 1 distinct IP
let appid=
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago (30d)
    | where ResultType == 0
    | summarize dcount(IPAddress) by AppId
    | where dcount_IPAddress == 1
    | distinct AppId;
//Query the same data for only those AppIds and summarize each IP by the AppId and friendly service principal names
AADServicePrincipalSignInLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where AppId in (appid)
| summarize ['Application Ids']=make_set(AppId) by IPAddress, ServicePrincipalName

//Advanced Hunting query

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

//First create a distinct count of IP addresses for each AppId and return any AppId with only 1 distinct IP
let appid=
    AADSpnSignInEventsBeta
    | where Timestamp > ago (30d)
    | where ErrorCode == 0
    | where IsManagedIdentity == 0
    | summarize dcount(IPAddress) by ApplicationId
    | where dcount_IPAddress == 1
    | distinct ApplicationId;
//Query the same data for only those AppIds and summarize each IP by the AppId and friendly service principal names
AADSpnSignInEventsBeta
| where Timestamp > ago (30d)
| where ErrorCode == 0
| where ApplicationId in (appid)
| summarize ['Application Ids']=make_set(ApplicationId) by IPAddress, ServicePrincipalName

//Find any single factor sign ins to Azure resources such as the Azure portal

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

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(7d)
| where AppDisplayName has "Azure"
| where ResultType == 0
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize ['Single Factor Authentications']=make_set(UserPrincipalName) by AppDisplayName
| extend ['User Count'] = array_length(['Single Factor Authentications'])
| order by ['User Count'] desc

//Advanced Hunting query

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

AADSignInEventsBeta
| where Timestamp > ago(7d)
| where Application has "Azure"
| where ErrorCode == 0
| where LogonType == @"[""interactiveUser""]"
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize ['Single Factor Authentications']=make_set(AccountUpn) by Application
| extend ['User Count'] = array_length(['Single Factor Authentications'])
| order by ['User Count'] desc

//Finds users who hold a privileged Azure Active Directory role who are signing into applications using single factor

//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(30d)
| where UserPrincipalName in~ (privusers)
| where ResultType == 0
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize
    ['List of Applications']=make_set(AppDisplayName),
    ['Count of Applications']=dcount(AppDisplayName)
    by UserPrincipalName
| sort by ['Count of Applications'] desc

//Summarize accounts (both members and guests) that haven't signed on for over 60 days, and summarize them into the month they last signed in

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

SigninLogs
| where TimeGenerated > ago (365d)
| where ResultType == 0
| where isnotempty(UserType)
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| where TimeGenerated < ago(60d)
| summarize
    ['Inactive Account List']=make_set(UserPrincipalName),
    ['Count of Inactive Accounts']=dcount(UserPrincipalName)
    by UserType, Month=startofmonth(TimeGenerated)
| sort by Month desc, UserType asc

//Summarize the usage of all your applications from last month to this month. Usage is calculated by distinct users per application.

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

SigninLogs
| where TimeGenerated > ago(60d)
| where ResultType == "0"
| summarize
    ['Last Month Signins']=dcountif(UserPrincipalName, TimeGenerated > ago(60d) and TimeGenerated < ago(30d)),
    ['This Month Signins']=dcountif(UserPrincipalName, TimeGenerated > ago(30d))
    by AppId, AppDisplayName
| extend ['Percentage Change']=(todouble(['This Month Signins'] - todouble(['Last Month Signins'])) / todouble(['Last Month Signins']) * 100)
| sort by AppDisplayName asc

//Create a summary showing which of your Azure AD conditional access policies are preventing the most signins and for what reasons

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

SigninLogs
| where TimeGenerated > ago (7d)
| project TimeGenerated, ConditionalAccessPolicies, ResultType, ResultDescription
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend ['Conditional Access Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "failure"
| summarize ['Count of Failures']=count()by ['Conditional Access Policy Name'], ResultType, ResultDescription
| sort by ['Count of Failures'] desc

//Summarize which conditional access policies your inbound guests have logged any successes or failures against.

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

SigninLogs
| where TimeGenerated > ago (90d)
| where UserType == "Guest"
| where AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId
| project ConditionalAccessPolicies, UserPrincipalName, HomeTenantId
| project-rename GuestTenantId=HomeTenantId
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult has_any ("success", "failure")
| extend ['Conditional Access Result']=strcat(CAName, " || ", CAResult)
| summarize
    ['Conditional Access Outcomes']=make_set(['Conditional Access Result'])
    by
    GuestTenantId
| order by GuestTenantId asc

//Summarize guest activity by external Azure AD guests (those that belong to another Azure AD tenant) vs External Guests (such as Gmail) to your tenant
//For each domain list the total number of signins and distinct user 

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

SigninLogs
| where TimeGenerated > ago (30d)
| where UserType == "Guest"
| where ResultType == 0
| extend ['Guest Domain'] = tostring(split(UserPrincipalName, "@")[1])
| summarize
    ['External Azure AD Guest Logins']=countif(ResourceTenantId != HomeTenantId),
    ['External Azure AD Guest Distinct Users']=dcountif(UserPrincipalName, ResourceTenantId != HomeTenantId),
    ['External Guest Logins']=countif(ResourceTenantId == HomeTenantId),
    ['External Guest Distinct Users']=dcountif(UserPrincipalName, ResourceTenantId == HomeTenantId)
    by ['Guest Domain']

//Group your Azure AD guest accounts into the month they last signed in

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

SigninLogs
| where TimeGenerated > ago (360d)
| where UserType == "Guest" or UserPrincipalName contains "#ext#"
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| project TimeGenerated, UserPrincipalName
| summarize ['Inactive Guest Accounts']=make_set(UserPrincipalName) by Month=startofmonth(TimeGenerated)
| sort by Month desc

//Summarize the domain names, the count of users and list of users for each guest tenant connecting to your Azure AD tenant

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

SigninLogs
| where TimeGenerated > ago (30d)
| project
    TimeGenerated,
    UserPrincipalName,
    HomeTenantId,
    AADTenantId,
    Id,
    ResourceTenantId,
    ResourceIdentity,
    UserId,
    AppDisplayName
| where UserId != "00000000-0000-0000-0000-000000000000"
| where ResourceIdentity != ''
| where HomeTenantId != ''
| where HomeTenantId != AADTenantId
| extend GuestDomain = split(UserPrincipalName, '@')[-1]
| summarize
    ['Guest Domain Names']=make_set(GuestDomain),
    ['Distinct User Count']=dcount(UserPrincipalName),
    ['List of Guest Users']=make_set(UserPrincipalName)
    by HomeTenantId, AppDisplayName
| sort by ['Distinct User Count'] desc

//Summarize signins to your Azure AD tenant still using Internet Explorer. Data is grouped into each user and which applications they are accessing.

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

SigninLogs
| where ResultType == 0
| 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
| where UserAgentFamily == "IE"
| summarize ['Total IE Signins']=count(), ['Count of Applications']=dcount(AppDisplayName), ['List of Applications']=make_set(AppDisplayName) by UserPrincipalName
| sort by ['Count of Applications'] desc

//Summarize legacy auth authentications by type (such as ActiveSync, IMAP etc) and which accounts are using those protocols

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

//Microsoft Sentinel query
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where ClientAppUsed in ("Exchange ActiveSync", "Exchange Web Services", "AutoDiscover", "Unknown", "POP3", "IMAP4", "Other clients", "Authenticated SMTP", "MAPI Over HTTP", "Offline Address Book")
| summarize ['Count of legacy auth attempts'] = count()by ClientAppUsed, UserPrincipalName
| sort by ClientAppUsed asc, ['Count of legacy auth attempts'] 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 ClientAppUsed in ("Exchange ActiveSync", "Exchange Web Services", "AutoDiscover", "Unknown", "POP3", "IMAP4", "Other clients", "Authenticated SMTP", "MAPI Over HTTP", "Offline Address Book")
| summarize ['Count of legacy auth attempts'] = count()by ClientAppUsed, AccountUpn
| sort by ClientAppUsed asc, ['Count of legacy auth attempts'] desc

//Summarize sign in activity from a particular country or countries to your tenant over the last week

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

//Microsoft Sentinel query
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
SigninLogs
| where TimeGenerated > ago(7d)
//Choose a country code, you could do multiples with | where Location in ("AU","US")
| where Location == "RU"
| extend ['Full Location'] = strcat(LocationDetails.countryOrRegion, '|', LocationDetails.state, '|', LocationDetails.city)
| where ResultType in(successCodes) or ResultType in(failureCodes)
| summarize
    ['Count of distinct successful sign ins'] = dcountif(UserPrincipalName, (ResultType in(successCodes))),
    ['List of successful users']=make_set_if(UserPrincipalName, (ResultType in(successCodes))),
    ['Successful result codes'] = make_set_if(ResultType, (ResultType in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(UserPrincipalName, (ResultType in(failureCodes))),
    ['List of failed users'] = make_set_if(UserPrincipalName, (ResultType in(failureCodes))),
    ['Failed result codes'] = make_set_if(ResultType, (ResultType in(failureCodes)))
    by ['Full Location'], IPAddress


//Summarize Russian sign in activity to your Azure AD tenant

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

//Advanced Hunting query
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
AADSignInEventsBeta
| where Timestamp > ago(7d)
//Choose a country code, you could do multiples with | where Country in ("AU","US")
| where Country == "RU"
| extend ['Full Location'] = strcat(Country, '|', State, '|', City)
| where ErrorCode in(successCodes) or ErrorCode in(failureCodes)
| summarize
    ['Count of distinct successful sign ins'] = dcountif(AccountUpn, (ErrorCode in(successCodes))),
    ['List of successful users']=make_set_if(AccountUpn, (ErrorCode in(successCodes))),
    ['Successful result codes'] = make_set_if(ErrorCode, (ErrorCode in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(AccountUpn, (ErrorCode in(failureCodes))),
    ['List of failed users'] = make_set_if(AccountUpn, (ErrorCode in(failureCodes))),
    ['Failed result codes'] = make_set_if(ErrorCode, (ErrorCode in(failureCodes)))
    by ['Full Location'], IPAddress

//When Azure AD Identity Protection flags an IP Address as malicious, find any successful or failed logon events in your tenant from that IP

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

//First create a list of success and erorr codes in Azure AD
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
//Find the suspicious IP information from the SecurityAlert table, this example looks back 7 days as this is an offline detection
let suspiciousip=
    SecurityAlert
    | where TimeGenerated > ago (7d)
    | where AlertName == "Malicious IP address"
    | extend IPAddress = tostring(parse_json(ExtendedProperties).["Client IP Address"])
    | distinct IPAddress;
//Look back 21 days for any other sign in data from that IP address
SigninLogs
| where TimeGenerated > ago(14d)
| where IPAddress in (suspiciousip)
| where ResultType in(successCodes) or ResultType in(failureCodes)
//Create a summary showing successful and failed logons from that IP address and which users are affected
| summarize
    ['Count of distinct successful sign ins'] = dcountif(UserPrincipalName, (ResultType in(successCodes))),
    ['List of successful users']=make_set_if(UserPrincipalName, (ResultType in(successCodes))),
    ['Successful result codes'] = make_set_if(ResultType, (ResultType in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(UserPrincipalName, (ResultType in(failureCodes))),
    ['List of failed users'] = make_set_if(UserPrincipalName, (ResultType in(failureCodes))),
    ['Failed result codes'] = make_set_if(ResultType, (ResultType in(failureCodes)))
    by IPAddress

//Summarize the count of the various types of MFA failures (such as user not responding, invalid codes, user declining the authentication)

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

SigninLogs
| where TimeGenerated > ago (90d)
| where ResultType == "500121"
| mv-expand todynamic(AuthenticationDetails)
| project AuthenticationDetails, ResultType
| extend ['MFA Failure Type'] = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| where ['MFA Failure Type'] startswith "MFA denied"
| summarize Count=count()by ['MFA Failure Type']
| sort by Count

//Summarize the percentage of signins covered by MFA for your top 20 most popular Azure AD apps

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

//Microsoft Sentinel query
//First create a list of your top 20 apps by finding distinct user count by application over 30 days
let top20apps=
    SigninLogs
    | where TimeGenerated > ago (30d)
    | summarize UserCount=dcount(UserPrincipalName)by AppDisplayName
    | sort by UserCount desc 
    | take 20
    | project AppDisplayName;
//Use that list to calculate the percentage of signins to those apps that are covered by MFA
SigninLogs
| where TimeGenerated > ago (30d)
| where AppDisplayName in (top20apps)
| summarize TotalCount=count(),
    MFACount=countif(AuthenticationRequirement == "multiFactorAuthentication"),
    nonMFACount=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| project
    AppDisplayName,
    TotalCount,
    MFACount,
    nonMFACount,
    MFAPercentage=(todouble(MFACount) * 100 / todouble(TotalCount))
| sort by MFAPercentage asc  

//Advanced Hunting query

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

//First create a list of your top 20 apps by finding distinct user count by application over 30 days
let top20apps=
    AADSignInEventsBeta
    | where Timestamp > ago (30d)
    | where LogonType == @"[""interactiveUser""]"
    | summarize UserCount=dcount(AccountUpn)by Application
    | sort by UserCount desc 
    | take 20
    | project Application;
//Use that list to calculate the percentage of signins to those apps that are covered by MFA
AADSignInEventsBeta
| where Timestamp > ago (30d)
| where LogonType == @"[""interactiveUser""]"
| where Application in (top20apps)
| summarize TotalCount=count(),
    MFACount=countif(AuthenticationRequirement == "multiFactorAuthentication"),
    nonMFACount=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by Application
| project
    Application,
    TotalCount,
    MFACount,
    nonMFACount,
    MFAPercentage=(todouble(MFACount) * 100 / todouble(TotalCount))
| sort by MFAPercentage asc

//Summarize outbound (your users connecting to other tenants) activity by listing the users and which applications they are accessing in each remote tenant

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

SigninLogs
| where TimeGenerated > ago(30d)
| where UserType == "Guest"
| where AADTenantId == HomeTenantId
| where ResourceTenantId != AADTenantId
| summarize
    ['Count of Applications']=dcount(AppDisplayName),
    ['List of Applications']=make_set(AppDisplayName),
    ['Count of Users']=dcount(UserPrincipalName),
    ['List of Users']=make_set(UserPrincipalName)
    by ResourceTenantId
| sort by ['Count of Users'] desc

//When a user registers or updates MFA details, take that IP address and summarize sign in events for that user and IP address for the last 30 days.
//If they have never signed in or signed in a few times from that IP it may a sign the account has been compromised and a threat actor has added MFA details

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

let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName in~ ("Admin registered security info", "Admin updated security info", "Admin deleted security info", "User registered security info", "User changed default security info", "User deleted security info")
| extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
| extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project ['MFA Event Time']=TimeGenerated, OperationName, UserPrincipalName, IPAddress
| join kind=inner(
    SigninLogs
    | where TimeGenerated > ago (30d)
    )
    on UserPrincipalName, IPAddress
| project
    TimeGenerated,
    ['MFA Event Time'],
    OperationName,
    ResultType,
    UserPrincipalName,
    IPAddress
| summarize
    ['Count of successful sign ins from MFA IP Address'] = countif(ResultType in(successCodes)),
    ['Count of failed sign ins from MFA IP Address'] = countif(ResultType in(failureCodes))
    by UserPrincipalName, OperationName, IPAddress, ['MFA Event Time']

//Investigate potentially suspicious IP addresses for your Azure AD signins. This query lists any IP address with more failures than successful connections and provides a summary for each with at least one successful signin.
//This may uncover password spray attacks that have had a successful connections.

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

//Microsoft Sentinel query
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType in(successCodes) or ResultType in(failureCodes)
| summarize
    ['Count of successful signins'] = countif((ResultType in(successCodes))),
    ['Count of distinct successful sign ins'] = dcountif(UserPrincipalName, (ResultType in(successCodes))),
    ['List of successful users']=make_set_if(UserPrincipalName, (ResultType in(successCodes))),
    ['Successful result codes'] = make_set_if(ResultType, (ResultType in(successCodes))),
    ['Count of failed signins']=countif((ResultType in(failureCodes))),
    ['Count of distinct failed sign ins'] = dcountif(UserPrincipalName, (ResultType in(failureCodes))),
    ['List of failed users'] = make_set_if(UserPrincipalName, (ResultType in(failureCodes))),
    ['Failed result codes'] = make_set_if(ResultType, (ResultType in(failureCodes))),
    ['First successful login'] = maxif(TimeGenerated, (ResultType in(successCodes)))
    by IPAddress
//Exclude IP addresses with more successful signins than failed. This is a good way to exclude known locations as they will have lots of failures too, but they will be balanced by more successes.
//Then find IP addresses with 5 or more distinct failed signins and at least one successful signin
| where ['Count of failed signins'] > ['Count of successful signins'] and ['Count of distinct failed sign ins'] > ['Count of distinct successful sign ins'] and ['Count of distinct failed sign ins'] >= 5 and ['Count of distinct successful sign ins'] >= 1
| order by ['Count of distinct successful sign ins'] desc

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

//Advanced Hunting query
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where ErrorCode in(successCodes) or ErrorCode in(failureCodes)
| summarize
    ['Count of successful signins'] = countif((ErrorCode in(successCodes))),
    ['Count of distinct successful sign ins'] = dcountif(AccountUpn, (ErrorCode in(successCodes))),
    ['List of successful users']=make_set_if(AccountUpn, (ErrorCode in(successCodes))),
    ['Successful result codes'] = make_set_if(ErrorCode, (ErrorCode in(successCodes))),
    ['Count of failed signins']=countif((ErrorCode in(failureCodes))),
    ['Count of distinct failed sign ins'] = dcountif(AccountUpn, (ErrorCode in(failureCodes))),
    ['List of failed users'] = make_set_if(AccountUpn, (ErrorCode in(failureCodes))),
    ['Failed result codes'] = make_set_if(ErrorCode, (ErrorCode in(failureCodes))) 
    by IPAddress
//Exclude IP addresses with more successful signins than failed. This is a good way to exclude known locations as they will have lots of failures too, but they will be balanced by more successes.
//Then find IP addresses with 5 or more distinct failed signins and at least one successful signin
| where ['Count of failed signins'] > ['Count of successful signins'] and ['Count of distinct failed sign ins'] > ['Count of distinct successful sign ins'] and ['Count of distinct failed sign ins'] >= 5 and ['Count of distinct successful sign ins'] >= 1
| order by ['Count of distinct successful sign ins'] desc

//Find the applications with the most signins coming from unknown locations (not defined in Conditional Access) and only requiring single factor authentication

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

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where NetworkLocationDetails == '[]'
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize ['Count of signins']=count(), ['Distinct user count']=dcount(UserPrincipalName) by AppDisplayName
| sort by ['Distinct user count'] desc

//Retrieve sign in failures due to third party MFA (Okta/Duo etc). Azure AD handles third party MFA different to native MS MFA. A user is sent to the third party MFA service and generates code 50158.
//If successful the user then generates a success code 0. When third party MFA fails Azure AD logs the 50158 result code but no corresponding 0 result code.

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

//Microsoft Sentinel query
SigninLogs
//Create a list of all result codes within a single sign in to Azure AD
| summarize MFA=make_list(ResultType) by CorrelationId
//Find correlation ids where the user was sent to third party MFA (ResultType 50158) but there is no subsequent success (ResultType 0)
| where MFA has "50158" and MFA !has "0"
//Join back to SigninLogs table to find the sign in details
| join kind=inner (SigninLogs) on CorrelationId
| project
    TimeGenerated,
    UserPrincipalName,
    UserType,
    AppDisplayName,
    IPAddress,
    Location,
    UserAgent,
    ResultType

//Advanced Hunting query

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

AADSignInEventsBeta
//Create a list of all result codes within a single sign in to Azure AD
| summarize MFA=make_list(ErrorCode) by CorrelationId
//Find correlation ids where the user was sent to third party MFA (ResultType 50158) but there is no subsequent success (ResultType 0)
| where MFA has "50158" and MFA !has "0"
//Join back to SigninLogs table to find the sign in details
| join kind=inner (AADSignInEventsBeta) on CorrelationId
| project
    Timestamp,
    AccountUpn,
    IsGuestUser,
    Application,
    IPAddress,
    Country,
    UserAgent,
    ErrorCode

//Find the top 20 most popular applications in your tenant that have had no Conditional Access policies (success or failure) applied

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

let apps=
    SigninLogs
    | where TimeGenerated > ago (30d)
    | project TimeGenerated, ConditionalAccessPolicies, AppDisplayName
//Exclude native Microsoft apps that you can't enforce policy on or that are covered natively in Office 365
    | where AppDisplayName !in ("Microsoft Office Web Apps Service", "Microsoft App Access Panel", "Office Online Core SSO", "Microsoft Authentication Broker", "Microsoft Account Controls V2", "Microsoft 365 Support Service","Office Online Maker SSO","My Apps","My Profile")
    | mv-expand ConditionalAccessPolicies
    | extend CAResult = tostring(ConditionalAccessPolicies.result)
    | summarize ResultSet=make_set(CAResult) by AppDisplayName
    | where ResultSet !has "success" or ResultSet !has "failure"
    | project AppDisplayName;
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where AppDisplayName in (apps)
| summarize Count=count()by AppDisplayName
| top 20 by Count

//Find the top 20 of a collection of varied data sets, no real detections in here just interesting data that is captured

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

//Microsoft Sentinel query

//Top 20 users signing into your tenant
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Member"
| summarize Count=count() by UserPrincipalName
| top 20 by Count

//Top 20 locations signing into your tenant
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Member"
| summarize Count=count() by Location
| where isnotempty(Location)
| top 20 by Count

//Top 20 users by MFA prompts
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Member"
| where AuthenticationRequirement == "multiFactorAuthentication"
| mv-expand todynamic(AuthenticationDetails)
| project TimeGenerated, AuthenticationDetails, UserPrincipalName
| extend AuthenticationMethod = tostring(AuthenticationDetails.authenticationMethod)
| where AuthenticationMethod in ("Mobile app notification","Text message","OATH verification code")
| summarize Count=count()by UserPrincipalName
| top 20 by Count

//Top 20 users by distinct applications accessed
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Member"
| summarize Count=dcount(AppDisplayName) by UserPrincipalName
| top 20 by Count

//Top 20 users accessing distinct locations accessed
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Member"
| summarize Count=dcount(Location) by UserPrincipalName
| top 20 by Count

//Top 20 applications by distinct users accessing them
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Member"
| summarize Count=dcount(UserPrincipalName) by AppDisplayName
| where AppDisplayName != "Windows Sign In"
| top 20 by Count

//Top 20 applications by distinct guests accessing them
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Guest"
| summarize Count=dcount(UserPrincipalName) by AppDisplayName
| top 20 by Count

//Top 20 guests by distinct applications accessed by them
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Guest"
| summarize Count=dcount(AppDisplayName) by UserPrincipalName
| top 20 by Count

//Top 20 guest domains accessing your tenant
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Guest"
| where AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId
| extend ['User Domain'] = tostring(split(UserPrincipalName, "@")[1])
| summarize Count=count() by ['User Domain']
| top 20 by Count

//Top 20 guest domains accessing your tenant by distinct applications accessed
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| where UserType == "Guest"
| where AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId
| extend ['User Domain'] = tostring(split(UserPrincipalName, "@")[1])
| summarize Count=dcount(AppDisplayName) by ['User Domain']
| top 20 by Count

//Find the top 20 IP addresses that are unknown to Azure AD, with users using unknown devices and single factor auth
//Then find the users and applications being accessed from each to help build out conditional access policy

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

let top20locations=
    SigninLogs
    | where TimeGenerated > ago(30d)
    | where ResultType == 0
    | extend DeviceTrustType = tostring(DeviceDetail.trustType)
    | where NetworkLocationDetails == '[]'
        and isempty(DeviceTrustType)
        and AuthenticationRequirement == "singleFactorAuthentication"
    | summarize Count=count()by IPAddress
    | top 20 by Count;
SigninLogs
| where ResultType == 0
| where IPAddress in (top20locations)
| summarize
    ['Total Signin Count']=count(),
    ['Distinct User Count']=dcount(UserPrincipalName),
    ['List of Users']=make_set(UserPrincipalName),
    ['Distinct Application Count']=dcount(AppDisplayName),
    ['List of Applications']=make_set(AppDisplayName)
    by IPAddress

//Detect when a user reports suspicious MFA activity via the updated user risk integration

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

SigninLogs
| where ResultType == 500121
| mv-expand todynamic(AuthenticationDetails)
| extend AuthResultDetail=AuthenticationDetails.authenticationStepResultDetail
| where AuthResultDetail == "SuspiciousActivityReported"
| project TimeGenerated, UserPrincipalName, ResultType, AppDisplayName, AuthResultDetail, Location, IPAddress, UserAgent, CorrelationId

//These events are also written to Azure AD Identity Protection

//Data connector required for this query - Azure Active Directory Identity Protection

AADUserRiskEvents
| where RiskEventType == "userReportedSuspiciousActivity"
| project TimeGenerated, UserPrincipalName, DetectionTimingType, RiskEventType, RiskState

//Detect users trying to access multiple applications they haven't been granted access to over a short period of time
//In this example alert when a user attempts to access 2 or more unique applications in 30 minutes

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

SigninLogs
| where ResultType == "50105"
| summarize
    ['Denied Application List']=make_set(AppDisplayName),
    ['Count of Applications']=dcount(AppDisplayName)
    by UserPrincipalName, bin(TimeGenerated, 30m)
| where ['Count of Applications'] >= 2

//Visualize the standard deviation of MFA failures per day using toscalar()

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

//Microsoft Sentinel query

let AverageMFAFailures = toscalar(SigninLogs
| where TimeGenerated > ago(60d)
| where ResultType == 500121
| summarize Count=count() by bin(TimeGenerated, 1d)
| summarize avg(Count));
SigninLogs
| where TimeGenerated > ago(60d)
| where ResultType == 500121
| summarize Count=count() by bin(TimeGenerated, 1d)
| extend Deviation = (Count - AverageMFAFailures) / AverageMFAFailures
| project-away Count
//Visualize the deviation per day
| render columnchart with (title="Deviation of MFA failures per day")

//Advanced Hunting query

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

let AverageMFAFailures = toscalar(AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 500121
| summarize Count=count() by bin(Timestamp, 1d)
| summarize avg(Count));
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 500121
| summarize Count=count() by bin(Timestamp, 1d)
| extend Deviation = (Count - AverageMFAFailures) / AverageMFAFailures
| project-away Count
//Visualize the deviation per day
| render columnchart with (title="Deviation of MFA failures per day")

//Visualizes conditional access policy failures over time by policy name

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

let start = now(-90d);
let end = now();
let timeframe= 12h;
SigninLogs
| project TimeGenerated, ResultType, ConditionalAccessPolicies
| where ResultType == 53003
| mv-expand ConditionalAccessPolicies
| where ConditionalAccessPolicies.result == "failure"
| extend ['CA Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| make-series ['Failure Count'] = count() default=0 on TimeGenerated in range(start, end, timeframe) by ['CA Policy Name']
| render timechart with (title="Conditional access policy failure over time")

//Visualize signins to your Azure AD tenant with no controls (known device, known location or MFA) vs those with at least one of those controls

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

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| summarize
    ['Signins with no controls']=countif(NetworkLocationDetails == '[]' and AuthenticationRequirement == "singleFactorAuthentication" and isempty(DeviceTrustType)),
    ['Signins with one or more controls']=countif(NetworkLocationDetails != '[]' or AuthenticationRequirement == "multiFactorAuthentication" or isnotempty(DeviceTrustType)) by
    bin(TimeGenerated, 1d)
| render timechart with (title="Azure AD signins no controls vs one or more controls")

//Visualize distinct inbound guests (guest accounts accessing your tenant) per month

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

SigninLogs
| where TimeGenerated > ago(365d)
| where UserType == "Guest"
| where ResultType == 0
//Find only inbound guests, i.e those guests accessing your tenant.
| summarize Count=dcountif(UserPrincipalName, AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId) by startofmonth(TimeGenerated)
| render columnchart with (title="Distinct guest accounts accessing your tenant per month")

//Visualize signins from External Azure AD guests (those that belong to another Azure AD tenant) vs External Guests (such as Gmail) to your tenant

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

SigninLogs
| where TimeGenerated > ago (45d)
| where UserType == "Guest"
| summarize
    ['External Guests']=countif(ResourceTenantId == HomeTenantId),
    ['External Azure AD Guests']=countif(ResourceTenantId != HomeTenantId)
    by bin(TimeGenerated, 1d)
| render timechart with (title="External Azure AD Guests vs External Guests", ytitle="Count")

//Visualize the most popular enterprise applications accessed by guest accounts

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

let timeframe=45d;
SigninLogs
| where TimeGenerated > ago(timeframe)
| where UserType == "Guest" or UserPrincipalName contains "#ext#"
| where ResultType == 0
// Exclude Microsoft management applications
| where AppDisplayName !in ("Microsoft Invitation Acceptance Portal", "My Apps", "Microsoft App Access Panel", "Microsoft Authentication Broker")
| summarize AppCount=count()by AppDisplayName
| sort by AppCount desc 
| render piechart with (title="Most Popular Apps Accessed by Guests")

//Visualize the most popular domains that have redeemed invites to your Azure AD tenant over the last 30 days.

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

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Redeem external user invite"
| extend GuestEmail = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['User Domain'] = tostring(split(GuestEmail, "@")[1])
| where isnotempty(['User Domain'])
| summarize Count=count()by ['User Domain']
| sort by Count desc 
| render barchart

//Visualize how many guests are redeeming invites over the time period with trend

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

let StartDate = now(-180d);
let EndDate = now();
AuditLogs
| where OperationName == "Redeem external user invite"
| make-series TotalInvites=count() on TimeGenerated in range(StartDate, EndDate, 1d)
| extend (RSquare, SplitIdx, Variance, RVariance, TrendLine)=series_fit_2lines(TotalInvites)
| project TimeGenerated, TotalInvites, TrendLine
| render timechart with (title="Guest Invites redeemed over time with trend")

//Visualize inbound Azure AD guests (other tenants connecting to your tenant) vs outbound (your users connecting to other tenants)

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

SigninLogs
| where TimeGenerated > ago (30d)
| where UserType == "Guest"
| where ResultType == 0
| summarize
    ['Inbound Guests']=countif(AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId),
    ['Outbound Guests']=countif(AADTenantId == HomeTenantId and ResourceTenantId != AADTenantId)
    by bin(TimeGenerated, 1d)
| render timechart with (title="Inbound Guest vs Outbound Guest Access", ytitle="Number of connections")

//Visualize sign in attempts to your Azure AD tenant by known or unknown network locations

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

SigninLogs
| where TimeGenerated > ago(30d)
//If a location it will be populated by its name, i.e 'Corporate Internet', if known the NetworkLocationDetails array is empty so we can find it by searching on '[]'
| summarize
    ['Known Location']=countif(NetworkLocationDetails != '[]'),
    ['Unknown Location']=countif(NetworkLocationDetails == '[]')
    by bin (TimeGenerated, 1d)
| render timechart with (title="Known vs Unknown signins to Azure AD", ytitle="Count")

//Visualize distinct users allowed to connect via legacy auth vs blocked by conditional access

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

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(180d)
| where ResultType in ("0", "53003")
| where ClientAppUsed in ("Exchange ActiveSync", "Exchange Web Services", "AutoDiscover", "Unknown", "POP3", "IMAP4", "Other clients", "Authenticated SMTP", "MAPI Over HTTP", "Offline Address Book")
| summarize
    ['Legacy Auth Users Allowed']=dcountif(UserPrincipalName, ResultType == 0),
    ['Legacy Auth Users Blocked']=dcountif(UserPrincipalName, ResultType == 53003)
    by bin(TimeGenerated, 1d)
| render timechart with (title="Legacy auth distinct users allowed vs blocked by Conditional Access")

//Advanced Hunting query

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

//Visualize distinct users allowed to connect via legacy auth vs blocked by conditional access
AADSignInEventsBeta
| where Timestamp > ago(180d)
| where ErrorCode  in ("0", "53003")
| where ClientAppUsed in ("Exchange ActiveSync", "Exchange Web Services", "AutoDiscover", "Unknown", "POP3", "IMAP4", "Other clients", "Authenticated SMTP", "MAPI Over HTTP", "Offline Address Book")
| summarize
    ['Legacy Auth Users Allowed']=dcountif(AccountUpn, ErrorCode == 0),
    ['Legacy Auth Users Blocked']=dcountif(AccountUpn, ErrorCode == 53003)
    by bin(Timestamp, 1d)
| render timechart

//Visualize legacy auth method (i.e Activesync, IMAP etc) by distinct user count over time

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

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(90d)
| where ResultType == 0
| where ClientAppUsed in ("Exchange ActiveSync", "Exchange Web Services", "AutoDiscover", "Unknown", "POP3", "IMAP4", "Other clients", "Authenticated SMTP", "MAPI Over HTTP", "Offline Address Book")
| summarize Count=dcount(UserPrincipalName) by ClientAppUsed, bin (TimeGenerated, 1d)
| render timechart with (title="Legacy auth methods by distinct user over time")

//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 ("Exchange ActiveSync", "Exchange Web Services", "AutoDiscover", "Unknown", "POP3", "IMAP4", "Other clients", "Authenticated SMTP", "MAPI Over HTTP", "Offline Address Book")
| summarize Count=dcount(AccountUpn) by ClientAppUsed, bin (Timestamp, 1d)
| render timechart

//Visualize when your users are actively challenged for MFA vs when it was previously satisfied

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

SigninLogs
| where TimeGenerated > ago(90d)
| where AuthenticationRequirement == "multiFactorAuthentication"
| mv-expand todynamic(AuthenticationDetails)
| project TimeGenerated, AuthenticationDetails
| extend MFAResultStep = tostring(AuthenticationDetails.authenticationStepResultDetail)
| summarize
    MFARequired=countif(MFAResultStep == "MFA completed in Azure AD"),
    PreviouslySatisfied=countif(MFAResultStep == "MFA requirement satisfied by claim in the token")
    by bin(TimeGenerated, 1d)
| render timechart
    with (
    xtitle="Day",
    ytitle="Count",
    title="MFA challenges vs MFA previously satisfied over time")