Under Review

Generic Network activity search (Windows)

This query provides a generic search for IP address and port information

Descriptive name Variable Type Notes
Begin Search on date $$Begin Search on date$$ DATE Provide a start date for the search
Hours to Search $$Hours to Search$$ STRING Specify the number of hours to search for
IP_Address $$IP_Address$$ IP_Address Specify the IP Address to search for, use % as a wildcard
Will search both source and destination data for a match
Port $$Port$$ STRING Specify the port to search for use % as a wildcard
Will search both source and destination data for a match
Process Name $$Process Name$$ STRING Specify a process name, use % as a wildcard
User Name $$User Name$$ STRING Specify a user name, use % as a wildcard

-- IPAddress Port Activity

-- VARIABLE:   $$Begin Search on date$$       DATE
-- VARIABLE:   $$Hours to Search$$            STRING
-- VARIABLE:   $$IP_Address$$                 STRING
-- VARIABLE:   $$Port$$                       STRING
-- VARIABLE:   $$Process Name$$               STRING
-- VARIABLE:   $$User Name$$                  STRING

-- In order to avoid the watchdog on the device, we will query the journals in in 1 hour chunks (3600 seconds)
WITH RECURSIVE
	Time_Interval(x) AS (
		VALUES ( CAST($$Begin Search on date$$ AS INT) )
		UNION ALL
		SELECT x+3600 FROM Time_Interval WHERE x <= CAST($$Begin Search on date$$ AS INT) + CAST( $$Hours to Search$$ * 3600 AS INT)
	)
-- Collect a list of processes that refrence the desired IP and PORT information
SELECT
   CAST((SELECT processname FROM sophos_process_journal spj WHERE spj.sophosPID = spa.sophosPid LIMIT 1) AS TEXT) processname,
   CAST((SELECT u.username FROM sophos_process_journal spj JOIN users u ON u.uuid = spj.sid WHERE spj.sophosPID = spa.sophosPID LIMIT 1) AS TEXT) Username,
   CAST(REPLACE(DATETIME(time,'unixepoch'),' ','T') AS TEXT) time, 
   source, 
   sourceport, 
   destination, 
   destinationport, 
   sophosPid, 
   subject, 
   originalDestination, 
   originalDestinationPort
FROM Time_Interval t 
  LEFT JOIN Sophos_process_activity spa ON time > t.x AND time < t.x+3600 AND subject IN ('Http','Network','Ip')
WHERE 
   (source LIKE CAST('$$IP_Address$$' AS TEXT) OR destination LIKE CAST('$$IP_Address$$' AS TEXT) OR originalDestination LIKE CAST('$$IP_Address$$' AS TEXT) ) AND 
   (sourcePort LIKE CAST('$$Port$$' AS TEXT) OR destinationPort LIKE CAST('$$Port$$' AS TEXT) OR originalDestinationPort LIKE CAST('$$Port$$' AS TEXT) ) AND
   LOWER(pathname) LIKE LOWER('%$$Process Name$$%') AND
   LOWER(username) LIKE LOWER('%$$User Name$$%')

SAMPLE OUTPUT

epName
processname
Username
time
source
sourcePort destination destinationport sophosPID
subject
originalDestination originalDestinationPort
svchost.exe
LOCAL SERVICE
2021-03-15T12:38:41
172.31.22.71
123
169.254.169.123
123
2472:132583075379538154
Ip
svchost.exe
NETWORK SERVICE
2021-03-15T12:44:52
172.31.22.71
55372
172.31.0.2
53
1336:132583075335022983
Ip
svchost.exe
NETWORK SERVICE
2021-03-15T12:44:52
172.31.22.71
53041
172.31.0.2
53
1336:132583075335022983
Ip
svchost.exe
SYSTEM
2021-03-15T12:45:38
::1
60977
::1
5985
1264:132583075333344510
Ip