KQL Queries

This is a home for KQL queries that I find useful in investigations and other scenarios.

Using Results from One Table in Another

Assign the results of the SecurityAlert table to the variable names , then close the query with a ; Write the query for the table that will use the names variable directly below it.

let names =
SecurityAlert
| where DisplayName has "AWS IAM Deactivation of MFA"
| distinct tostring(parse_json(Entities)[0].DisplayName);
AWSCloudTrail
| where EventName has "DeactivateMFADevice"
| where UserIdentityArn has_any (names)

Advanced-Joined Rules

Search for Mailboxes of Users who Hit Malicious Links

The following query identifies users who have visited a malicious URL and then checks whether their mailboxes have had any rule modifications. Threat actors commonly use this tactic to maintain access for phishing and credential harvesting attacks while preventing the original target from seeing responses to the phishing email.

let usersList=
    _Im_WebSession(url_has_any="winstnet80nss") //Specify Malicious URL
    | where User != "[e-mail]" //Specify users NOT to be included in the search
    | project
        TimeGenerated,
        EventProduct,
        User,
        SrcPrivateIpAddr,
        SrcPublicIpAddr,
        Url,
        DstIpAddr,
        HttpReferrer,
        HttpRequestMethod,
        HttpStatusCode,
        DvcAction,
        UserAgent,
        FileName,
        ThreatName
    | distinct User; //Remove duplicates
OfficeActivity
| where Operation in ("Add-InboxRule", "Set-InboxRule", "Remove-InboxRule")
| where isnotnull(TargetUserOrGroupName)  // Filters out system events
| join kind=inner (usersList) on $left.UserId == $right.User
| project TimeGenerated, UserId, Operation, TargetUserOrGroupName, OfficeWorkload, ClientIP, Parameters

UPN Login Lookups Across Multiple Tables

This query builds a unified view of a single user’s authentication activity by correlating interactive, non-interactive, and hybrid/on-prem identity events across multiple Microsoft identity data sources. It pulls from SigninLogs, IdentityLogonEvents, and AADNonInteractiveUserSignInLogs, normalizes inconsistent schemas by remapping columns during projection, and merges everything into a single investigation-ready dataset. The result is a joined, chronological view of how and where an identity authenticated across cloud and hybrid environments, suitable for threat hunting, incident response, and behavioral analysis.

let investUPN = "[e-mail]"; // Set UPN to search on
let investTime = 30d; // Set Lookback Range
union isfuzzy=true withsource=SourceTable
(
    SigninLogs
    | where TimeGenerated >= ago(investTime)
    | where UserPrincipalName =~ investUPN
    | project TimeGenerated,AuthenticationProtocol, Identity, UserPrincipalName, ResultType, ResultSignature, ResultDescription, IPAddress, Location, LocationDetails, AuthenticationDetails,DeviceDetail, ResourceDisplayName,AppDisplayName, IsInteractive, UserAgent
    | as SigninLogs
),
(
    IdentityLogonEvents
    | where TimeGenerated >= ago(investTime)
    | where AccountUpn =~ investUPN
    | project TimeGenerated,AuthenticationProtocol = Protocol, UserPrincipalName = AccountUpn,Identity = AccountDisplayName, ActionType, AppDisplayName = Application,LogonType, FailureReason, DeviceName, DeviceType, OSPlatform, Location, IPAddress, Port, DestinationDeviceName, DestinationIPAddress, DestinationPort, TargetDeviceName
    | as IdentityLogonEvents
),
(
    AADNonInteractiveUserSignInLogs
    | where TimeGenerated >= ago(investTime)
    | where UserPrincipalName =~ investUPN
    | project TimeGenerated, AuthenticationProtocol,UserPrincipalName, Identity, Category, ResultType, ResultSignature, ResultDescription,IPAddress, Location, LocationDetails, AppDisplayName, AuthenticationDetails, DeviceDetail, UserAgent
    | as AADNonInteractiveUserSignInLogs
)

• union isfuzzy=true – Allows tables with different schemas to be merged without failing, filling missing fields with nulls instead of breaking the query.

• withsource=SourceTable – Preserves the original table name for each event, making it easy to distinguish cloud, hybrid, and non-interactive authentication activity.

