Browse Live Discover and Response Queries by Category
Approved

NOTE: Please review the comments section in addition to this post

Live Discover Query - RDP history

 REVIEWED by Sophos 

As RDP is always a hot topic in the world of security, it might be helpful to gain a report of perhaps who is connecting to where.

The default RDP client, mstsc.exe maintains a history of the computers connected to under the following key to pre-populate the drop-down list:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Terminal Server Client\Default\

They are stored as individual string values, MRU0, MRU1, MRU2, etc..  Therefore the following query could be used:

select data,path from registry  where key like 'HKEY_USERS\%\SOFTWARE\Microsoft\Terminal Server Client\Default' and data <> '';

May be adapted for a specific address of a server:

select data as 'Remote RDP Address', path from registry where key like 'HKEY_USERS\%\SOFTWARE\Microsoft\Terminal Server Client\Default' and data <> '' and data ='wibble.wobble.com';

Maybe useful but of course there is nothing stopping the keys being removed.

Jak

jak
Parents
  • You can optimize the join for the Sophos_process_journal.  The information in the Registry Journal identifies the SophosPID for the process that made the registry change and the SOphosPID is the PID and the windows_filesystem time of the PID Start. What we need to do is extract that windows filetime from the SophosPID then convert it to a UNIXEPOCH for our ON clause. First we will trim off the PID: information from the Sophos PID with the use of some creative string functions We then use some math to convert the filetime to UNIXEPOCH With a valid unixepoch for the process start time we can now join the Sophos_process_journal with an ON time = 'the unix epoc' that will greatly reduce the volume of data being searched for in the join and should give you a nice performance bump. Tell me if this is doing what you intended. So a query like the one below should be faster. SELECT    srj.time,    srj.sophospid as "Sophos PID",    spj.sid,    srj.keyname "Key where deletion took place",    srj.valuename as "Value deleted",    spj.cmdline as "Program which deleted it",    u.username as "User who ran it" FROM sophos_registry_journal SRJ    LEFT JOIN sophos_process_journal SPJ ON spj.sophosPID = srj.sophosPID       /* Get the unixepoch time from the sophosPID in the Registry Table and use that to quickly find the process info */       AND spj.time = replace(srj.SophosPID, rtrim(srj.SophosPID, replace(srj.SophosPID , ':', '')), '')/10000000-11644473600     LEFT JOIN users u ON u.uuid = spj.sid WHERE srj.time > STRFTIME('%s','NOW','-24 HOURS')    AND srj.eventtype = 6    AND srj.keyname LIKE '%\SOFTWARE\Microsoft\Terminal Server Client\Default%';
Comment
  • You can optimize the join for the Sophos_process_journal.  The information in the Registry Journal identifies the SophosPID for the process that made the registry change and the SOphosPID is the PID and the windows_filesystem time of the PID Start. What we need to do is extract that windows filetime from the SophosPID then convert it to a UNIXEPOCH for our ON clause. First we will trim off the PID: information from the Sophos PID with the use of some creative string functions We then use some math to convert the filetime to UNIXEPOCH With a valid unixepoch for the process start time we can now join the Sophos_process_journal with an ON time = 'the unix epoc' that will greatly reduce the volume of data being searched for in the join and should give you a nice performance bump. Tell me if this is doing what you intended. So a query like the one below should be faster. SELECT    srj.time,    srj.sophospid as "Sophos PID",    spj.sid,    srj.keyname "Key where deletion took place",    srj.valuename as "Value deleted",    spj.cmdline as "Program which deleted it",    u.username as "User who ran it" FROM sophos_registry_journal SRJ    LEFT JOIN sophos_process_journal SPJ ON spj.sophosPID = srj.sophosPID       /* Get the unixepoch time from the sophosPID in the Registry Table and use that to quickly find the process info */       AND spj.time = replace(srj.SophosPID, rtrim(srj.SophosPID, replace(srj.SophosPID , ':', '')), '')/10000000-11644473600     LEFT JOIN users u ON u.uuid = spj.sid WHERE srj.time > STRFTIME('%s','NOW','-24 HOURS')    AND srj.eventtype = 6    AND srj.keyname LIKE '%\SOFTWARE\Microsoft\Terminal Server Client\Default%';
Children
No Data
Unfiltered HTML
Help Us Improve The Community
Unfiltered HTML