Critical Microsoft Security Service Connectivity Failures

Query

DeviceNetworkEvents
| where isnotempty(RemoteUrl)
| extend Domain = case(
    RemoteUrl contains "//", tostring(parse_url(RemoteUrl).Host),
    RemoteUrl
)
| extend Service = case(
    Domain endswith ".wdcp.microsoft.com"
        or Domain endswith ".wdcpalt.microsoft.com"
        or Domain endswith ".wd.microsoft.com", "Cloud Protection (Defender)",
    Domain endswith ".update.microsoft.com"
        or Domain endswith ".delivery.mp.microsoft.com"
        or Domain endswith ".windowsupdate.com"
        or Domain == "ctldl.windowsupdate.com", "Windows Update / Microsoft Update",
    "Other"
)
| where Service != "Other"
| where RemotePort == 443
| where ActionType in ("ConnectionBlocked", "ConnectionFailed", "ConnectionTimeout", "ConnectionReset")
| summarize Failures = count(), LastSeen = max(Timestamp)
    by DeviceName, DeviceId, Service, Domain, ActionType
| order by Failures desc, LastSeen desc

About this query

Explanation

This query is designed to identify devices that are experiencing connectivity issues with critical Microsoft security services, specifically focusing on Defender cloud protection and Windows Update/Microsoft Update services. It checks for network events where devices attempt to connect to these services over HTTPS (port 443) and records any failed attempts. Failures can be due to various reasons such as being blocked, failing to connect, timing out, or having the connection reset.

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

Filters Network Events: It looks at network events that have a RemoteUrl, indicating an attempt to connect to an external service.
Extracts Domain: From the URL, it extracts the domain to identify which service the device is trying to reach.
Classifies Service: It categorizes the connection attempt into either "Cloud Protection (Defender)" or "Windows Update / Microsoft Update" based on the domain.
Focuses on HTTPS Failures: It specifically looks for failed connection attempts over HTTPS (port 443).
Summarizes Results: The query summarizes the data by counting the number of failures for each device and service, and records the last time a failure was observed.
Output Details: The output includes details such as the device name and ID, the service category, the domain, the type of failure, the number of failures, and the last seen timestamp.

This query is useful for identifying and troubleshooting network issues that prevent devices from accessing essential Microsoft security services, which can be critical for maintaining security updates and protection.

KQL Search