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
  • 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?
Comment
  • 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?
Children
No Data
Unfiltered HTML