← All cheat sheets

OSQUERY

Plain-text reference · 7 KB. Read it, search it (Ctrl-F) or print it.

osquery exposes the operating system as a SQL database.
Essential for endpoint visibility, threat hunting, and compliance.

INSTALLATION#

# macOS
brew install osquery

# Ubuntu/Debian
apt install osquery

# Windows
# Download from https://osquery.io/downloads

BASIC USAGE#


            

                        

                
INTERACTIVE MODE#

                
osqueryi                             # Start shell
osqueryi --json                      # JSON output
osqueryi --line                      # Line mode

            

                        

                
ONE-SHOT QUERY#

                
osqueryi "SELECT * FROM users;"
osqueryi --json "SELECT * FROM processes;"

            

                        

                
DAEMON MODE#

                
osqueryd --config_path=/etc/osquery/osquery.conf

            

                        

                
SQL BASICS#

                

            

                        

                
SELECT COLUMNS#

                
SELECT * FROM users;
SELECT username, uid FROM users;
SELECT DISTINCT shell FROM users;

            

                        

                
FILTERING#

                
SELECT * FROM processes WHERE name = 'bash';
SELECT * FROM processes WHERE pid > 1000;
SELECT * FROM processes WHERE name LIKE '%python%';
SELECT * FROM processes WHERE uid IN (0, 1000);

            

                        

                
SORTING/LIMITING#

                
SELECT * FROM processes ORDER BY resident_size DESC;
SELECT * FROM processes ORDER BY pid LIMIT 10;

            

                        

                
JOINS#

                
SELECT p.name, p.pid, u.username
FROM processes p
JOIN users u ON p.uid = u.uid;

            

                        

                
AGGREGATION#

                
SELECT COUNT(*) FROM processes;
SELECT uid, COUNT(*) FROM processes GROUP BY uid;

            

                        

                
SYSTEM INFORMATION#

                

            

                        

                
BASIC INFO#

                
SELECT * FROM system_info;
SELECT * FROM os_version;
SELECT * FROM kernel_info;
SELECT hostname, cpu_type, physical_memory FROM system_info;

            

                        

                
HARDWARE#

                
SELECT * FROM cpu_info;
SELECT * FROM memory_info;
SELECT * FROM disk_info;
SELECT * FROM interface_details;

            

                        

                
USERS AND GROUPS#

                

            

                        

                
USERS#

                
SELECT * FROM users;
SELECT username, uid, gid, shell, directory FROM users;
SELECT * FROM users WHERE shell NOT LIKE '%nologin%';

            

                        

                
GROUPS#

                
SELECT * FROM groups;
SELECT * FROM user_groups;

            

                        

                
LOGGED IN USERS#

                
SELECT * FROM logged_in_users;
SELECT * FROM last;

            

                        

                
PROCESSES#

                

            

                        

                
LIST PROCESSES#

                
SELECT * FROM processes;
SELECT pid, name, path, cmdline, uid FROM processes;
SELECT * FROM processes WHERE name = 'sshd';

            

                        

                
PROCESS DETAILS#

                
SELECT p.pid, p.name, p.cmdline, u.username
FROM processes p
LEFT JOIN users u ON p.uid = u.uid;

            

                        

                
PROCESS TREE#

                
SELECT pid, name, parent FROM processes;

            

                        

                
MEMORY USAGE#

                
SELECT name, pid, resident_size/1024/1024 AS mem_mb
FROM processes
ORDER BY resident_size DESC
LIMIT 20;

            

                        

                
OPEN FILES#

                
SELECT * FROM process_open_files WHERE pid = 1234;
SELECT * FROM process_open_sockets WHERE pid = 1234;

            

                        

                
NETWORK#

                

            

                        

                
LISTENING PORTS#

                
SELECT * FROM listening_ports;
SELECT DISTINCT port, protocol, address FROM listening_ports;

            

                        

                
ACTIVE CONNECTIONS#

                
SELECT * FROM process_open_sockets;
SELECT * FROM socket_events;

            

                        

                
NETWORK INTERFACES#

                
SELECT * FROM interface_addresses;
SELECT * FROM interface_details;

            

                        

                
DNS RESOLVERS#

                
SELECT * FROM dns_resolvers;

            

                        

                
ARP TABLE#

                
SELECT * FROM arp_cache;

            

                        

                
ROUTES#

                
SELECT * FROM routes;

            

                        

                
FILE SYSTEM#

                

            

                        

                
FILE INFO#

                
SELECT * FROM file WHERE path = '/etc/passwd';
SELECT * FROM hash WHERE path = '/etc/passwd';

            

                        

                
DIRECTORY LISTING#

                
SELECT * FROM file WHERE directory = '/etc/';
SELECT * FROM file WHERE path LIKE '/home/%/.ssh/%';

            

                        

                
FIND FILES#

                
SELECT * FROM file WHERE path LIKE '/tmp/%.exe';
SELECT path, size, mtime FROM file
WHERE directory = '/var/log/' AND filename LIKE '%.log';

            

                        

                
FILE HASHES#

                
SELECT path, md5, sha256 FROM hash
WHERE path = '/usr/bin/bash';

            

                        

                
SUID FILES#

                
SELECT * FROM file WHERE mode LIKE '%s%';
SELECT path, mode, uid FROM file
WHERE path LIKE '/usr/bin/%' AND mode LIKE '4%';

            

                        

                
PERSISTENCE (LINUX)#

                

            

                        

                
CRON JOBS#

                
SELECT * FROM crontab;
SELECT * FROM crontab WHERE command LIKE '%curl%';

            

                        

                
STARTUP ITEMS#

                
SELECT * FROM startup_items;

            

                        

                
SYSTEMD UNITS#

                
SELECT * FROM systemd_units;
SELECT * FROM systemd_units WHERE active_state = 'active';

            

                        

                
KERNEL MODULES#

                
SELECT * FROM kernel_modules;

            

                        

                
PERSISTENCE (WINDOWS)#

                

            

                        

                
AUTORUN#

                
SELECT * FROM autoexec;
SELECT * FROM programs;

            

                        

                
SCHEDULED TASKS#

                
SELECT * FROM scheduled_tasks;

            

                        

                
SERVICES#

                
SELECT * FROM services;
SELECT * FROM services WHERE start_type = 'AUTO_START';

            

                        

                
REGISTRY#

                
SELECT * FROM registry WHERE key LIKE '%\Run%';

            

                        

                
PERSISTENCE (MACOS)#

                

            

                        

                
LAUNCH AGENTS/DAEMONS#

                
SELECT * FROM launchd;
SELECT * FROM launchd WHERE run_at_load = 1;

            

                        

                
LOGIN ITEMS#

                
SELECT * FROM startup_items;

            

                        

                
SECURITY HUNTING#

                

            

                        

                
SUSPICIOUS PROCESSES#

                
# Processes in unusual locations
SELECT * FROM processes
WHERE path LIKE '/tmp/%' OR path LIKE '/dev/shm/%';

