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
  • So that above query checks for 24 hours, but say we want to check for 30 days of historic activity.  To do that we need to check more of the sophos_registry_journal and because that can contain a lot of data we need to do it in chunks so that we do not consume so much memory that the watchdog process on the endpoint terminates the query.  I also cleaned up the time to show as human readable instead of UNIXEPOCH format :) While SQLite does not support a LOOP statement we can build one using a recursive query. /* Create a loop to search the sophos journal in 10 hour increments for the last 30 days */ WITH RECURSIVE    for(x) AS (       VALUES ( CAST (strftime ('%s', 'now','-30 days') AS INT) )    UNION ALL       SELECT x+36000 FROM for WHERE x '%s', 'now') AS INT)    ) /* Now that we have cut the 30 days into 10 hour chunks lets use our SELECT statement to look for the evidence */ SELECT DISTINCT    datetime(srj.time,'unixepoch') Date_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 for     /* We want to check the 10hours of sophos_registry_journal data for delete (event type = 6) events that match the keyname */    LEFT JOIN sophos_registry_journal SRJ ON srj.eventtype = 6       AND srj.time > for.x AND srj.time '%\SOFTWARE\Microsoft\Terminal Server Client\Default%'    /* Get the unixepoch time from the sophosPID from the sophos_registry_journal and use that to quickly find the process info */    LEFT JOIN sophos_process_journal SPJ ON spj.sophosPID = srj.sophosPID        AND spj.time = replace(srj.SophosPID, rtrim(srj.SophosPID, replace(srj.SophosPID , ':', '')), '')/10000000-11644473600     /* If we have found a process that made that registry delete we want to now look for the name of the user that owned the offending process */    LEFT JOIN users u ON u.uuid = spj.sid WHERE srj.SophosPID > 0;   Does that detect what you hoped it would? or do we still have more work to do?
  • 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%';
  • It took longer than i'd like to run, but it seems to return the desired result; select    sophos_registry_journal.time,    sophos_registry_journal.sophospid as "Sophos PID",    sophos_process_journal.sid,    sophos_registry_journal.keyname "Key where deletion took place",    sophos_registry_journal.valuename as "Value deleted",    sophos_process_journal.cmdline as "Program which deleted it",    users.username as "User who ran it" from sophos_registry_journal join sophos_process_journal join users where sophos_registry_journal.keyname like '%\SOFTWARE\Microsoft\Terminal Server Client\Default%'    and sophos_registry_journal.eventtype = 6 and sophos_registry_journal.time > STRFTIME('%s','NOW','-24 HOURS')    and sophos_process_journal.time > STRFTIME('%s','NOW','-24 HOURS')    and sophos_process_journal.sophospid = sophos_registry_journal.sophospid and users.uuid = sophos_process_journal.sid
  • Well, if the keys have been removed in the last 90 days we should be able to see the delete of the registry key in the Sophos_registry_journal.  Anyone want to write that query?  If so Just post and I can help out. Thanks
  • I'm curious if others have tried this one out.  Locking down RDP is one of the key steps to protecting against ransomware attacks.  Anyone else have success with this one?
Unfiltered HTML