Sophos SQL cheat sheet

SELECT pid, name, path, cmdline FROM processes;

SELECT pid, path FROM processes WHERE name = 'powershell.exe';

SELECT pid, path FROM processes WHERE name LIKE 'powershell.exe';

SELECT name, cmdline FROM processes WHERE cmdline LIKE '%-enc%';

SELECT pid, name FROM processes WHERE name IN ('cmd.exe', 'powershell.exe');

SELECT p.name, pp.name AS parent
FROM processes p
JOIN processes pp ON p.parent = pp.pid;

SELECT lp.port, lp.address, p.name
FROM listening_ports lp
JOIN processes p ON lp.pid = p.pid;

SELECT username, description, directory FROM users;

SELECT name, path, username FROM startup_items;

SELECT name, action, path FROM scheduled_tasks WHERE enabled = 1;

SELECT name, path, user_account FROM services WHERE start_type = 'AUTO_START';

SELECT path, size, mtime FROM file WHERE path LIKE 'C:\Users\Public\%';

SELECT path, sha256 FROM hash WHERE path = 'C:\Windows\Temp\update.exe';

SELECT name, COUNT(*) AS n FROM processes
GROUP BY name ORDER BY n DESC LIMIT 10;

SELECT regex_match(cmdline, 'https?://[^ ]+', 0) AS url
FROM processes WHERE cmdline LIKE '%http%';

SELECT pid, path FROM processes WHERE name = $$Process name$$;

SELECT meta_hostname, name, cmdline, username
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos';

AND LOWER(name) = 'powershell.exe'

AND LOWER(parent_name) = 'winword.exe'

AND sha256 = '<sha256 here>'

SELECT meta_hostname, COUNT(*) AS runs
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
GROUP BY meta_hostname ORDER BY runs DESC;

SELECT DISTINCT query_name FROM xdr_data;