Skip to content

Which users logged into which hosts

Using Sophos Linux Sensor's process event data it's possible to get a list of which users logged into which hosts and when. This can help find abnormal usage patterns which may be a sign of a compromised account.

Required Tables

  • process_events
  • sensors
  • alerts

Returned Fields

Field Description
dt The datetime of the alert
sensors.hostname The host name of the sensor
process_events.username The user that owns the processes that triggered the alert

Query

SELECT FROM_UNIXTIME(alerts.unix_nano_timestamp/1e9) as dt, 
    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
WHERE policy_type = 'InteractiveShell'