Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.

Purpose

Often, Remote Desktop Protocol (RDP) sessions are used in an organization for remote management of servers and/or workstations. Many incidents begin with a misconfigured RDP listening port. Organizations may be monitoring for this already through other management software, but if not, it's a good exercise for proactive or reactive threat hunting with XDR.

The following queries are all available in our Live Discover & Response Forum and GitHub Repo.

Prerequisites

You must have XDR enabled in your environment. 

This is intended for Windows only.

Query Focus #1 - Is RDP Enabled?

The original query was written by AndrewMundell 

SELECT 
datetime(mtime, 'unixepoch', 'localtime') AS modifiedTime,
CASE
   WHEN data = 0 then 'RDP Enabled'
   WHEN data = 1 then 'RDP Disabled'
   ELSE 'Error'
END AS 'RDP state'
FROM registry 
WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\' 
AND name = 'fDenyTSConnections'

Results:

Understanding the Code

In Andrew's query, he is pulling the registry key directly off of the machine to see if RDP is enabled. By using a CASE statement, this allows him to return human readable information on the status of the machine.

WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\' 
AND name = 'fDenyTSConnections'

When fDenyTSConnections is set to "0", it is enabled, and when it is "1", it is disabled. 

Query Focus #2 - Are there any sessions?

The original query was written by Karl_Ackerman 

/*
Set "Days to look back from now" as a variable type string
This will define how many days to look back on
*/

SELECT 
   datetime(time,'unixepoch') 'Date-Time', 
   (strftime('%s','now')-time)/(3600*24) 'Days ago' ,eventid, 'TS Remote' AS Source,
   JSON_EXTRACT(data, '$.UserData.Param1') AS Name,
   JSON_EXTRACT(data, '$.UserData.Param2') AS Source_Machine_Network,
   JSON_EXTRACT(data, '$.UserData.Param3') AS Source_IP
FROM sophos_windows_events
WHERE source = 'Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational' AND 
   eventid = 1149 AND 
   time > strftime('%s', 'now', '-$$Days to look back from now$$ days');

Results: (account names redacted)

Understanding the Code

In Karl's query, he is pulling off information from the Sophos Windows Events journal. Here, any event ID with 1149 is matched and returned if it falls within your required search interval. You can search up to 90 days of event logs on Sophos Live Discover.

FROM sophos_windows_events
WHERE source = 'Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational' AND 
   eventid = 1149 AND 
   time > strftime('%s', 'now', '-$$Days to look back from now$$ days');

Query Focus #3 - Show me details on the sessions

The original query was written by Brian Ritchie 

SELECT datetime, eventid, 'TS Remote' AS Source,
JSON_EXTRACT(data, '$.UserData.Param1') AS Account,
JSON_EXTRACT(data, '$.UserData.Param2') AS Source_Machine_Network,
JSON_EXTRACT(data, '$.UserData.Param3') AS Source_IP,
'-' AS Process_Name,
'-' AS Logon_Type,
'-' AS User_SID,
'-' AS SessionID,
'-' AS Session_ID
FROM sophos_windows_events
WHERE source = 'Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational' and eventid = 1149
UNION ALL
SELECT datetime, eventid, 'Security' AS Source,
JSON_EXTRACT(data, '$.EventData.TargetUserName') AS Account,
JSON_EXTRACT(data, '$.EventData.WorkstationName') AS Source_Machine_Network,
JSON_EXTRACT(data, '$.EventData.IpAddress') AS Source_IP,
JSON_EXTRACT(data, '$.EventData.ProcessName') AS Process_Name,
JSON_EXTRACT(data, '$.EventData.LogonType') AS Logon_Type,
JSON_EXTRACT(data, '$.EventData.TargetUserSid') AS User_SID,
'-' AS SessionID,
'-' AS Session_ID
FROM sophos_windows_events
WHERE source = 'Security' and (eventid = 4624 OR eventid = 4625)
UNION ALL
SELECT datetime, eventid, 'TS Local' AS Source,
JSON_EXTRACT(data, '$.UserData.User') AS Account,
'-' AS Source_Machine_Network,
JSON_EXTRACT(data, '$.UserData.Address') AS Source_IP,
'-' AS Process_Name,
'-' AS Logon_Type,
'-' AS User_SID,
JSON_EXTRACT(data, '$.UserData.Session') AS SessionID,
JSON_EXTRACT(data, '$.UserData.SessionID') AS Session_ID
FROM sophos_windows_events
WHERE source = 'Microsoft-Windows-TerminalServices-LocalSessionManager/Operational' ORDER BY datetime DESC;

Results: (account names redacted)

Understanding the Code

Take a look at Brian's second query on the page - it will interact RDP sessions against an API call against IP Stack. If you enjoy that, look at Kris Wayman's query post on comparing against Abuse IP Database. 

---

Happy querying!

-jk

modified query
[edited by: JeramyKopacko at 3:11 AM (GMT -7) on 24 May 2023]