These 2 queries were developed to assist with running checks on RDP or Terminal servers run on Windows Server. They typically have port 3389 open and are vulnerable to brute force attacks. This is the most popular and easiest manner that hackers gain entry to networks then deploy Ryuk and Emotet, or some ransomware/malware combo.
Query 1 | RDP Detailed Query | Query used to get all RDP and other login types and usernames from three different event logs.
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;
Query 2 | Open sockets and geo ip information via ipstack.com(API) | Gets a list of all open sockets and the geo ip information of who they are talking to. This query requires you to create a free account at ipstack.com and plug in the api key into the query. It is highlighted in blue.
SELECT
spp.sophosPID,
p.path,
pos.local_port,
pos.remote_address,
pos.remote_port,
json_extract(curl.result,'$.continent_name') continent,
json_extract(curl.result,'$.country_name') country,
json_extract(curl.result,'$.region_name') region,
json_extract(curl.result,'$.city') city,
spp.localRep,
spp.globalRep,
spp.mlScore,
spp.puaScore,
spp.sha256
FROM processes p
LEFT JOIN sophos_process_properties spp
ON spp.pid = p.pid
LEFT JOIN process_open_sockets pos
ON pos.pid = p.pid
LEFT JOIN curl
ON url = 'http:' || '/' || '/' || 'api.ipstack.com/' || pos.remote_address || '?access_key=c2924e4b557b12c8eaf2573525592a82'
WHERE remote_address NOT IN ('0.0.0.0', '::', '::1', '0', '127.0.0.1') /*ignore localhost loopback addresses and placeholder addresses*/;
