Remote Desktop Protocol: Executing the External RDP Query

The function of the RDP Logins from External IPs.sql query is fairly self-explanatory, based on the name. In this post, we’ll use it to look for successful RDP connections that have taken place from external IP addresses – that is, anything that’s non-RFC 1918. For the sake of this demonstration, we’ll do the work of building and executing the query itself through our own Sophos Central service, but the basics hold true no matter the investigation tool. As an alternative, the “Executing the External RDP Query” video linked below shows the relevant steps, rather than describing them as we do here. 

Building and executing the query 

The first step is to create the query, which in Sophos Central you’ll do in 

Threat Analysis Center > Live Discover > Designer Mode 

by clicking the Create new query button, as shown in Figure 1. 

Figure 1: Navigating to the query-creation button 

Clicking the button leads to a screen with a SQL box, into which you’ll paste the following query (also available on our Github): 

SELECT 

strftime('%Y-%m-%dT%H:%M:%SZ',datetime) AS date_time, 

eventid, 

CASE eventid 

   WHEN 21 THEN eventid || ' - Session logon succeeded' 

   WHEN 22 THEN eventid || ' - Shell start notification received' 

   WHEN 25 THEN eventid || ' - Session reconnection successful' 

   ELSE NULL 

END AS description, 

JSON_EXTRACT(data, '$.UserData.User') AS username, 

SUBSTR(JSON_EXTRACT(data, '$.UserData.User'), 1, INSTR(JSON_EXTRACT(data, '$.UserData.User'), '\') - 1) AS domain, 

JSON_EXTRACT(data, '$.UserData.Address') AS source_IP, 

JSON_EXTRACT(data, '$.UserData.SessionID') AS session_ID, 

CASE 

    WHEN JSON_EXTRACT(data, '$.UserData.Address') GLOB '*[a-zA-Z]*' THEN 'private_IP' 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Address'), '192.168.') = 1 THEN 'private_IP'   

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Address'), '172.') = 1 AND CAST(SUBSTR(JSON_EXTRACT(data, '$.UserData.Address'), 5, 2) AS INTEGER) BETWEEN 16 AND 31 THEN 'private_IP' 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Address'), '10.') = 1 THEN 'private_IP' 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Address'), '127.') = 1 THEN 'private_IP' 

    WHEN JSON_EXTRACT(data, '$.UserData.Address') = '0.0.0.0' THEN 'private_IP' 

    WHEN JSON_EXTRACT(data, '$.UserData.Address') LIKE '%::%' THEN 'unknown' 

    WHEN JSON_EXTRACT(data, '$.UserData.Address') = '' THEN 'private_IP' 

   ELSE 'external_IP' 

END AS status, 

'TS LocalSession EVTX' AS data_source, 

'Logins.01.4' AS query 

FROM sophos_windows_events 

WHERE source = 'Microsoft-Windows-TerminalServices-LocalSessionManager/Operational' 

    AND eventid IN (21,22,25) 

    AND (status = 'external_IP' OR status = 'unknown') 

  

UNION ALL 

  

SELECT 

strftime('%Y-%m-%dT%H:%M:%SZ',datetime) AS date_time, 

eventid, 

CASE eventid 

   WHEN 1149 THEN eventid || ' - User authentication succeeded' 

   ELSE NULL 

END AS description, 

JSON_EXTRACT(data, '$.UserData.Param1') AS username, 

JSON_EXTRACT(data, '$.UserData.Param2') AS domain, 

JSON_EXTRACT(data, '$.UserData.Param3') AS source_IP, 

NULL AS Session_ID, 

CASE 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Param3'), '192.168.') = 1 THEN 'private_IP' 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Param3'), '172.') = 1 AND CAST(SUBSTR(JSON_EXTRACT(data, '$.UserData.Param3'), 5, 2) AS INTEGER) BETWEEN 16 AND 31 THEN 'private_IP' 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Param3'), '10.') = 1 THEN 'private_IP' 

    WHEN INSTR(JSON_EXTRACT(data, '$.UserData.Param3'), '127.') = 1 THEN 'private_IP' 

    WHEN JSON_EXTRACT(data, '$.UserData.Param3') = '0.0.0.0' THEN 'private_IP' 

    WHEN JSON_EXTRACT(data, '$.UserData.Param3') LIKE '%::%' THEN 'unknown' 

    WHEN JSON_EXTRACT(data, '$.UserData.Param3') = '' THEN 'private_IP' 

    ELSE 'external_IP' 

END AS status, 

'TS RemoteConnection EVTX' AS data_source, 

'Logins.01.4' AS query 

FROM sophos_windows_events 

WHERE source = 'Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational' 

    AND eventid = 1149 

    AND (status = 'external_IP' OR status = 'unknown') 

Once that’s pasted in, you’ll select the machines against which this query should run. The query is Windows-specific; running it against macOS or Linux machines will return no results, so deselecting those (under the Filters –> Operating system option) is a good first step. Beyond that, the needs of each enterprise are unique. However, there’s a strong case to be made to run the query against every Windows machine on your network – even the endpoints, just in case one’s incorrectly exposed to the internet. (Alas, our Incident Response investigators find this far more often than one would expect.)  

Click Update Selected Devices to confirm your selections, and select Run Query at bottom right to execute. (The system will ask you to confirm that you wish to run this untested query; you do.) The query begins to execute; the speed at which results are returned depends on how many devices are queried and on their network connections. When it’s finished, the Status column will alert you to query completion (or, if something’s gone wrong, to query failure). Scroll up; there’s a section called Query results that shows the results. If nothing’s there – congratulations! No RDP logins from external IP addresses were found. If, however, there are results shown… 

Understanding the results 

If your query returns results, the first field to take note of in those results is the endpoint name. In the example shown below (taken from the testbed we set up to make our video), two machines reported back that they have external RDP connections. 

Figure 2: Our testbed had two machines, and both of those machines have been touched by an external RDP angel 

Expanding the results shows the date and time at which the connection occurred, the event ID returned by the query (with a brief description of what that event ID means), the username of the account that logged in, and the source IP address from which they connected. The non-RFC 1918 addresses prove that these connections did not come from the network’s private address space. 

It’s worth noting that, as with any query of this type, more investigation is necessary in order to rule out false positives. However, a “false” positive – a peculiar external connection that really was just an administrator opening RDP on a server temporarily – is still worth understanding. As we noted earlier in this series of articles, attackers are breathtakingly quick to hop onto an open RDP connection. If the administrator was able to connect, the odds are excellent that an attacker had time to find the open port as well. An abundance of caution would suggest isolating the device and examining it further for potential compromise. 

Remote Desktop Protocol: The Series

Part 1: Remote Desktop Protocol: Introduction (post, video)
Part 2: Remote Desktop Protocol: Exposed RDP (is dangerous) (post, video)
Part 3: RDP: Queries for Investigation (post, video)
Part 4: RDP Time Zone Bias (post, video)
Part 5: Executing the External RDP Query ([you are here], video)
Part 6: Executing the 4624_4625 Login Query (post, video)
GitHub query repository: SophosRapidResponse/OSQuery
Transcript repository: sophoslabs/video-transcripts
YouTube playlist: Remote Desktop Protocol: The Series