osquery

osquery#

osquery exposes an operating system as a high-performance relational database. The operator writes SQL-based queries to explore process, file, network, and module state across Windows, Linux, and macOS.

Process and network#

-- Top 10 largest processes by resident memory
select pid, name, uid, resident_size
  from processes
  order by resident_size desc limit 10;

-- Top 10 most active process names
select count(pid) as total, name
  from processes
  group by name order by total desc limit 10;

-- New processes listening on network ports
select distinct process.name, listening.port, listening.address, process.pid
  from processes as process
  join listening_ports as listening
    on process.pid = listening.pid;

-- Suspicious outbound network activity
select s.pid, p.name, local_address, remote_address, family, protocol, local_port, remote_port
  from process_open_sockets s
  join processes p on s.pid = p.pid
  where remote_port not in (80, 443) and family = 2;

-- Running processes whose binary has been deleted
select name, path, pid
  from processes where on_disk = 0;

Indicators on disk#

select * from file where path = '/dev/ptmx0';

select * from apps
  where bundle_identifier = 'com.ht.RCSMac'
     or bundle_identifier like 'com.yourcompany.%'
     or bundle_package_type like 'OSAX';

select * from launchd
  where label = 'com.ht.RCSMac'
     or label like 'com.yourcompany.%'
     or name = 'com.apple.loginStoreagent.plist'
     or name = 'com.apple.mdworker.plist'
     or name = 'com.apple.UIServerLogin.plist';

Kernel modules#

-- Run periodically, diff against older results
select name from kernel_modules;

Windows masquerading#

-- Process named lsass.exe not in expected path
SELECT * FROM processes
  WHERE LOWER(name)='lsass.exe'
    AND LOWER(path)!='c:\\windows\\system32\\lsass.exe'
    AND path!='';

-- Service process tree
SELECT name FROM processes
  WHERE pid=(SELECT parent FROM processes WHERE LOWER(name)='services.exe')
    AND LOWER(name)!='wininit.exe';

-- svchost.exe with the wrong path
SELECT * FROM processes
  WHERE LOWER(name)='svchost.exe'
    AND LOWER(path)!='c:\\windows\\system32\\svchost.exe'
    AND LOWER(path)!='c:\\windows\\syswow64\\svchost.exe'
    AND path!='';

-- Services process tree
SELECT name FROM processes
  WHERE pid=(SELECT parent FROM processes WHERE LOWER(name)='svchost.exe')
    AND LOWER(name)!='services.exe';

-- Accessibility tools hijacked
SELECT * FROM hash
  WHERE (path='c:\\windows\\system32\\osk.exe'
      OR path='c:\\windows\\system32\\sethc.exe'
      OR path='c:\\windows\\system32\\narrator.exe'
      OR path='c:\\windows\\system32\\magnify.exe'
      OR path='c:\\windows\\system32\\displayswitch.exe')
    AND sha256 IN (
      SELECT sha256 FROM hash
        WHERE path='c:\\windows\\system32\\cmd.exe'
           OR path='c:\\windows\\system32\\WindowsPowerShell\\v1.0\\powershell.exe'
           OR path='c:\\windows\\system32\\explorer.exe')
    AND sha256!='e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855';

Timestamp inconsistency#

select path, fn_btime, btime
  from ntfs_file_data
  where device="\\\\.\\PhysicalDrive0"
    and partition=3
    and directory="/Users/<USER>/Desktop/dir"
    and fn_btime != btime;

select filename, path
  from ntfs_file_data
  where device="\\\\.\\PhysicalDrive0"
    and partition=2
    and path="/Users/<USER>/Downloads"
    and (fn_btime > ctime OR btime > ctime);

Directory unused entries#

select parent_path, filename, slack
  from ntfs_indx_data
  WHERE parent_path="/Users/<USER>/Desktop/test_dir"
    and slack!=0;

References#

Contents