Top 10 KQL Queries Every Detection Engineer Should Know

Detection Potential DLL Hijacking Cases

Identifies suspicious DLL loading events by matching them against a curated list of known vulnerable DLLs from Hijacklibs.net.

• Provides Actionable Context: Enriches detections with detailed metadata like file paths, initiating processes, user accounts, and external intelligence for quick analysis.
• Matches Vulnerable DLLs: Compares SHA256 hashes of loaded DLLs against the known list of vulnerable executables.
• Filters Key Events: Pinpoints only those events where vulnerable DLLs are present to reduce noise in detection results.

Grab the KQL query below

let dll_hijacking_source = externaldata
(
    Name:string,
    Author:string,
    Created:string,
    Vendor:string,
    CVE:string,
    ExpectedLocations:string,
    VulnerableExecutablePath:string,
    VulnerableExecutableType:string,
    VulnerableExecutableAutoElevated:string,
    VulnerableExecutablePrivilegeEscalation:string,
    VulnerableExecutableCondition:string,
    VulnerableExecutableSHA256:string,
    VulnerableExecutableEnvironmentVariable:string,
    Resources:string,
    Acknowledgements:string,
    URL:string
)
[@"https://hijacklibs.net/api/hijacklibs.csv"] 
with (format="csv", ignoreFirstRecord=True);

DeviceImageLoadEvents
| join kind=inner (dll_hijacking_source) on $left.SHA256 == $right.VulnerableExecutableSHA256
| where isnotempty(VulnerableExecutableSHA256)
| summarize by 
    DeviceId, 
    DeviceName, 
    ActionType, 
    FileName, 
    FolderPath, 
    SHA256, 
    VulnerableExecutableSHA256, 
    Resources, 
    Acknowledgements, 
    URL, 
    FileSize, 
    InitiatingProcessAccountName, 
    InitiatingProcessAccountDomain, 
    InitiatingProcessAccountUpn, 
    InitiatingProcessIntegrityLevel, 
    InitiatingProcessFileName, 
    InitiatingProcessVersionInfoCompanyName, 
    Name, 
    Author, 
    VulnerableExecutableType, 
    VulnerableExecutableEnvironmentVariable;

Detecting these unusual patterns is critical because traditional security tools often miss them because DLL use is legitimate in many cases.

Identify differences between EntraID user phone number & its MFA authentication number

Identifies mismatches between the phone provider in the EntraID user profile and the phone # used to authenticate via MFA.

• Monitors User Updates: Analyzes changes to user profiles, particularly updates to strong authentication settings, to flag potential anomalies.
• Epic Data Highlights: Summarizes discrepancies by user account, profile phone number, MFA phone number, and country for focused investigation.

Grab the KQL query below

CloudAppEvents
| where ActionType == "Update user." and RawEventData contains "StrongAuthentication"
| extend target = RawEventData.ObjectId
| mvexpand ModifiedProperties = parse_json(RawEventData.ModifiedProperties)
| where ModifiedProperties matches regex @"\+\d{1,3}\s*\d{9,}"
| mvexpand ModifiedProperties = parse_json(ModifiedProperties)
| where ModifiedProperties contains "NewValue" and ModifiedProperties matches regex @"\+\d{1,3}\s*\d{9,}"
| extend PhoneNumber = extract(@"\+\d{1,3}\s*\d{9,}", 0, tostring(ModifiedProperties))

// Joining IdentityInfo table to get phone numbers provided on the user profile
| join kind=inner (IdentityInfo) on $left.AccountDisplayName == $right.AccountDisplayName

// Filtering to show only different phone numbers
| where Phone != PhoneNumber
| summarize by AccountDisplayName, Phone, PhoneNumber, Country;

Detecting these unusual patterns is critical because traditional security tools often miss them because DLL use is legitimate in many cases.

‍

Identify communication atrisk due to encryption algorithm ciphers in use

Identifies where there are multiple connections established using non-recommended or non-valid datagram transport layer security (DTLS), which could mean that users are not protected against eavesdropping, tampering or message forgery.

