KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 05 MI Out Of Hours Timeline 90d

Query

// Hunt     : Workload Identity - Managed Identity Out-of-Hours Full Timeline (90d)
// Tactics  : Persistence, LateralMovement
// MITRE    : T1078.004, T1550
// Purpose  : Full 90-day history of Managed Identities authenticating outside business hours
//            (Mon–Fri 06:00–22:00). Baseline normal patterns, identify persistent anomalies,
//            and surface MIs accessing sensitive resources at unexpected times.
//==========================================================================================

let SensitiveResources = dynamic([
    "Key Vault", "Storage", "SQL", "Cognitive", "OpenAI",
    "Synapse", "Service Bus", "Event Hub", "Container Registry"
]);
// isfuzzy=true: AADManagedIdentitySignInLogs only exists when the Managed Identity diagnostic connector is enabled
union isfuzzy=true
    (AADManagedIdentitySignInLogs | invoke ExcludeAllowlistedIPs()),
    (datatable(TimeGenerated:datetime, ResultType:string, ServicePrincipalName:string,
               ServicePrincipalId:string, ResourceDisplayName:string, SourceAppClientId:string)[])
| where TimeGenerated > ago(90d)
| where ResultType == "0"
| extend Hour        = hourofday(TimeGenerated)
| extend DayOfWeek   = dayofweek(TimeGenerated)
| extend IsWeekend   = DayOfWeek in (0d, 6d)
| extend IsOOH       = Hour < 6 or Hour > 22 or IsWeekend
| extend IsSensitive = ResourceDisplayName has_any (SensitiveResources)
| summarize
    TotalAuths        = count(),
    OOHAuths          = countif(IsOOH),
    BizHoursAuths     = countif(not(IsOOH)),
    SensitiveOOH      = countif(IsOOH and IsSensitive),
    UniqueResources   = dcount(ResourceDisplayName),
    OOHResources      = make_set_if(ResourceDisplayName, IsOOH, 15),
    SensitiveResources_List = make_set_if(ResourceDisplayName, IsOOH and IsSensitive, 10),
    UniqueSourceApps  = dcount(SourceAppClientId),
    SourceApps        = make_set(SourceAppClientId, 10),
    OOHDays           = dcountif(bin(TimeGenerated, 1d), IsOOH),
    FirstOOH          = minif(TimeGenerated, IsOOH),
    LastOOH           = maxif(TimeGenerated, IsOOH)
    by ServicePrincipalName, ServicePrincipalId
| where OOHAuths > 0
| extend OOHRatio = round(todouble(OOHAuths) / todouble(TotalAuths) * 100, 1)
| order by SensitiveOOH desc, OOHAuths desc

Explanation

This query is designed to analyze the authentication patterns of Managed Identities over the past 90 days, specifically focusing on activities occurring outside of regular business hours (Monday to Friday, 6 AM to 10 PM). The goal is to identify any unusual or potentially suspicious behavior, such as access to sensitive resources during these off-hours.

Here's a simplified breakdown of what the query does:

  1. Sensitive Resources List: It defines a list of sensitive resources like Key Vault, Storage, SQL, etc., which are considered critical.

  2. Data Source: It pulls data from AADManagedIdentitySignInLogs, which logs Managed Identity sign-ins, and excludes any IPs that are on an allowlist.

  3. Time Frame: It filters the data to include only the last 90 days.

  4. Successful Authentications: It considers only successful authentication attempts (where ResultType is "0").

  5. Time Analysis: It calculates the hour and day of the week for each authentication attempt to determine if it occurred outside of business hours or on weekends.

  6. Sensitive Access: It checks if the accessed resource is among the predefined sensitive resources.

  7. Summary Statistics: For each Managed Identity (identified by ServicePrincipalName and ServicePrincipalId), it calculates:

    • Total number of authentications.
    • Number of authentications outside business hours (OOH).
    • Number of authentications during business hours.
    • Number of sensitive resource accesses outside business hours.
    • Unique resources accessed and those accessed outside business hours.
    • Unique source applications used for authentication.
    • Number of days with OOH activity.
    • First and last OOH authentication timestamps.

  8. Filtering and Sorting: It filters out identities with no OOH authentications and sorts the results by the number of sensitive OOH accesses and total OOH authentications.

The query helps in establishing a baseline of normal behavior and identifying anomalies, particularly focusing on Managed Identities accessing sensitive resources at unexpected times.

Details

David Alonso profile picture

David Alonso

Released: April 21, 2026

Tables

AADManagedIdentitySignInLogs

Keywords

WorkloadIdentityManagedIdentityAuthenticationResourcesLogsPatternsAnomaliesAccessTimes

Operators

letdynamicunionisfuzzyinvokedatatablewhereagoextendhourofdaydayofweekinhas_anysummarizecountcountifnotdcountmake_set_ifmake_setdcountifbinminifmaxifbyroundtodoubleorder by

Actions