KQL Search

Query Details

HomeAI ToolsDevice Query

Authenticator Device Enrollment Country Risk Baseline

Query

let Lookback=90d;
let CountryBaseline=EntraIdSignInEvents
| where Timestamp > ago(Lookback)
| where isnotempty(AccountUpn)
| where isnotempty(IPAddress)
| extend Geo=geo_info_from_ip_address(IPAddress)
| extend CountryName=tostring(Geo["country"])
| where isnotempty(CountryName)
| summarize BaselineCountries=make_set(CountryName,256),BaselineCountryCount=dcount(CountryName) by AccountUpn;
AuditLogs
| where TimeGenerated > ago(Lookback)
| extend Details=tostring(AdditionalDetails)
| where Details has_any ("Microsoft Authenticator","PhoneAppNotification","PhoneAppOTP","Authenticator")
| extend Timestamp=TimeGenerated,
         AccountUpn=tostring(TargetResources[0].userPrincipalName),
         InitiatorUpn=coalesce(tostring(InitiatedBy.user.userPrincipalName),tostring(InitiatedBy.app.displayName)),
         InitiatorIP=coalesce(tostring(InitiatedBy.user.ipAddress),tostring(InitiatedBy.app.ipAddress))
| extend Geo=iff(isempty(InitiatorIP),dynamic(null),geo_info_from_ip_address(InitiatorIP))
| extend Country=tostring(Geo["country"]),
         Latitude=todouble(Geo["latitude"]),
         Longitude=todouble(Geo["longitude"])
| join kind=leftouter (CountryBaseline) on $left.AccountUpn==$right.AccountUpn
| extend BaselineCountries=coalesce(BaselineCountries,dynamic([]))
| extend HasBaseline=array_length(BaselineCountries)>0
| extend HasGeo=isnotempty(Country)
| extend IsNewCountry=iff(HasGeo==false,bool(null),not(set_has_element(BaselineCountries,Country)))
| extend RiskLevelAggregated=case(Result !~ "success",100,HasGeo==false,50,HasBaseline==false,50,IsNewCountry==true,50,0)
| extend RiskLevel=case(RiskLevelAggregated==100,"High",RiskLevelAggregated==50,"Medium",RiskLevelAggregated==10,"Low","None/Unknown")
| extend RiskReason=case(Result !~ "success","Audit operation not success",HasGeo==false,"No geo data for initiator IP",HasBaseline==false,"No 90d country baseline for user",IsNewCountry==true,"New country vs 90d sign-in baseline","Within baseline")
| project Timestamp,AccountUpn,InitiatorUpn,IPAddress=InitiatorIP,Country,Latitude,Longitude,BaselineCountryCount,BaselineCountries,IsNewCountry,RiskLevelAggregated,RiskLevel,RiskReason,OperationName,Result,CorrelationId,AdditionalDetails
| sort by Timestamp desc

Explanation

This query is designed to analyze and assess the risk of authentication events based on geographic information over a 90-day period. Here's a simplified breakdown of what it does:

  1. Define Lookback Period: The query looks back over the last 90 days.

  2. Establish Baseline:

    • It first gathers data from EntraIdSignInEvents to create a baseline of countries from which users have signed in during the last 90 days.
    • It filters out events without a user principal name (AccountUpn) or IP address.
    • It uses the IP address to determine the country of origin for each sign-in and summarizes this data by user.

  3. Analyze Audit Logs:

    • It then examines AuditLogs for the same 90-day period, focusing on events related to Microsoft Authenticator and similar authentication methods.
    • It extracts relevant details such as the timestamp, user principal name, initiator's user principal name, and IP address.
    • It determines the geographic location (country, latitude, longitude) from the IP address.

  4. Join and Compare:

    • The query joins the audit log data with the baseline data to compare current sign-in countries against the baseline countries for each user.
    • It checks if the current country is new compared to the baseline.

  5. Risk Assessment:

    • It calculates a risk level based on several conditions:
      • If the operation was not successful, it assigns a high risk.
      • If there's no geographic data or baseline, or if the country is new, it assigns a medium risk.
      • Otherwise, it assigns a low or no risk.
    • It provides a reason for the assigned risk level.

  6. Output:

    • The query projects relevant fields such as timestamp, user details, geographic information, baseline data, risk levels, and reasons.
    • It sorts the results by timestamp in descending order, showing the most recent events first.

This query helps identify potentially risky authentication events by comparing current sign-in locations against historical data, allowing for proactive security measures.

Details

Daniel Card profile picture

Daniel Card

Released: January 24, 2026

Tables

EntraIdSignInEventsAuditLogs

Keywords

EntraIdSignInEventsAuditLogsAccountUpnIPAddressGeoCountryNameTimestampTimeGeneratedAdditionalDetailsMicrosoftAuthenticatorPhoneAppNotificationPhoneAppOTPAuthenticatorInitiatorUpnInitiatorIPLatitudeLongitudeBaselineCountriesBaselineCountryCountRiskLevelAggregatedRiskLevelRiskReasonOperationNameResultCorrelationId

Operators

let|whereisnotemptyextendgeo_info_from_ip_addresstostringsummarizemake_setdcountagohas_anycoalesceiffisemptydynamicjoinonarray_lengthset_has_elementcase!~projectsort bydesc

Actions