• Identifies Weak Encryption Usage: Detects network connections using outdated or insecure encryption algorithms (e.g., Datagram Transport Layer Security or DTLS) to flag potential vulnerabilities.
• Leverages Trusted Resources: Uses encryption algorithm data from the globally recognized iana.org database to validate and assess the security of the algorithms in use.
• Analyzes Connection Patterns: Highlights repetitive connections with weak encryption to prioritize risks and address communication security gaps.

Grab the KQL query below

let CVE = externaldata(
    Value:string, 
    Description:string, 
    DTLSOK:string, 
    Recommended:string, 
    Reference:string
)
[@"https://www.iana.org/assignments/tls-parameters/tls-parameters-4.csv"] 
with (format="csv");

DeviceNetworkEvents
| extend cipher = parse_json(AdditionalFields).cipher
| extend cipher = tostring(cipher)
| where isnotempty(cipher)
| extend RemoteIPCountry = geo_info_from_ip_address(RemoteIP).country
| extend RemoteIPCountry = tostring(RemoteIPCountry)
| join kind=inner (CVE) on $left.cipher == $right.Description

// Filtering non-recommended TLS versions or communications where DTLS is not OK
| where DTLSOK has "N" or Recommended has "N"

// Creating a new column for RFC URL Reference
| extend RFCLink = tolower(Reference)
| extend RFCLink = substring(RFCLink, 1, strlen(RFCLink) - 2)
| extend RFCLink = strcat("rfc-editor.org/rfc/", RFCLink, ".html")
| extend RFCLink = tostring(RFCLink)

// Sorting by Totalconnections to detect repetitive low-encryption connections
| summarize Totalconnections = count() 
    by RemoteIP, RemoteIPCountry, TenantId, DeviceName, LocalIP, 
       ActionType, RemotePort, Protocol, cipher, Value, 
       Description, DTLSOK, Recommended, Reference, RFCLink
| order by Totalconnections;

Weak encryption algorithms are often exploited in Man-in-The-Middle (MITM) attacks.

Anonymous access to files by Suspicious IP Addresses

Identifies connections using anonymous proxy activities with OneDrive/Sharepoint files, such as cases where an external user is accessing a SharePoint/OneDrive file shared without any restrictions.

• Detects Anonymous Proxy Access: Identifies connections to OneDrive/SharePoint files made via anonymous proxies, flagging potentially unauthorized or suspicious activities.
• Targets Shared Files Without Restrictions: Focuses on files shared publicly without security restrictions, which could allow malicious actors external access to sensitive data.
• Analyzes Malicious IP Activity: Tracks access from known or potentially malicious IP addresses interacting with potentially sensitive files/data.

Grab the KQL query below

CloudAppEvents
| where IsAnonymousProxy == 1
| where Application !has "Exchange"
| where RawEventData !has "@"
| extend userID = RawEventData.UserId
| extend SourceRelativeUrl = RawEventData.SourceRelativeUrl
| project 
    Timestamp, 
    ObjectName, 
    ObjectType, 
    SourceRelativeUrl, 
    CountryCode, 
    IPAddress, 
    userID, 
    ActionType, 
    Application, 
    DeviceType, 
    OSPlatform, 
    ISP, 
    IsAdminOperation, 
    AccountType, 
    IsImpersonated, 
    UserAgentTags, 
    OAuthAppId, 
    RawEventData
| sort by ObjectName;

Anonymous access through proxies is a favorite technique in reconnaissance phases of attacks, where adversaries probe shared resources to identify weak points for further exploitation.

Identifying methods used to establish secure communication over insecure channels

Identifies if the current encryption to secure your communications is enough or if due to the level of sensitivity of your information, would need to be reviewed.

