5 KQL Queries for Malicious Network Traffic

Microsoft Sentinel provides powerful tools for security monitoring and threat hunting. A crucial aspect of this is analyzing network traffic logs to detect suspicious or malicious activity. Kusto Query Language (KQL) is the key to unlocking insights from the vast amounts of network data collected by Sentinel from various sources like firewalls, endpoints, and network appliances.

Here are 5 KQL Queries for Malicious Network Traffic to help you get started with network threat hunting in Sentinel. When you find your suspicious IP addresses a quick way to check them is to use IP Recon.

Prerequisites#

Before using these queries, ensure you have:

A configured Microsoft Sentinel workspace.
Relevant data connectors enabled and ingesting logs. This includes:
- Firewall logs (e.g., Palo Alto, Fortinet, Check Point via Syslog or CEF, Azure Firewall). These often populate the CommonSecurityLog table.
- Microsoft Defender for Endpoint (MDE) integration, which populates tables like DeviceNetworkEvents.
- Windows DNS logs (DnsEvents) or other DNS service logs.
- Threat Intelligence platform connectors enabled (highly recommended).
- Azure Network Watcher logs (VMConnection) if monitoring Azure VM traffic flow.

Keep in Mind Your environment may have different tables and some of these queries will need to be tweak to suit your situation.

Filtering Internal Traffic#

Often, you want to focus on traffic crossing your network boundary (external <-> internal) rather than internal-to-internal communication. You can define your internal IP ranges and use them to filter queries.

Here’s a common pattern using a let statement. Place this at the beginning of your query:

let PrivateIPRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16", "127.0.0.1/32", "::1/128"]); // Add any other internal ranges specific to your org
// --- Query Body Starts Below ---
// Example Usage within a query:
// SomeTable
// | where ipv4_is_match(SourceIP, PrivateIPRanges) == false // Source is External
//   and ipv4_is_match(DestinationIP, PrivateIPRanges) == true // Destination is Internal

You can adapt the where clause logic depending on whether you want to see traffic from external, to external, or specifically exclude internal-to-internal. The ipv4_is_match() function efficiently checks if an IP address falls within any of the specified CIDR ranges.

Example KQL Queries for Network Threat Hunting#

Note: Field names (like SourceIP, DestinationIp, SentBytes, DeviceAction) can vary depending on the specific log source and how it’s parsed (e.g., via CEF, built-in connectors, or custom parsers). You may need to adjust these queries to match the exact schema of your data.

Query 1: Denied Outbound Connections to Threat Intel IPs (CommonSecurityLog)#

This query uses the CommonSecurityLog table (common for firewalls) to find attempts from internal devices to connect to IP addresses flagged by Microsoft’s Threat Intelligence feeds, where the firewall denied the connection. This indicates potentially blocked malware C2 communication or scanning.

Code snippet

// Query 1: Denied Outbound Connections to Threat Intel IPs (Firewall Logs)
let PrivateIPRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]); // Define your internal ranges
let lookback = 1d; // Set the lookback period (e.g., 1 day)
// Get Threat Intel IPs
let TI_Indicators = ThreatIntelligenceIndicator
    | where TimeGenerated > ago(lookback)
    | where isnotempty(NetworkIP)
    | summarize make_set(Description) by NetworkIP;
// Query Firewall Logs
CommonSecurityLog
| where TimeGenerated > ago(lookback)
| where DeviceVendor has_any ("PaloAltoNetworks", "Fortinet", "Cisco", "CheckPoint", "Microsoft") // Adjust firewall vendors as needed
| where DeviceAction in ("Deny", "Block", "Drop") // Adjust action strings based on your firewall logs
// Attempt to parse IPs if not standard fields - this may need significant adjustment based on raw log format in AdditionalExtensions or Message field
// | parse kind=relaxed Message with * 'src=' SourceIP ' ' * 'dst=' DestinationIP ' ' * // Example parsing
// Or use existing fields if available:
| where isnotempty(SourceIP) and isnotempty(DestinationIP)
| where ipv4_is_match(SourceIP, PrivateIPRanges) // Source is Internal
| join kind=inner (TI_Indicators) on $left.DestinationIP == $right.NetworkIP // Destination is Threat Intel IP
| project TimeGenerated, DeviceVendor, DeviceProduct, SourceIP, DestinationIP, DestinationPort, DeviceAction, Reason=set_Description
| summarize Count=count() by SourceIP, DestinationIP, DestinationPort, DeviceAction, Reason=tostring(Reason)
| order by Count desc

Explanation: It first defines internal IPs and gets a list of known bad IPs from ThreatIntelligenceIndicator. It then filters CommonSecurityLog for deny actions from internal IPs going to these known bad external IPs. Parsing or extracting IPs might be needed depending on your specific firewall log format within CommonSecurityLog.

