We continue to make excellent progress to the intended May release of the Data Lake version of the product.
This week I wanted to demonstrate some of the capabilities we have just added around Pivots and the Depth of information available for admins that need to perform a rapid investigation.
We cover the breadth of capabilities by showing a data lake query looking for hundreds of indicators of compromise across all devices. In the next few weeks we are going to be adding the ability to schedule queries to be run automatically for report generation. Starting from here I will pivot do some interesting information available on the device then drill down to the specific actions a process took and then even deeper down to the contents of a file left behind by the adversary. Below is the 8 min video and the the Extended Process Tree I used to get forensic information off of the device.
Link to Video:
Extended Process Tree Query
-- Extended Process Tree for a SophosPID
-- VARIABLE $$SophosPID$$ sophosPID
-- NOTE THE PROCESS OR ANCESTORS MAY STILL BE RUNNING SO HANDLE ENDTIME CORRECTLY by changing endtime for running processes to now + 10 min
WITH RECURSIVE
-- GET A LIST OF ALL ANCESTORS OF A SOPHOS PID
Ancestors(SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end) AS (
-- Define the SEED Row as the information on the SophosPID provided
SELECT sophosPID, 0, ParentSophosPID, processname, pathname, cmdline, sha256, sid, time,
CASE WHEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0) > 0
THEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0)
ELSE strftime('%s','now','+10 minutes') END
FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) = 0
UNION ALL
-- Recursvly identify all decendents
SELECT spj.SophosPID, Level - 1, spj.ParentSophosPID, spj.processname, spj.pathname, spj.cmdline, spj.sha256, spj.sid, spj.time,
CASE WHEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0) > 0
THEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0)
ELSE strftime('%s','now','+10 minutes') END
FROM Ancestors JOIN Sophos_Process_Journal spj ON spj.SophosPID = Ancestors.parent AND CAST(spj.endtime AS INT) = 0
-- Perform a Depth First Search ASC would perform a Breadth First Search
ORDER BY 2 DESC
),
-- Add Row Numbers to the Ancestor List so we order the tree corretly
-- EXCLUDE the line for the specified SophosPID so that when we show the tree we do not have a duplicate row
Orderd_Ancestors AS (SELECT SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end, -1 * ROW_Number() OVER () Row FROM Ancestors WHERE SophosPID NOT IN ('$$SophosPID$$') ),
-- GET A LIST OF ALL DECENDENTS OF A SOPHOS PID
-- NOTE THE PROCESS OR CHILDREN MAY STILL BE RUNNING SO HANDLE ENDTIME CORRECTLY by changing endtime for running processes to now + 10 min
Children(SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end) AS (
-- Define the SEED Row as the information on the SophosPID provided
SELECT sophosPID, 0, ParentSophosPID, processname, pathname, cmdline, sha256, sid, time,
CASE WHEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0) > 0
THEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0)
ELSE strftime('%s','now','+10 minutes') END
FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) = 0
UNION ALL
-- Recursvly identify all decendents
SELECT spj.SophosPID, Level +1, spj.ParentSophosPID, spj.processname, spj.pathname, spj.cmdline, spj.sha256, spj.sid, spj.time,
CASE WHEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0) > 0
THEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0)
ELSE strftime('%s','now','+10 minutes') END
FROM Children JOIN Sophos_Process_Journal spj ON spj.ParentSophosPID = Children.SophosPID AND CAST(spj.endtime AS INT) = 0 AND spj.time > Children.start and spj.time < Children.end
-- Perform a Depth First Search ASC would perform a Breadth First Search
ORDER BY 2 DESC
),
-- Add Row Numbers to the Decendent List so we order the tree corretly
Orderd_Descendants AS (SELECT SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end, ROW_Number() OVER () Row FROM Children),
-- Now collect the activity for all descendents and the selected sophosPID using a UNION to list the decendent then the file activity it had
File_Activity AS (
-- FOR ANCESTORS WE WILL ONLY SHOW THE PROCESS TREE INFO (No activity will be collected)
SELECT
REPLACE(DATETIME(A.start,'unixepoch'), ' ','T') Date_Time,
CASE A.SophosPID
WHEN '$$SophosPID$$' THEN A.ProcessName
ELSE substr('< < < < < < < < < < < < < < < < < < < < ', 1, A.Level * -2) || A.processName
END Process_Tree,
'-----------' Subject,
'-----------' Action,
'-----------' Object,
CAST(A.cmdline AS TEXT) Cmd_Line,
A.SophosPID SophosPID,
A.pathname Process_Pathname,
A.sha256 Process_SHA256,
A.SID Process_SID,
A.Level Level,
A.Row Row,
0 Sub_Row,
a.start time
FROM Orderd_Ancestors A
UNION ALL
-- SHOW THE PROCESS TREE INFO FOR DESCENDENTS
SELECT
REPLACE(DATETIME(D.start,'unixepoch'), ' ','T') Date_Time,
CASE D.SophosPID
WHEN '$$SophosPID$$' THEN D.ProcessName
ELSE substr('> > > > > > > > > > > > > > > > > > > > > > ', 1, D.Level * 2) || D.processName
END Process_Tree,
'-----------' Subject,
'-----------' Action,
'-----------' Object,
CAST(D.cmdline AS TEXT) Cmd_Line,
D.SophosPID SophosPID,
D.pathname Process_Pathname,
D.sha256 Process_SHA256,
D.SID Process_SID,
D.Level Level,
D.Row Row,
0 Sub_Row,
D.start time
FROM Orderd_Descendants D
UNION ALL
-- ADD THE PROCESS ACTIVITY FOR EACH DESCENDENT
SELECT
REPLACE(DATETIME(MIN(spa.time),'unixepoch'),' ','T') Date_Time,
CASE D.SophosPID
WHEN '$$SophosPID$$' THEN '( '||D.processname||' ) ACTIVITY'
ELSE substr('~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~', 1, D.Level * 2) || '( '||D.processname||' ) ACTIVITY'
END Process_Tree,
spa.subject Subject,
CAST(GROUP_CONCAT(DISTINCT spa.action)||' ('||COUNT(spa.action)||')' AS TEXT) Action,
spa.object,
CAST(D.cmdline AS TEXT) Cmd_Line,
D.SophosPID SophosPID,
D.pathname Process_Pathname,
D.sha256 Process_SHA256,
D.SID Process_SID,
'' Level,
D.Row Row,
1 Sub_Row,
spa.time time
-- NOTE The details for each process does not include 'FileDataReads', 'FileOtherReads', 'FileBinaryReads', 'Image', 'Thread'
FROM Orderd_Descendants D LEFT JOIN Sophos_Process_Activity spa ON spa.subject IN ('DirectoryChanges','Dns','FileBinaryChanges','FileDataChanges','FileOtherChanges','Http','Ip','Network','Url','Registry')
AND spa.SophosPID = D.SophosPID
AND spa.time > D.start-1
AND spa.time < D.end+1
WHERE spa.subject > ''
GROUP BY spa.subject, spa.action, spa.object, spa.SophosPID, D.processname, D.SophosPID, D.pathname, D.sha256, D.SID
)
-- Now that we have all activity for each descendent, we need to provide the pretty list showing the Process Tree and File activity for each process in the tree
SELECT Date_Time, Process_Tree, Subject, Action, Object, Cmd_Line, SophosPID, Process_Pathname, Process_SHA256, Process_SID, row, sub_row, time,ROW_NUMBER() OVER( ORDER BY Row, Sub_Row, Time) SORT_ORDER_FOR_EXCEL
FROM File_Activity
ORDER By SORT_ORDER_FOR_EXCEL
ASCII File viewer
-- Perform an ASCII DUMP for a file -- VARIABLE $$File Path$$ STRING SELECT CAST(line AS TEXT) '$$File Path$$' FROM grep WHERE pattern IN (CHAR(0),CHAR(10),CHAR(32)) AND path = '$$File Path$$'