• Evaluates Encryption Protocols: Identifies whether current encryption methods (e.g., elliptic curve and Diffie-Hellman groups) used for secure communication are recommended or potentially insecure based on IANA classifications.
• Flags Non-Recommended Practices: Detects communication channels using encryption algorithms or configurations that are not marked as "Recommended" or fail Datagram Transport Layer Security (DTLS) checks.
• Provides Insight into Device Security: Enriches results with device operating system information and connection details to help prioritize and investigate security risks effectively.

Grab the KQL query below

let Courve_Source = externaldata(
    Value:int, 
    Description:string, 
    DTLSOK:string, 
    Recommended:string, 
    Reference:string
)
[@"https://www.iana.org/assignments/tls-parameters/tls-parameters-8.csv"] 
with (format="csv");

DeviceNetworkEvents
| extend curve = parse_json(AdditionalFields).curve
| extend curve = tostring(curve)
| extend server_name = parse_json(AdditionalFields).server_name
| extend server_name = tostring(server_name)
| extend RemoteIPCountry = geo_info_from_ip_address(RemoteIP).country
| extend RemoteIPCountry = tostring(RemoteIPCountry)
| join kind=inner (Courve_Source) on $left.curve == $right.Description

// Filtering non-recommended curve versions or communications where DTLS is not OK
| where DTLSOK has "N" or Recommended has "N"

// Adding DeviceInfo table to get Device OS details
| lookup kind=inner (DeviceInfo) on $left.DeviceName == $right.DeviceName

// Summarizing by key connection details
| summarize Totalconnections = count() 
    by RemoteIP, RemoteIPCountry, OSDistribution, OSPlatform, 
       OSVersion, ClientVersion, OSBuild, OSArchitecture, 
       DeviceName, LocalIP, ActionType, RemotePort, Protocol, 
       server_name, curve, DTLSOK, Recommended
| order by Totalconnections;

Poorly implemented ECC can backfire, as attackers can exploit improper configurations, turning the tool of security into an unintended vulnerability.

Suspicious SSH connection inspections

Detects unauthorized SSH connection attempts where external sources use unusual parameters like babeld (a network protocol) or conker (a network configuration manager), indicating potential suspicious activity such as data exfiltration or malicious lateral movement within the network.

• Detects Suspicious SSH Activity: Identifies SSH connection attempts where the external source uses uncommon or suspicious parameters like babeld (network protocol) or conker (network configuration manager), which could indicate malicious intent.
• Flags SSH Threats: Unauthorized SSH access, lateral movement within networks, or potential data exfiltration through SSH tunneling.
• Provides Context for Investigation: Enriches results with details such as source IP, country, server parameters, and authentication success to help analysts understand and respond to threats.

Grab the KQL query below

DeviceNetworkEvents
| where ActionType has "SshConnectionInspected"
| extend 
    CountryIP = tostring(geo_info_from_ip_address(RemoteIP).country), 
    server = tostring(parse_json(AdditionalFields).server)
| where isnotempty(CountryIP)
| where server contains "babeld" or server contains "conker"
| project 
    Timestamp, 
    ActionType, 
    RemoteIP, 
    RemotePort, 
    CountryIP, 
    server, 
    auth_success = tostring(parse_json(AdditionalFields).auth_success), 
    client = tostring(parse_json(AdditionalFields).client);

SSH tunneling is often nicknamed the "backdoor’s favorite tool" because it provides attackers with encrypted channels to bypass detection systems and move undetected within a network.

Detect PnP devices connected to your endpoint machines

Identifies PnP devices connected to endpoint machines to address concerns such as unauthorized USB or external drives on critical servers, preventing printers on sensitive systems, and identifying unfamiliar devices from unknown vendors.

• Monitors Connected Devices: Tracks Plug and Play (PnP) devices like USB drives, printers, and external hardware connected to endpoint machines to identify potential security risks.
• Identifies Untrusted or Unusual Devices: Flags devices from unknown vendors, untrusted providers, or those that haven’t been updated, ensuring only secure and approved hardware is used.
• Protects Sensitive Systems: Focuses on safeguarding critical servers and endpoints with sensitive data, preventing unauthorized access or data leakage through connected devices.