Query 2: Endpoint Network Connections to Uncommon External Ports (DeviceNetworkEvents)#

This query leverages DeviceNetworkEvents from Microsoft Defender for Endpoint to find successful network connections from managed devices to external IP addresses on ports that are not typically used for common web or mail traffic. This can indicate C2 channels or connections to unusual services.

Code snippet

// Query 2: Endpoint Connections to Uncommon External Ports (MDE)
let PrivateIPRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]); // Define your internal ranges
let lookback = 1d;
let CommonPorts = dynamic([80, 443, 53, 22, 21, 25, 110, 143, 993, 995, 3389, 135, 137, 138, 139, 445]); // Add/remove common ports in your env
DeviceNetworkEvents
| where TimeGenerated > ago(lookback)
| where ActionType == "NetworkConnectionSuccess" // Focus on successful connections
| where isnotempty(RemoteIP) // Ensure there's a remote IP
| where ipv4_is_match(LocalIP, PrivateIPRanges) // Local device is internal
| where ipv4_is_match(RemoteIP, PrivateIPRanges) == false // Remote IP is external
| where isnotempty(RemotePort) and RemotePort !in (CommonPorts) // Remote Port is not common
| project TimeGenerated, DeviceName, LocalIP, RemoteIP, RemoteUrl, RemotePort, InitiatingProcessFileName, InitiatingProcessCommandLine
| summarize Connections=count(), Processes=dcount(InitiatingProcessFileName), RemotePorts=make_set(RemotePort, 5) by DeviceName, LocalIP, RemoteIP, RemoteUrl // Summarize to reduce noise
| order by Connections desc

Explanation: Filters DeviceNetworkEvents for successful connections originating from internal IPs (LocalIP) to external IPs (RemoteIP) on ports not in the CommonPorts list. Summarizing helps group similar connections.

Query 3: Large Outbound Data Transfers#

Detecting unusually large data transfers leaving your network can be an indicator of data exfiltration. This query sums outbound traffic from internal sources to external destinations, highlighting the largest transfers. It often relies on firewall logs (CommonSecurityLog) or Azure network flow logs (VMConnection).

Code snippet

// Query 3: Large Outbound Data Transfers (Example using CommonSecurityLog)
let PrivateIPRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]); // Define your internal ranges
let lookback = 1d;
let DataThresholdBytes = 100 * 1024 * 1024; // Define threshold (e.g., 100 MB) - Adjust significantly based on baseline!
CommonSecurityLog
| where TimeGenerated > ago(lookback)
// Ensure byte fields exist. Names might be SentBytes, BytesSent, DestinationBytes, etc. Check your schema!
| where isnotempty(SentBytes) and isnotempty(ReceivedBytes) // Assuming SentBytes represents outbound from SourceIP perspective
| where DeviceVendor has_any ("PaloAltoNetworks", "Fortinet", "Cisco", "CheckPoint", "Microsoft") // Adjust firewall vendors
| where isnotempty(SourceIP) and isnotempty(DestinationIP)
| where ipv4_is_match(SourceIP, PrivateIPRanges) // Internal source
| where ipv4_is_match(DestinationIP, PrivateIPRanges) == false // External destination
| extend SentBytesNum = todouble(SentBytes) // Convert to numeric, use todouble or tolong
| summarize TotalSentBytes = sum(SentBytesNum) by SourceIP, DestinationIP
| where TotalSentBytes > DataThresholdBytes
| project SourceIP, DestinationIP, TotalSentBytes_MB = round(TotalSentBytes / (1024*1024), 2)
| order by TotalSentBytes_MB desc

Explanation: Filters logs (here, CommonSecurityLog) for traffic from internal IPs to external IPs. It sums the bytes sent (SentBytes - field name may vary!) for each source-destination pair and flags pairs exceeding a defined threshold.

Query 4: DNS Queries for Known Malicious Domains#

Malware often needs to resolve C2 server domains. This query checks DNS requests (DnsEvents) originating from internal clients for domains listed in the Threat Intelligence feeds.

Code snippet

// Query 4: DNS Queries for Threat Intel Domains
let PrivateIPRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]); // Define your internal ranges
let lookback = 1d;
// Get Threat Intel Domains
let TI_Indicators = ThreatIntelligenceIndicator
    | where TimeGenerated > ago(lookback)
    | where isnotempty(DomainName)
    | summarize make_set(Description) by DomainName;
