/
Tested custom queries

Tested custom queries

This page contains queries organised by platform. Each query is labelled with a coloured badge indicating the platform(s) it supports: Green = All Platforms, Blue = Windows / macOS, Yellow = Linux / macOS, Purple = macOS only.

Be caution when changing answer limits, as this can cause slow responses or lead to errors.

ALL PLATFORMS Get BIOS and firmware platform information

SELECT address AS 'address', date AS 'platform_date_mdy', extra AS 'extra', revision AS 'revision', size AS 'size', vendor AS 'vendor', volume_size AS 'volume_size' FROM platform_info LIMIT 1;

ALL PLATFORMS Get the device hostname and computer name

SELECT hostname AS 'hostname', computer_name AS 'computer_name', local_hostname AS 'local_hostname' FROM system_info LIMIT 1;

ALL PLATFORMS Get hardware specifications including model, serial, CPU and memory

SELECT hardware_model AS 'model', hardware_serial AS 'serial', hardware_vendor AS 'vendor', hardware_version AS 'version', physical_memory AS 'physical_memory', cpu_brand AS 'cpu_brand', cpu_logical_cores AS 'cpu_logical_cores', cpu_physical_cores AS 'cpu_physical_cores', cpu_type AS 'cpu_type', cpu_subtype AS 'cpu_subtype' FROM system_info LIMIT 1;

ALL PLATFORMS Get operating system name, version and platform details

SELECT name AS 'name', version AS 'version', major AS 'major', minor AS 'minor', patch AS 'patch', platform AS 'platform', platform_like AS 'platform_like', codename AS 'codename', build AS 'build' FROM os_version LIMIT 1;

ALL PLATFORMS Get kernel version and boot arguments

SELECT version AS 'version', arguments AS 'arguments', path AS 'path', device AS 'kernel_device_identifier' FROM kernel_info;

ALL PLATFORMS Get osquery agent version and build information

SELECT version AS 'version', build_distro AS 'build_distro', build_platform AS 'build_platform', extensions AS 'extensions' FROM osquery_info LIMIT 1;

ALL PLATFORMS Get system uptime in days, hours and minutes

SELECT days AS 'days', hours AS 'hours', minutes AS 'minutes', seconds AS 'seconds', total_seconds AS 'total_seconds' FROM uptime LIMIT 1;

ALL PLATFORMS List ARP cache entries showing IP and MAC address mappings

SELECT address || mac || interface AS 'unique_id', address AS 'ip_address', mac AS 'mac_address', interface AS 'interface', permanent AS 'permanent' FROM arp_cache WHERE ip_address NOT LIKE '169.254%';

ALL PLATFORMS List active listening ports with associated process names

SELECT DISTINCT process.name || listening.port || listening.address || process.pid AS 'unique_id', process.name AS 'name', process.path AS 'path', listening.port AS 'port', listening.address AS 'address', process.pid AS 'pid' FROM processes AS process JOIN listening_ports AS listening ON process.pid = listening.pid;

ALL PLATFORMS List Python packages installed on the endpoint

SELECT name AS 'name', version AS 'version', summary AS 'summary', author AS 'author', license AS 'license', path AS 'path', directory AS 'directory' FROM python_packages;

ALL PLATFORMS List installed Chrome browser extensions per user

SELECT name AS 'name', identifier AS 'identifier', version AS 'version', chrome_extensions.description AS 'description', update_url AS 'update_url', persistent AS 'persistent', author AS 'author', path AS 'path', chrome_extensions.uid AS 'uid' FROM users CROSS JOIN chrome_extensions USING (uid);

ALL PLATFORMS List entries defined in the hosts file

SELECT address || hostnames AS 'unique_id', address AS 'address', hostnames AS 'hostnames' FROM etc_hosts;

ALL PLATFORMS List osquery configuration flags and their current values

SELECT name AS 'name', type AS 'kind', value AS 'value', default_value AS 'default_value' FROM osquery_flags WHERE shell_only = 0;

ALL PLATFORMS List all scheduled osquery queries and their execution statistics

SELECT name AS 'name', query AS 'query', interval AS 'interval', executions AS 'executions', last_executed AS 'last_executed', blacklisted AS 'blacklisted', output_size AS 'output_size', wall_time AS 'wall_time', user_time AS 'user_time', system_time AS 'system_time', average_memory AS 'average_memory' FROM osquery_schedule WHERE (name IS NOT '' AND name IS NOT NULL);