• Case-insensitive matching with =~ – Ensures identity matching works even when UPN casing differs across data sources.

• Schema normalization via project renaming – Aligns fields like Protocol, AccountUpn, and Application into consistent columns so the merged output is actually usable.

• Parameterized investigation scope – Variables for user and time range make the query reusable and easy to adapt for different investigations.

• Early time filtering – Applying the time window before the union keeps performance tight and reduces unnecessary data processing.

IP Login Lookups Across Multiple Tables

This query pivots on a single IP address to identify all authentication activity associated with it across cloud, hybrid, and non-interactive identity planes. It correlates Azure AD interactive sign-ins, on-prem or hybrid logon events, and background/service authentications into one normalized dataset, allowing you to quickly assess whether an IP is tied to user logins, automated processes, or broader credential abuse. The output is designed for rapid triage and scoping when investigating a suspicious or known-malicious source address.

let malIP = "198.98.51.189"; // Set the IP you want to search on
let investTime = 30d; // Set Lookback Range
union isfuzzy=true withsource=SourceTable
(
    SigninLogs
    | where TimeGenerated >= ago(investTime)
    | where IPAddress == malIP
    | project TimeGenerated,AuthenticationProtocol, Identity, UserPrincipalName, ResultType, ResultSignature, ResultDescription, IPAddress, Location, LocationDetails, AuthenticationDetails,DeviceDetail, ResourceDisplayName,AppDisplayName, IsInteractive, UserAgent
    | as SigninLogs
),
(
    IdentityLogonEvents
    | where TimeGenerated >= ago(investTime)
    | where IPAddress == malIP
    | project TimeGenerated,AuthenticationProtocol = Protocol, UserPrincipalName = AccountUpn,Identity = AccountDisplayName, ActionType, AppDisplayName = Application,LogonType, FailureReason, DeviceName, DeviceType, OSPlatform, Location, IPAddress, Port, DestinationDeviceName, DestinationIPAddress, DestinationPort, TargetDeviceName
    | as IdentityLogonEvents
),
(
    AADNonInteractiveUserSignInLogs
    | where TimeGenerated >= ago(investTime)
    | where IPAddress == malIP
    | project TimeGenerated, AuthenticationProtocol,UserPrincipalName, Identity, Category, ResultType, ResultSignature, ResultDescription,IPAddress, Location, LocationDetails, AppDisplayName, AuthenticationDetails, DeviceDetail, UserAgent
    | as AADNonInteractiveUserSignInLogs
)

• IP-centric investigation model – Instead of starting from a user, the query pivots on a single IP to reveal all identities and authentication types associated with it.

• union isfuzzy=true – Safely merges tables with different schemas without query failure, filling missing fields with nulls.

• withsource=SourceTable – Retains the originating table for each event, making it clear whether activity is interactive, non-interactive, or hybrid.

• Schema normalization via project renaming – Aligns fields like Protocol, AccountUpn, and Application so results from different tables can be analyzed together.

• Parameterized investigation scope – The IP and lookback window are defined once, making the query reusable for rapid IOC pivots.

• Early time and IP filtering – Filters are applied before projection and union, keeping performance tight and reducing noise during investigations.

Emails

Search Malicious URLs in Emails

The below query will search the EmailUrlInfo table for any emails that contain the malicious URL. This will retrieve the NetworkMessageId from the email, and then feeds that in to the EmailEvents table, where it pulls the email based on the NetworkMessageID.

let badMail =
EmailUrlInfo
| where Url contains "bad.url.com"
| project  NetworkMessageId, IdentifiedURL = Url;
EmailEvents
| where NetworkMessageId in (badMail)
| join kind=inner (badMail) on NetworkMessageId
| project TimeGenerated,EmailDirection, DeliveryAction, DeliveryLocation, OrgLevelAction,RecipientEmailAddress, SenderFromAddress, SenderDisplayName, Subject, IdentifiedURL, UrlCount, NetworkMessageId, InternetMessageId, AuthenticationDetails

• join kind=inner (badMail) on NetworkMessageId → Brings in the identified URL from EmailUrlInfo, making it more informative.

• project IdentifiedUrl → Displays the detected URL along with email details.