REVIEWED by Sophos
This may not work as it is trying to do a fair bit of number crunching and if the device has had a large number of network connections we will run into the watchdog process. As it stands this may take a few minutes to complete, during which time the endpoint will have a CPU spike of about 20% but mileage will vary and a memory consumption spike of under 250MB for the Sophos_OSQuery Service. I will have our development team take a look at how to optimize this but thought it would be valuable to share the query so folks can experiment.
The objective is to identify all process network connections to a IP-Address/Port then determine the number of connections that have happened over the specified time period and what the interval is between the connections.
A call-home/beacon process is a bit of software that regularly checks an external location, for malware this is to establish persistance and allow the adversary to issue commands when they want, but more than malware performs regular checkins with some internet location, so we have to include an FP suppression. This can be different for each company so you would need to edit the FP suppression section with the pathname of the allowed process, or the destination IP-Address that beacons are expected to go to.
To facilitate an investigation we will include the SophosPID.
The query takes a variable 'Hours to check'
-- GENERIC DETECTION FOR RECURRING BEACON ON A FIXED INTERVAL
WITH MIN_MAX_AVG_ConnectionCount AS (
WITH Connection_history_with_interval_data AS (
WITH Connection_History AS (/* Map each network connection to destination and port as a tuple and generate a counted entry for each with a time stamp of when it happened */
SELECT ROW_NUMBER () OVER (PARTITION BY spa.destination, spa.destinationport) RowNum, spa.destination || '::' || spa.destinationport Tuple, spa.time, spa.sophosPID, spa.destination, spa.destinationport
FROM sophos_process_activity spa
WHERE spa.subject = 'Network' AND spa.time >= strftime('%s','now','-$$Hours to check$$ hours') AND spa.destination NOT IN ('127.0.0.1','::1') /* Ignore loopback */ )/* Calculate the Interval between connections, skip first entery for each tuple */
SELECT CH.rownum, CH.Tuple, CH.time - (SELECT CH2.time FROM Connection_history CH2 WHERE CH2.Tuple = CH.Tuple and CH2.rownum = CH.rownum-1) Interval, CH.sophosPID, CH.destination, CH.destinationport
FROM Connection_History CH
WHERE CH.rownum > 1 )/* Now calculate the MIN, MAX,AVG, and SUM for the intervals of each tuple */
SELECT COUNT(rownum) + 1 Number_of_Connections, Tuple, MAX(Interval) Max_Interval, MIN(Interval) Min_Interval, AVG(Interval) Average_Interval, SUM(Interval) Sum_Interval, sophosPID, destination, destinationport
FROM Connection_history_with_interval_data
GROUP by Tuple )/* With calculations complete identify the suspect BEACONING tuples and identify 1 process that was performing the beacon (Note there could be more than 1 process) */
SELECT
(SELECT pathname FROM sophos_process_journal spj WHERE spj.time = replace(MinMax.SophosPID, rtrim(MinMax.SophosPID, replace(MinMax.SophosPID, ':', '')), '') / 10000000 - 11644473600 AND spj.sophospid = MinMax.sophospid LIMIT 1) PathName,
Number_of_Connections, Tuple, Min_Interval, Max_Interval, Average_Interval, Sum_Interval, CAST(SophosPID AS TEXT) SophosPID, destination, destinationport
FROM MIN_MAX_AVG_ConnectionCount MinMax
WHERE LENGTH(pathname) > 0
AND Max_Interval - Min_interval < 10 AND Number_of_Connections > 2
/* Manage an FP Suppression list for processes and destination addresses identified as benign (These are most often updaters) */
AND pathname NOT IN ('C:\Program Files\Sophos\Sophos Network Threat Protection\SophosNtpService.exe',
'C:\Program Files (x86)\Sophos\Sophos Anti-Virus\SavService.exe')
AND destination NOT IN ('')
