REVIEWED by Sophos
With Live discover I wanted to explore if I can write a query to detect lateral movement between devices that are under management. Because Live Discover queries are run on individual devices we can not easily build a query that directly detects inter device communications (Note that will be possible once we complete the work on XDR where the data lake will have information from all devices).
To detect if a device has been chatting with another device in the network I realized that I can do this on a per device basis. Given that we know the IP Address of our devices we can write a query to ask all other devices if they have ever had a connection to the one device we are interested in. Say that we know the IP Address of the device we are interested in is 1.2.3.4 we can ask all other devices if they have ever sent or received a connection with 1.2.3.4.
This ends up building a nice query for generic checking if a device has had a connection to an IP-Address and can be used to check if anyone has ever talked to some malware delivery site we learned about from threat intel.
Below is a screen shot of the query and the actual query. In this query the machine we are worried about is Victim1, IP 192.168.1.128. We set that as a variable in the query, then set the start date and time range to be 24 hours.
With that set we issue the query to all the devices we are worried it may have connected to.
Here we see the RDP session that was launched from the machine we are investigating to another managed windows PC in the network.
I exclude some ports from the query to ensure we do not get false positives related to refused connections.
SELECT
datetime(spa.time,'unixepoch') Date_Time,
spa.sophosPid,
replace(spp.pathname, rtrim(spp.pathname, replace(spp.pathname, '\', '')), '') process_name,
spa.subject,
spa.action,
spa.object,
spa.url,
spa.source,
spa.sourcePort,
spa.destination,
spa.destinationPort,
spa.originalDestination,
spa.originalDestinationPort,
spa.protocol,
spp.pathname,
spp.mlscore,
spp.puascore,
spp.globalrep,
spp.localrep,
spp.sha256
FROM Sophos_process_activity spa
JOIN sophos_process_properties spp ON spp.sophosPID = spa.SophosPID
WHERE
subject IN ('Http','Network','Ip') AND
( source LIKE CAST('$$IP Address to look for$$' AS TEXT) OR destination LIKE CAST('$$IP Address to look for$$' AS TEXT) ) AND
time > $$Start Search From$$ AND
time < $$Start Search From$$ + $$Number of Hours of activity to search$$ * 3600 AND
spa.sourceport NOT IN (137,138,139) AND spa.destinationPort NOT IN (137,138,139);