Grab the KQL query below

let connected = DeviceEvents
| where ActionType has "PnpDeviceConnected"
| extend 
    ClassName = tostring(parse_json(AdditionalFields).ClassName),
    DeviceDescription = tostring(parse_json(AdditionalFields).DeviceDescription),
    ClassID = tostring(parse_json(AdditionalFields).ClassId),
    DevID0 = tostring(parse_json(AdditionalFields).DeviceId);

// Case 1: Identify what is connected to a device → | where DeviceName has ""
// Case 2: Filter the type of PnP devices → | where ClassName has ""
// Case 3: Exclude specific PnP devices → | where ClassName !in ("Monitor", "Mouse");

DeviceEvents
| where ActionType has "PnpDeviceAllowed"
| extend 
    DeviceInstanceId = tostring(parse_json(AdditionalFields).DeviceInstanceId),
    DriverProvider = tostring(parse_json(AdditionalFields).DriverProvider),
    DriverDate = tostring(parse_json(AdditionalFields).DriverDate),
    DeviceUpdated = tostring(parse_json(AdditionalFields).DeviceUpdated),
    DriverVersion = tostring(parse_json(AdditionalFields).DriverVersion),
    DriverName = tostring(parse_json(AdditionalFields).DriverName)
| join kind=inner (connected) on $left.DeviceInstanceId == $right.DevID0

// Case 4: Identify PnP devices from untrusted providers → | where DriverProvider !in ("Microsoft", "Logitech")
// Case 5: Identify non-updated PnP devices → | where DeviceUpdated == "false"

| distinct DeviceName, ClassName, DeviceDescription, ClassID, DriverProvider, DriverDate, DeviceUpdated, DriverVersion, DriverName;

USB-based attacks, often referred to as "sneakernet" attacks, have seen a significant resurgence in recent years. In 2024, 51% of malware attacks were designed to exploit USB devices, a substantial increase from 9% in 2019.

Classifying HTTP status codes pivot table query

This query generates a detailed table with extended information about HTTP status codes to help identify potential threats. Think of it as a "pivot table" that supports multiple queries tailored to your specific criteria.

• Analyzes HTTP Status Codes: Creates a detailed table of HTTP status codes and their associated metadata (e.g., type, description, and potential threats) to identify suspicious or abnormal activity.
• Highlights Potential Threats: Uses a curated list of status codes to detect anomalies such as unauthorized access, misconfigurations, or suspicious traffic patterns (e.g., repeated 4xx or 5xx errors).
• Provides Actionable Insights: Enriches data with information like remote IP location, method, and referring site to give analysts the context needed to investigate and respond to potential issues.

Grab the KQL query below

let status_codes = externaldata(statuscode: string, Type_code: string, Description: string, Possible_Threat: string)
[@"https://raw.githubusercontent.com/Sergio-Albea-Git/-Defender-XDR-/main/Security-Lists/status_code.csv"] 
with (format="csv", ignoreFirstRecord=True);

DeviceNetworkEvents
| extend 
    status_code = tostring(parse_json(AdditionalFields).status_code),
    SiteIPCountry = tostring(geo_info_from_ip_address(RemoteIP).country),
    Method = tostring(parse_json(AdditionalFields).method),
    Site = tostring(parse_json(AdditionalFields).referrer),
    host = tostring(parse_json(AdditionalFields).host)
| where isnotempty(SiteIPCountry)
| where isnotempty(status_code)
| where isnotempty(Site)
// Excluding some cases to refine results
| where Site !has "www.google." or Site !has "support.amd.com"
| lookup kind=inner (status_codes) on $left.status_code == $right.statuscode
// You can add other fields such as Method or AdditionalFields, but this will increase the number of results
| summarize 
    make_list(status_code), 
    make_list(DeviceName), 
    Count = count() 
    by RemoteIP, SiteIPCountry, Site, Type_code, Description, Possible_Threat
| order by Count;

