While exploring the DeviceNetworkEvents table in Defender XDR, I wanted to dig deeper into the different actions behind various HTTP status codes. I started with a few queries to identify cases where specific status codes were appearing repeatedly, and I uncovered some interesting insights, such as:
– Users consistently accessing non-business-related sites.
– Users frequently visiting websites hosted in high-risk countries.
– Continuous errors while attempting to connect to multiple destinations.
– Malicious sites not blocked by default where luckily the connection failed sometimes but unfortunately not always.
Based on these findings, I decided to create a table with extended information about the potential threats associated with each status code. This allows me to quickly work with other KQL queries to identify potential threats. Consider this KQL query as a «Pivot Table» for multiple queries that can be triggered based on your criteria!
💡 Tip : I use to work with ‘join’ operator every time that I add a secondary table into my KQL queries. However, is recommended to use ‘lookup’ instead of ‘join’ when right side is small and left side is large.
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 = parse_json(AdditionalFields).status_code
| extend SiteIPCountry = geo_info_from_ip_address(RemoteIP).country
| extend Site = parse_json(AdditionalFields).referrer
| extend status_code = parse_json(AdditionalFields).status_code
| extend status_code = tostring(status_code)
| extend Site = tostring(Site)
| extend SiteIPCountry = tostring(SiteIPCountry)
| where isnotempty(SiteIPCountry)
| where isnotempty(status_code)
| where isnotempty(Site)
| lookup kind=inner (status_codes) on $left.status_code == $right.statuscode
| summarize make_list(status_code),make_list(DeviceName),Count = count() by RemoteIP, SiteIPCountry, Site,Type_code, Description, Possible_Threat
| order by Count