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

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.