// Query DNS Logs
DnsEvents
| where TimeGenerated > ago(lookback)
| where isnotempty(ClientIP)
| where ipv4_is_match(ClientIP, PrivateIPRanges) // Query came from internal client
| where QueryType in ("A", "AAAA", "CNAME") // Focus on common record types for resolution
| where isnotempty(Name) // Name is the domain being queried
| join kind=inner (TI_Indicators) on $left.Name == $right.DomainName
| project TimeGenerated, ClientIP, DeviceName, QueriedDomain = Name, QueryType, ThreatDescription = set_Description
| summarize Count=count() by ClientIP, DeviceName, QueriedDomain, QueryType, ThreatDescription=tostring(ThreatDescription)
| order by Count desc

Explanation: This joins DNS query events (DnsEvents) from internal clients with known malicious domains from ThreatIntelligenceIndicator, highlighting systems attempting to resolve potentially harmful domain names.

Query 5: Network Connections to Newly Observed External IPs#

Connections to IP addresses that have never or rarely been seen communicating with your network before can be suspicious. This query finds external IPs communicated with recently that haven’t been seen in a longer baseline period.

Code snippet

// Query 5: Network Connections to Newly Observed External IPs
let PrivateIPRanges = dynamic(["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"]); // Define your internal ranges
let observation_period = 1d; // Period to check for new connections
let baseline_period = 14d; // Longer period to establish baseline
// Get all external IPs communicated with during the baseline period
let baseline_external_ips = union DeviceNetworkEvents, CommonSecurityLog, VMConnection // Add other relevant tables
    | where TimeGenerated between (ago(baseline_period) .. ago(observation_period))
    | extend IP = case(
        ipv4_is_match(coalesce(SourceIP, LocalIP, SourceIp), PrivateIPRanges) == false, coalesce(SourceIP, LocalIP, SourceIp), // Check if Source is external
        ipv4_is_match(coalesce(DestinationIP, RemoteIP, DestinationIp), PrivateIPRanges) == false, coalesce(DestinationIP, RemoteIP, DestinationIp), // Check if Dest is external
        "" // Neither is external (or fields are missing)
      )
    | where isnotempty(IP) and IP !startswith "127." and IP !startswith "::1" and IP !contains ":" // Basic filtering for valid, non-localhost IPv4
    | distinct IP;
// Find recent connections involving external IPs NOT seen in the baseline
union DeviceNetworkEvents, CommonSecurityLog, VMConnection // Use the same tables
| where TimeGenerated > ago(observation_period)
| extend Source_IP = coalesce(SourceIP, LocalIP, SourceIp)
| extend Destination_IP = coalesce(DestinationIP, RemoteIP, DestinationIp)
| extend External_IP = case(
    ipv4_is_match(Source_IP, PrivateIPRanges) == false, Source_IP,
    ipv4_is_match(Destination_IP, PrivateIPRanges) == false, Destination_IP,
    ""
  )
| where isnotempty(External_IP) and External_IP !startswith "127." and External_IP !startswith "::1" and External_IP !contains ":" // Basic filtering
| where External_IP !in (baseline_external_ips)
| where ipv4_is_match(Source_IP, PrivateIPRanges) != ipv4_is_match(Destination_IP, PrivateIPRanges) // Ensure one is internal, one is external
| project TimeGenerated, Source_IP, Destination_IP, External_IP, TableName = $table // Identify source table
| summarize FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated), ConnectionCount=count(), InternalIPs=make_set(iif(ipv4_is_match(Source_IP, PrivateIPRanges), Source_IP, Destination_IP)), Tables=make_set(TableName) by External_IP
| order by FirstSeen asc

Explanation: It first finds all unique external IPs seen in a longer baseline period (e.g., 14 days). Then, it looks at traffic in the recent observation period (e.g., last 1 day) and identifies connections involving an external IP that was not present in the baseline set. coalesce is used to handle different IP field names across tables (DeviceNetworkEvents, CommonSecurityLog, VMConnection).

Customization is Key#

These queries are templates. You must adapt them to your environment:

Adjust IP Ranges: Ensure PrivateIPRanges accurately reflects your internal network space.
Verify Field Names: Double-check field names for IPs, ports, bytes, actions, etc., in your specific logs. Use the Sentinel UI schema browser if unsure.
Tune Thresholds: Modify values like DataThresholdBytes based on your network’s normal behavior.
Refine Filters: Add or remove ports from CommonPorts, adjust DeviceVendor names, or filter by specific internal subnets if needed.
Combine and Correlate: Combine network findings with other data (e.g., process execution events (SecurityEvent, DeviceProcessEvents), sign-in logs (SigninLogs)) for richer context.

Conclusion#

KQL provides a flexible and powerful way to query network logs within Microsoft Sentinel. By using targeted queries like the examples above, security analysts can effectively hunt for signs of compromise, C2 communication, data exfiltration, and other malicious network activities, significantly improving their organization’s security posture. Remember to continually refine your queries based on your environment and emerging threats.