Attackers often exploit HTTP status codes during web application reconnaissance, using errors like 403 (Forbidden) or 404 (Not Found) to map application vulnerabilities or identify restricted resources!

Spotting malicious ISPs through activity monitoring

Identifies sign-in attempts over the past 30 days to identify potentially malicious activity from Internet Service Providers (ISPs). Sorting by Suspicious Percentage helps pinpoint ISPs with multiple IPs and zero successful sign-in attempts, a strong indicator of malicious behavior.

• Monitors ISP Login Behavior: Analyzes sign-in attempts over the past 30 days to identify ISPs exhibiting suspicious behavior, such as multiple IPs with no successful logins.
• Calculates Suspicious Activity Metrics: Uses the ratio of failed login attempts (e.g., wrong passwords, locked accounts) to total attempts to calculate a Suspicious Percentage for each ISP.
• Prioritizes Risky ISPs: Orders ISPs by their SuspiciousPercentage to identify and tag those most likely associated with malicious activities for further investigation or blocking.

Grab the KQL query below

IdentityLogonEvents
| where Timestamp > ago(30d)
| summarize 
    Different_IPs = make_set(IPAddress), 
    Total_different_IPs = dcount(IPAddress), 
    Total_sign_attempts = count(), 
    Suspicious_Sign_attempt = countif(
        (ActionType has "OldPassword") or 
        (FailureReason has "WrongPassword") or 
        (FailureReason has "validating credentials due to invalid username or password.") or 
        (FailureReason has "The account is locked, you've tried to sign in too many times with an incorrect user ID or password.") or 
        (FailureReason has "Authentication failed.") or 
        (FailureReason has "UnknownUser") or 
        (FailureReason has "The user account is disabled.")
    ),
    Success_Sign_attempt = count(ActionType has "LogonSuccess"),
    Issues_Sign_attempt = countif(
        (FailureReason has "The session is not valid due to password expiration or recent password change.") or 
        (FailureReason has "General failure")
    ) 
    by ISP, Location
| extend 
    SuspiciousRatio = Suspicious_Sign_attempt * 1.0 / Total_sign_attempts, 
    ValidRatio = Success_Sign_attempt * 1.0 / Total_sign_attempts, 
    IssuesRatio = Issues_Sign_attempt * 1.0 / Total_sign_attempts
| extend 
    SuspiciousPercentage = SuspiciousRatio * 100, 
    ValidPercentage = ValidRatio * 100, 
    IssuesPercentage = IssuesRatio * 100
| order by SuspiciousPercentage;

Attackers often use a single ISP with multiple IP addresses to launch large-scale login attempts, leveraging tools to automate brute force attacks.

Spotting malicious ISPs through activity monitoring

Identifies devices in the DeviceEvents table that are initiating RDP connections and maps the location of the remote IP addresses. Note that the LocalIP column in the table, despite its name, may represent remote IPs establishing the connection, which can be misleading.

• Identifies External RDP Connections: Detects devices initiating Remote Desktop Protocol (RDP) connections and tracks the remote IP addresses involved.
• Maps Remote Locations: Extracts and displays the geographical locations (countries) of the remote IPs connecting via RDP to provide context for potential threats.
• Clarifies Misleading Data: Highlights that the "LocalIP" field may actually represent remote IPs in some cases, ensuring accurate analysis.

Grab the KQL query below

DeviceEvents
| where ActionType contains "RemoteDesktopConnection"
| extend Country_IP = tostring(geo_info_from_ip_address(LocalIP).country)
| where isnotempty(Country_IP)
| project 
    Timestamp, 
    DeviceName, 
    ActionType, 
    LocalIP, 
    LocalPort, 
    Country_IP, 
    ReportId, 
    DeviceId;

RDP is one of the most common vectors for ransomware attacks.

Watch the Detection Engineering Dispatch episode below, as Alex and Sergio Albea break down the top KQL queries for catching everything from DLL hijacking to anonymous OneDrive access.

Sergio’s KQL detection content