Devices With External RDP Connections

Query

DeviceEvents
| where ActionType contains "RemoteDesktopConnection"
| extend location = geo_info_from_ip_address(LocalIP)
| where location contains "Country"
| project Timestamp, DeviceName, ActionType, LocalIP, LocalPort, location,ReportId, DeviceId

About this query

Devices with external RDP connections

Description: This query identifies devices into DeviceEvents table that are initiating RDP connections and provides the location of the remote IP addresses. DeviceEvents table has a column called 'LocalIP' which can be confusing but it also includes RemoteIPs. I excluded the entries without info about the location of the IP (which means are potentially Local IPs). As optional, you can add a line to exclude “whitelisted” location such as :' | where location !contain "Spain" '

Explanation

Summary:

This query identifies devices that are initiating Remote Desktop Protocol (RDP) connections by looking into the DeviceEvents table. It also provides the geographical location of the remote IP addresses involved in these connections. The query excludes entries that don't have location information, which likely means they are local IPs. Optionally, you can exclude connections from specific locations, such as Spain.

Query Breakdown:

Filter for RDP Connections: The query looks for events where the ActionType contains "RemoteDesktopConnection".
Get Location Info: It extends the data to include the geographical location of the IP addresses using the geo_info_from_ip_address function on the LocalIP column.
Exclude Local IPs: It filters out entries that don't have location information, assuming these are local IPs.
Select Relevant Columns: It projects (selects) specific columns to include in the output: Timestamp, DeviceName, ActionType, LocalIP, LocalPort, location, ReportId, and DeviceId.

Optional Exclusion:

To exclude connections from specific locations, such as Spain, you can add the following line to the query:

| where location !contains "Spain"

Complete Query with Optional Exclusion:

DeviceEvents
| where ActionType contains "RemoteDesktopConnection"
| extend location = geo_info_from_ip_address(LocalIP)
| where location contains "Country"
| where location !contains "Spain"  // Optional line to exclude specific locations
| project Timestamp, DeviceName, ActionType, LocalIP, LocalPort, location, ReportId, DeviceId

KQL Search