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