Query Details
Identifying Devices By Vendor Country Based On Inbound Connections
Query
**Identifying Devices by Vendor&Country based on Inbound Connections**
This KQL Query focuses on summarizing the number of devices attempting to connect to exposed servers, categorized by vendor and it country, by decoding their MAC addresses.
This information can help you to identify if an unusual combination appears (e.g., a Chinese-manufactured device but the connection originates from Russia), detect nation-state attack patterns, as certain adversaries often use infrastructure in specific regions and
cases where attackers often route traffic through VPNs, proxies, or compromised hosts to obfuscate their real location.
```
// Importing the Vendor MAC Address table
let mac_info = externaldata(MAC: string,Vendor:string ,Country:string)[@"https://raw.githubusercontent.com/Sergio-Albea-Git/Threat-Hunting-KQL-Queries/refs/heads/main/Security-Lists/mac_list.csv"] with (format="csv", ignoreFirstRecord=True);
// selecting the Source MAC Address of the remote connections
DeviceNetworkEvents
| extend AdditionalFields = parse_json( AdditionalFields)
| extend direction = AdditionalFields["direction"]
| where direction has "In"
| extend Source_Mac = tostring(AdditionalFields["Source Mac"])
// formatting the First 3 Octets of the MAC Address
| extend MAC_Prefix_format = replace(":", "-", Source_Mac)
| extend MAC_Prefix = substring(MAC_Prefix_format, 0, 8)
// joining the Vendor MAC address info table
| join kind=inner (mac_info) on $left.MAC_Prefix == $right.MAC
// Getting the Country of the RemoteIPs
| extend geo_ip = tostring(geo_info_from_ip_address(RemoteIP).country)
| where isnotempty (geo_ip)
| distinct Source_Mac,Vendor, Device_Component_Country= Country, RemoteIP_Country = geo_ip , RemoteIP , ActionType, DeviceName
```
Explanation
This KQL query is designed to analyze network activity by identifying devices based on their MAC addresses and categorizing them by vendor and country. Here's a simplified breakdown of what the query does:
-
Import Vendor Data: It starts by importing a list of MAC addresses along with their associated vendor and country information from an external CSV file.
-
Filter Inbound Connections: The query focuses on network events where the direction of the connection is inbound, meaning devices are attempting to connect to the network.
-
Extract MAC Prefix: It extracts the first three octets of the source MAC address from these inbound connections, which are used to identify the device vendor.
-
Join with Vendor Info: The extracted MAC prefix is then matched with the imported vendor data to determine the vendor and country of the device.
-
Determine Remote IP Country: For each connection, the query also determines the country associated with the remote IP address.
-
Identify and Distinct: Finally, it compiles a distinct list of devices, showing the source MAC address, vendor, the country of the device component, the country of the remote IP, the remote IP itself, the action type, and the device name.
The purpose of this query is to help identify unusual patterns, such as devices from unexpected countries or regions, which could indicate potential security threats like nation-state attacks or obfuscated traffic routes.
Details
Sergio Albea
Released: February 18, 2025
Tables
Keywords
Operators