WINDOWS / MACOS List installed TLS/SSL certificates from the system store

SELECT common_name AS 'common_name', subject AS 'subject', issuer AS 'issuer', ca AS 'is_authority', self_signed AS 'self_signed', datetime(certificates.not_valid_before, 'unixepoch') AS 'not_valid_before', datetime(certificates.not_valid_after, 'unixepoch') AS 'not_valid_after', signing_algorithm AS 'signing_algorithm', key_algorithm AS 'key_algorithm', key_strength AS 'key_strength', key_usage AS 'key_usage', sha1 AS 'sha1', path AS 'path', serial AS 'serial' FROM certificates;

WINDOWS / MACOS List programs and services configured to run at startup

SELECT name AS 'name', path AS 'path', args AS 'args', type AS 'startup_item_type', source AS 'source', status AS 'status', username AS 'username' FROM startup_items WHERE (path IS NOT '' AND path IS NOT NULL);

LINUX / MACOS Get Docker daemon status and container counts

SELECT id AS 'docker_system_id', containers AS 'containers', containers_running AS 'containers_running', containers_paused AS 'containers_paused', containers_stopped AS 'containers_stopped', images AS 'images', storage_driver AS 'storage_driver', architecture AS 'architecture', cpus AS 'cpus', memory AS 'memory', server_version AS 'server_version', root_dir AS 'root_dir', version AS 'version', api_version AS 'api_version', go_version AS 'go_version', os_type AS 'os_type' FROM docker_version, docker_info LIMIT 1;

LINUX / MACOS Get the most frequent console user and their login count

WITH most_frequent_console_user AS ( SELECT username, count(username) AS occurrence FROM last WHERE tty = 'console' OR tty = ':0' GROUP BY username ORDER BY occurrence DESC ) SELECT u.uid AS 'uid', u.username AS 'username', occurrence AS 'logins_count' FROM most_frequent_console_user JOIN users u ON u.username = most_frequent_console_user.username;

LINUX / MACOS List configured DNS resolver addresses

SELECT id || type || address || netmask AS 'unique_id', id AS 'resolver_id', type AS 'resolver_type', address AS 'address', netmask AS 'netmask' FROM dns_resolvers;

LINUX / MACOS List network interfaces with IP addresses and traffic statistics

SELECT interface_details.interface AS 'name', mac AS 'mac_address', interface_details.type AS 'interface_type', mtu AS 'mtu', flags AS 'flags', ipackets AS 'ipackets', opackets AS 'opackets', ibytes AS 'ibytes', obytes AS 'obytes', ierrors AS 'ierrors', oerrors AS 'oerrors', address AS 'address', mask AS 'mask', broadcast AS 'broadcast', link_speed AS 'link_speed' FROM interface_addresses JOIN interface_details USING (interface);

LINUX / MACOS List mounted storage devices with filesystem and encryption status

SELECT block_devices.label AS 'label', block_devices.type AS 'storage_device_type', block_devices.vendor AS 'storage_device_vendor', block_devices.model AS 'storage_device_model', mounts.device_alias AS 'storage_device_alias', mounts.device AS 'storage_device', mounts.blocks AS 'blocks', mounts.blocks_available AS 'blocks_available', mounts.blocks_size AS 'blocks_size', mounts.path AS 'path', mounts.type AS 'filesystem', disk_encryption.encrypted AS 'encrypted', disk_encryption.type AS 'encryption_type' FROM mounts LEFT OUTER JOIN block_devices ON mounts.device_alias = block_devices.name LEFT OUTER JOIN disk_encryption ON mounts.device_alias = disk_encryption.name;

LINUX / MACOS List sudoers rules and elevated privilege definitions

SELECT header || rule_details AS 'unique_id', header AS 'header', rule_details AS 'rule_details' FROM sudoers;

LINUX / MACOS List scheduled cron jobs on the endpoint

SELECT event || minute || hour || day_of_month || month || day_of_week || command || path AS 'unique_id', event AS 'event', minute AS 'minute', hour AS 'hour', day_of_month AS 'day_of_month', month AS 'month', day_of_week AS 'day_of_week', command AS 'command', path AS 'path' FROM crontab;

LINUX / MACOS List removable USB devices currently connected to the endpoint

