REVIEWED by Sophos
If you are in a situation where you installed the CIXA-EDR product after a breach has already happened or is underway you will not have any of the sophos journals to hunt through for how the breach happened. Without the recorded history of process, memory, network, registry and file system activity you will need to use other records that are on the device. A great source of recorded information is the windows event logs. Sophos creates a copy of this information in the Sophos_Windows_Events table and you can search it for information. It will contain things like security events, RDP authentication logs, application activity logs, records of services being created and scheduled tasks. University courses, books and commercial products have been built around performing forensics with just the windows event logs.
I wanted to provide a quick query that allows the admin to search one or more event log sources for information across any number of windows devices. Here is what I came up with.
The query has a number of variables you will have to define:
-- QUERY CODE --
/*********************************************************************************************\
| This bit of code here will divide the number of days selected into smaller chunks. We are |
| doing this so that when we search the windows events we keep the volume of data small for |
| each search window. This allows us to stay under the watchdog limit of 250MB of run time |
| memory for osquery. We do pay for that with a slower more CPU intensive query. The limit on |
| CPU consumption is 30% but typically queries are terminated for memory consumption first |
\*********************************************************************************************/
WITH RECURSIVE
for(x) AS (
VALUES ( ( SELECT CAST (strftime ('%s', 'now','-$$Search - Days to look back$$ days') AS INT) ) )
UNION ALL
SELECT x+$$Search - Window Size in Minutes$$*60 FROM for WHERE x < (SELECT CAST (strftime ('%s', 'now') AS INT))
)
/*********************************************************************************************\
| Now that we have multiple time windows defined for our search we will searh through ALL |
| event logs. we have to specify source as % (wildcard) so that we search all of them. If we |
| did not specify source the table will default to just "System", "Application", "Setup" and |
| "Security". |
\*********************************************************************************************/
SELECT time, datetime, source, eventid, data, provider_name, provider_guid, task, task_message, level, keywords, executing_pid, executing_tid, computer, user_id, activity_id, related_activity_id, opcode, qualifiers, version
FROM for
LEFT JOIN sophos_windows_events ON time >= for.x and time <= for.x+$$Search - Window Size in Minutes$$*60
WHERE source LIKE '$$Source$$' AND
data LIKE '%$$Data to search for$$%' AND
eventid LIKE '$$EventID$$';
---------------------------------------------------------------------------------
Sample Output when looking for ALL events across ALL tables for the last 5 days. Note you can still run into the limit of 10MB per endpoint for returned data, or the 1MB per row limit, or the 100K total rows per query across all endpoints.