# Processes with no path
SELECT * FROM processes WHERE path = '';

# Hidden processes (name starting with .)
SELECT * FROM processes WHERE name LIKE '.%';

            

                        

                
NETWORK ANOMALIES#

                
# Connections to unusual ports
SELECT * FROM process_open_sockets
WHERE remote_port NOT IN (80, 443, 22, 53);

# Listening on suspicious ports
SELECT * FROM listening_ports
WHERE port IN (4444, 5555, 8888);

            

                        

                
FILE ANOMALIES#

                
# Recently modified files
SELECT path, mtime FROM file
WHERE directory = '/etc/'
AND mtime > (strftime('%s', 'now') - 86400);

# Large files in temp
SELECT path, size FROM file
WHERE directory = '/tmp/' AND size > 10000000;

            

                        

                
SHELL HISTORY#

                
SELECT * FROM shell_history;
SELECT * FROM shell_history WHERE command LIKE '%curl%';

            

                        

                
BROWSER DATA#

                
SELECT * FROM chrome_extensions;
SELECT * FROM firefox_addons;

            

                        

                
OSQUERY FLEET#

                

            

                        

                
CONFIGURATION#

                
{
  "options": {
    "host_identifier": "hostname",
    "schedule_splay_percent": 10
  },
  "schedule": {
    "processes": {
      "query": "SELECT * FROM processes;",
      "interval": 60
    },
    "network": {
      "query": "SELECT * FROM listening_ports;",
      "interval": 300
    }
  },
  "packs": {
    "incident-response": "/etc/osquery/packs/incident-response.conf"
  }
}

            

                        

                
PACKS#

                
# Pre-built query packs
/usr/share/osquery/packs/

incident-response
hardware-monitoring
vuln-management
ossec-rootkit

            

                        

                
QUICK REFERENCE#

                
osqueryi                             # Interactive mode
osqueryi "SELECT * FROM users;"      # One-shot query
.tables                              # List tables
.schema processes                    # Show table schema
.mode line                           # Line output
.mode csv                            # CSV output
.quit                                # Exit

# Common queries
SELECT * FROM processes;
SELECT * FROM listening_ports;
SELECT * FROM users;
SELECT * FROM logged_in_users;
SELECT * FROM crontab;
SELECT * FROM file WHERE path = '/etc/passwd';

            

                    

                
        
Defensive reference on CyberRamen. Offensive / red-team sheets live on
            OffensiveRamen.com.