SELECT DISTINCT usb_address || usb_port || model AS 'unique_id', usb_address AS 'usb_address', usb_port AS 'usb_port', vendor AS 'vendor', vendor_id AS 'vendor_id', version AS 'version', model AS 'model', model_id AS 'model_id', serial AS 'serial', class AS 'usb_class', subclass AS 'usb_subclass', protocol AS 'protocol' FROM usb_devices WHERE removable = 1;

MACOS Get macOS Application Layer Firewall (ALF) configuration

SELECT allow_signed_enabled AS 'allow_signed_enabled', firewall_unload AS 'firewall_unload', global_state AS 'global_state', logging_enabled AS 'logging_enabled', logging_option AS 'logging_option', stealth_enabled AS 'stealth_enabled', version AS 'firewall_version' FROM alf LIMIT 1;

MACOS Get macOS Gatekeeper security configuration

SELECT assessments_enabled AS 'assessments_enabled', dev_id_enabled AS 'dev_id_enabled', version AS 'version', opaque_version AS 'opaque_version' FROM gatekeeper LIMIT 1;

MACOS Get battery status, health and charge information

SELECT manufacturer AS 'manufacturer', model AS 'model', serial_number AS 'serial_number', cycle_count AS 'cycle_count', health AS 'health', condition AS 'condition', state AS 'state', charging AS 'charging', charged AS 'charged', designed_capacity AS 'designed_capacity', max_capacity AS 'max_capacity', current_capacity AS 'current_capacity', percent_remaining AS 'percent_remaining', minutes_until_empty AS 'minutes_until_empty', minutes_to_full_charge AS 'minutes_to_full_charge' FROM battery LIMIT 1;

MACOS Get macOS sharing preferences (screen sharing, file sharing, remote login)

SELECT screen_sharing AS 'screen_sharing', file_sharing AS 'file_sharing', printer_sharing AS 'printer_sharing', remote_login AS 'remote_login', remote_management AS 'remote_management', remote_apple_events AS 'remote_apple_events', internet_sharing AS 'internet_sharing', disc_sharing AS 'disc_sharing', content_caching AS 'content_caching' FROM sharing_preferences LIMIT 1;

MACOS Get the Bluetooth sharing preference for the current console user

WITH bluetooth_plist AS ( SELECT f.path, f.filename, DATETIME(f.mtime, 'unixepoch') AS last_modified, '/Users/' || SPLIT(path, '/', 1) AS directory FROM plist p, file f USING(path) WHERE p.path IN (SELECT path FROM file WHERE path LIKE '/Users/%/Library/Preferences/ByHost/com.apple.Bluetooth%') GROUP BY path), bluetooth_users AS ( SELECT bp.*, u.username AS user FROM bluetooth_plist bp, users u USING(directory)), bluetooth_sharing_liu AS ( SELECT bu.* FROM bluetooth_users bu, logged_in_users liu USING(user) WHERE liu.tty = 'console' ORDER BY last_modified DESC LIMIT 1), bluetooth_sharing_current AS ( SELECT MAX(CASE WHEN key = 'PrefKeyServicesEnabled' THEN value END) AS bluetooth_sharing_value, bsl.* FROM plist p, bluetooth_sharing_liu bsl USING(path) GROUP BY path), bluetooth_sharing AS ( SELECT user, last_modified, bluetooth_sharing_value, CASE WHEN bluetooth_sharing_value IS NULL THEN '0' WHEN bluetooth_sharing_value = '1' THEN '1' WHEN bluetooth_sharing_value = '0' THEN '0' END AS value FROM bluetooth_sharing_current) SELECT value AS 'bluetooth_sharing' FROM bluetooth_sharing;

MACOS Get XProtect antimalware signature version and hash

SELECT (SELECT mtime FROM file WHERE path='/System/Library/CoreServices/XProtect.bundle/Contents/Resources/XProtect.plist') AS 'signatures_updated_at_epoch', (SELECT value FROM plist WHERE key='Version' AND path='/System/Library/CoreServices/XProtect.bundle/Contents/Resources/XProtect.meta.plist') AS 'version', (SELECT md5(group_concat(xe.name)) FROM xprotect_entries xe) AS 'entries_hash', (SELECT md5(group_concat(xm.identifier)) FROM xprotect_meta xm) AS 'meta_entries_hash' LIMIT 1;

MACOS List XProtect antimalware signature entries

SELECT name || filename || filetype || identity || optional || launch_type || uses_pattern AS 'unique_id', name AS 'name', filename AS 'filename', filetype AS 'filetype', identity AS 'identity', optional AS 'optional', launch_type AS 'launch_type', uses_pattern AS 'uses_pattern' FROM xprotect_entries;

