Given a time we want to list all processes that ran during the boot session.
-- This will take a few steps. First lets narrow down the time range
----------------------
-- DETERMINE THE LOWER AND UPPER TIME LIMITS FOR THE SOPHOS_PID
----------------------
WITH
-- Get the system boot time for the target process This will be the lower bound below which we do not search
System_boot(boot_time) AS (
SELECT MAX(meta_boot_time)
FROM xdr_data
WHERE query_name IN ( 'running_processes_linux_events')
AND LOWER(meta_hostname) LIKE LOWER('%%$$device_name$$%')
AND meta_boot_time <= $$Date_Time$$ -- System boot must have been before process start
),
-- Get the next boot time or 'NOW' as a max upper bound to search
Next_boot_time (max_time) AS (
SELECT CASE WHEN MIN(meta_boot_time) > 0 THEN MIN(meta_boot_time) ELSE CAST(to_unixtime(now()) AS BIGINT) END
FROM xdr_data
WHERE query_name IN ( 'running_processes_linux_events')
AND LOWER(meta_hostname) LIKE LOWER('%%$$device_name$$%')
AND meta_boot_time >= $$Date_Time$$ -- System boot must have been before process start
),
-- Set the lower and upper bound as a table for easy access
Time_Limits(lower_bound, upper_bound) AS (
SELECT boot_time, Next_boot_time.max_time
FROM Next_boot_time LEFT JOIN System_Boot ON CAST(1 AS BOOLEAN)
),
-- NOW BUILD A SINGLE TABLE WITH the Device, Time, Name, CmdLine, SophosPID, ParentSophosPID for each process (We have to flatten the groupings)
All_Data AS (
SELECT
X1.meta_hostname,
X1.time,
X1.name,
rtrim(array_join(array_agg(distinct X1.cmdline||','), chr(10)),',') as cmdline,
S1.Single_PID||':'||CAST(X1.time AS VARCHAR) SophosPID,
-- Address PID REUSE with a sub-select to identify the correct time for the parent if multiple PIDs match the ParentPID --
T1.Single_Parent||':'||CAST(
(SELECT MAX(X2.time)
FROM xdr_data X2 CROSS JOIN UNNEST(SPLIT(X2.pids,',')) AS S2 (Single_PID) CROSS JOIN UNNEST(SPLIT(X2.parents,',')) AS T2 (Single_Parent), Time_Limits TL_2
WHERE X2.query_name IN ( 'running_processes_linux_events')
AND LOWER(X2.meta_hostname) LIKE LOWER('%%$$device_name$$%')
AND S2.Single_PID = T1.Single_Parent
AND X2.time >= TL_2.lower_bound
AND X2.time <= X1.time
) AS VARCHAR) Parent_SophosPID,
X1.path,
X1.sha256
FROM xdr_data X1 CROSS JOIN UNNEST(SPLIT(X1.pids,',')) AS S1 (Single_PID) CROSS JOIN UNNEST(SPLIT(X1.parents,',')) AS T1 (Single_Parent), Time_Limits TL_1
WHERE X1.query_name IN ( 'running_processes_linux_events')
AND LOWER(X1.meta_hostname) LIKE LOWER('%%$$device_name$$%')
AND X1.time >= TL_1.lower_bound
AND X1.time <= TL_1.upper_bound
GROUP BY X1.meta_hostname, X1.time, X1.name, X1.time, X1.path, X1.sha256, X1.pids, S1.Single_PID, X1.parents, T1.Single_Parent
)
SELECT meta_hostname, date_format(from_unixtime(time), '%y-%m-%dt%h:%i:%sz') as date_time, name, cmdline, SophosPID, Parent_SophosPID, path, sha256 FROM ALL_Data ORDER BY time ASC, SophosPID ASC
