KQL Search

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:

1. Filter for RDP Connections: The query looks for events where the ActionType contains "RemoteDesktopConnection".
2. 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.
3. Exclude Local IPs: It filters out entries that don't have location information, assuming these are local IPs.
4. 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