MACOS List XProtect metadata entries for known malware identifiers

SELECT type AS 'meta_entry_type', identifier AS 'identifier', min_version AS 'min_version', developer_id AS 'developer_id' FROM xprotect_meta;

MACOS Get System Integrity Protection (SIP) configuration flags

SELECT config_flag AS 'config_flag', enabled AS 'enabled', enabled_nvram AS 'enabled_nvram' FROM sip_config;

MACOS List Homebrew packages installed on macOS

SELECT name AS 'name', path AS 'path', version AS 'version' FROM homebrew_packages;

MACOS List loaded macOS kernel extensions (kexts) with signing info

SELECT idx AS 'idx', kernel_extensions.path AS 'path', name AS 'name', version AS 'version', cdhash AS 'cdhash', authority AS 'authority', team_identifier AS 'team_identifier', identifier AS 'identifier', signed AS 'signed' FROM kernel_extensions JOIN signature ON kernel_extensions.path = signature.path;

MACOS List launchd daemon and agent entries

SELECT path AS 'path', name AS 'name', label AS 'label', program AS 'program', run_at_load AS 'run_at_load', keep_alive AS 'keep_alive', disabled AS 'disabled', username AS 'username', groupname AS 'groupname', stdout_path AS 'stdout_path', stderr_path AS 'stderr_path', start_interval AS 'start_interval', program_arguments AS 'program_arguments', process_type AS 'process_type' FROM launchd;

MACOS List folders shared over the network on macOS

SELECT name AS 'name', path AS 'path' FROM shared_folders;

MACOS Get current WiFi connection status and network details

SELECT interface AS 'interface', ssid AS 'ssid', bssid AS 'bssid', network_name AS 'network_name', country_code AS 'country_code', security_type AS 'security_type', rssi AS 'rssi', noise AS 'noise', channel AS 'channel', channel_width AS 'channel_width', channel_band AS 'channel_band', transmit_rate AS 'transmit_rate', mode AS 'mode' FROM wifi_status LIMIT 1;

MACOS List URL scheme handlers registered on macOS

SELECT scheme || handler AS 'unique_id', scheme AS 'scheme', handler AS 'handler', enabled AS 'enabled', external AS 'external', protected AS 'protected' FROM app_schemes;

MACOS List macOS system authorization policies

SELECT label AS 'label', modified AS 'authorization_modified_at_cocoa', allow_root AS 'allow_root', timeout AS 'timeout', version AS 'version', tries AS 'tries', authenticate_user AS 'authenticate_user', shared AS 'shared', class AS 'authorization_class', session_owner AS 'session_owner' FROM authorizations WHERE (label IS NOT '' AND label IS NOT NULL);

MACOS Get user account policy data including login history and password age

SELECT uid AS 'uid', creation_time AS 'user_created_at_epoch', failed_login_count AS 'failed_logins_count', failed_login_timestamp AS 'last_failed_login_at_epoch', password_last_set_time AS 'password_last_set_at_epoch' FROM account_policy_data;

MACOS Get macOS login window settings (guest access, last logged-in user)

SELECT (SELECT value FROM plist WHERE path = '/Library/Preferences/com.apple.loginwindow.plist' AND key = 'GuestEnabled') AS guest_enabled, (SELECT value FROM plist WHERE path = '/Library/Preferences/com.apple.loginwindow.plist' AND key = 'lastUserName') AS last_user_name, (SELECT value FROM plist WHERE path = '/Library/Preferences/com.apple.loginwindow.plist' AND key = 'UseVoiceOverLegacyMigrated') AS use_voice_over_legacy_migrated;

MACOS List NVRAM variable entries

SELECT name AS 'name', type AS 'value_type', value AS 'value' FROM nvram;

MACOS Check if osquery has sufficient macOS full-disk access permissions

SELECT total_seconds, CASE WHEN (SELECT 1 WHERE EXISTS(SELECT 1 FROM os_version WHERE minor > 14) AND EXISTS(SELECT 1 FROM file WHERE path = '/Library/Preferences/com.apple.TimeMachine.plist') AND NOT EXISTS(SELECT 1 FROM plist WHERE path = '/Library/Preferences/com.apple.TimeMachine.plist' LIMIT 1)) THEN 'true' WHEN (SELECT 1 FROM os_version WHERE minor > 14) THEN 'false' END AS has_insufficient_macos_permissions FROM uptime;