What Commands Did Users Type By Host (History Evasion)

Using Sophos Linux Sensor's shell command data it's possible to get a list of all of the shell commands executed by a user, even a user employing history evasion techniques.

Required Tables

sensors
shell_commands
process_events

Input Fields

The username to audit should be placed where the <USERNAME> text is in the query.

Returned Fields

Field	Description
dt	Datetime of the alert
shell_commands.program_filename shell_commands.program_arguments	The shell command program information
sensors.hostname	The sensor hostname the command was run on
process_events.username	The user that ran it

Query

SELECT FROM_UNIXTIME(alerts.unix_nano_timestamp/1e9) as dt,
         shell_commands.program_filename,
         shell_commands.program_arguments,
         sensors.hostname,
         process_events.username
FROM alerts
LEFT JOIN sensors
    ON sensors.sensor_id = alerts.sensor_id
LEFT JOIN 
    (SELECT process_uuid,
         username
    FROM process_events
    GROUP BY  username, process_uuid) AS process_events
    ON alerts.process_uuid=process_events.process_uuid LEFT OUTER
JOIN shell_commands
    ON shell_commands.username = process_events.username
WHERE policy_type = 'InteractiveShell'
        AND process_events.username = '<USERNAME>'
ORDER BY  alerts.unix_nano_timestamp, hostname, username DESC