6.1.5. OSQuery

OSQuery is an operating system instrumentation framework for Windows, OS X (macOS), Linux, and FreeBSD. It uses basic SQL commands to leverage a relational data-model to describe a device.

The three Main characteristics of OSQuery are the following:

  • It is fast and tested

  • It runs everywhere

  • It is open source

References from: https://osquery.readthedocs.io/en/latest/ https://osquery.io/

6.1.5.1. List of queries to gather device information

The following default osqueries are included in the Agent, but users have the ability to modify or add new ones by editing the configuration file located in the installation path of the application (appsettings.json).

6.1.5.1.1. Session events

  • Identifier:

SESSION_EVENTS

  • Windows query:

SELECT DISTINCT 'LOGIN' AS TYPE, 'SESSION_UNLOCKED' AS EVENT, user AS USERNAME, logon_domain AS DOMAINNAME, logon_time AS TIMESTAMP FROM logon_sessions WHERE authentication_package = 'NTLM' AND logon_time = (SELECT MAX(logon_time) FROM logon_sessions);

  • Mac query:

SELECT (CASE WHEN type = '7' OR type = '6' THEN 'LOGIN' ELSE 'LOGOUT' END) AS TYPE, (CASE WHEN type = '7' OR type = '6' THEN 'SESSION_UNLOCKED' ELSE 'SESSION_LOCKED' END) AS EVENT, username AS USERNAME, host AS DOMAINNAME, time AS TIMESTAMP FROM last;

  • Linux query:

SELECT (CASE WHEN type = '7' OR type = '6' THEN 'LOGIN' ELSE 'LOGOUT' END) AS TYPE, (CASE WHEN type = '7' OR type = '6' THEN 'SESSION_UNLOCKED' ELSE 'SESSION_LOCKED' END) AS EVENT, username AS USERNAME, host AS DOMAINNAME, time AS TIMESTAMP FROM last;

6.1.5.1.2. Active users

  • Identifier:

USER_ACTIVE

  • Windows query:

SELECT DISTINCT liu.user AS USER_ACTIVE, ls.logon_domain AS DOMAIN FROM logged_in_users AS liu JOIN logon_sessions AS ls WHERE liu.sid = ls.logon_sid;

  • Mac query:

SELECT DISTINCT user AS NAME, host AS DOMAIN FROM logged_in_users;

  • Linux query:

SELECT DISTINCT user AS NAME, host AS DOMAIN FROM logged_in_users;

6.1.5.1.3. USB devices

  • Identifier:

USB_DEVICE

  • Windows query:

Not compatible yet.

  • Mac query:

SELECT model AS DEVICE_ID, vendor AS MANUFACTURER, model_id AS HARDWARE_ID FROM usb_devices;

  • Linux query:

SELECT model AS DEVICE_ID, vendor AS MANUFACTURER, model_id AS HARDWARE_ID FROM usb_devices;

6.1.5.1.4. Networks

  • Identifier:

NETWORKS

  • Windows query:

SELECT details.connection_id as NAME, details.description as DESCRIPTION, details.dhcp_enabled as DHCP_ENABLED, details.dhcp_server as GATEWAY, details.type as TYPE, details.connection_status AS OPER_STATUS, details.mac as MACADDR, addresses.address as IPADDRESS FROM interface_details AS details JOIN interface_addresses AS addresses ON details.interface = addresses.interface WHERE addresses.address LIKE '%.%.%.%';

  • Mac query:

SELECT details.connection_id as NAME, details.type as TYPE, details.connection_status AS OPER_STATUS, details.mac as MACADDR, addresses.address as IPADDRESS FROM interface_details AS details JOIN interface_addresses AS addresses ON details.interface = addresses.interface WHERE addresses.address LIKE '%.%.%.%';

  • Linux query:

SELECT details.connection_id as NAME, details.type as TYPE, details.mac as MACADDR, addresses.address as IPADDRESS, details.connection_status AS OPER_STATUS FROM interface_details AS details JOIN interface_addresses AS addresses ON details.interface = addresses.interface WHERE addresses.address LIKE '%.%.%.%';

6.1.5.1.5. Processes

  • Identifier:

PROCESS

  • Windows query:

SELECT DISTINCT name as NAME, pid as ID, parent as PARENTID, threads as THREADCOUNT, resident_size as MEMORY FROM processes;

  • Mac query:

SELECT DISTINCT name as NAME, pid as ID, parent as PARENTID, threads as THREADCOUNT, resident_size as MEMORY FROM processes;

  • Linux query:

SELECT DISTINCT name as NAME, pid as ID, parent as PARENTID, threads as THREADCOUNT, resident_size as MEMORY FROM processes;

6.1.5.1.6. Installed softwares

  • Identifier:

SOFTWARES

  • Windows query:

SELECT name AS NAME, version AS VERSION, publisher AS PUBLISHER, install_date AS INSTALLDATE FROM programs;

  • Mac query:

SELECT bundle_name AS NAME, bundle_short_version AS VERSION, copyright AS PUBLISHER FROM apps;

  • Linux query:

SELECT name AS NAME, version AS VERSION FROM deb_packages WHERE status LIKE '%installed%'",

6.1.5.1.7. Certificates

  • Identifier:

CERTIFICATE

  • Windows query:

SELECT DISTINCT issuer as ISSUER, not_valid_before as START_DATE, not_valid_after as END_DATE, subject as SUBJECT FROM certificates;

  • Mac query:

SELECT DISTINCT issuer as ISSUER, not_valid_before as START_DATE, not_valid_after as END_DATE, subject as SUBJECT FROM certificates;

  • Linux query:

Not compatible yet.

6.1.5.1.8. Hardware

  • Identifier:

HARDWARE

  • Windows query:

SELECT si.uuid AS UNIQUE_ID, si.computer_name AS NAME, ov.name AS OSNAME, ov.version AS OSVERSION, ov.build AS RELEASE_ID, si.cpu_type AS ARCH, ld.device_id AS OS_VOLUME, f.device AS SERIAL_NUMBER, bi.protection_status AS BIT_LOCKER, (CASE WHEN si.hardware_serial = '0' OR si.hardware_serial IS NULL or si.hardware_serial = '' THEN '1' ELSE '0' END) AS OS_VM FROM system_info AS si JOIN os_version AS ov JOIN file AS f JOIN logical_drives AS ld JOIN bitlocker_info AS bi WHERE boot_partition = 1 AND path LIKE '%' AND drive_letter = OS_VOLUME LIMIT 1;

  • Mac query:

SELECT si.uuid AS UNIQUE_ID, si.computer_name AS NAME, ov.name AS OSNAME, ov.version AS OSVERSION, ov.build AS RELEASE_ID, si.cpu_type AS ARCH, f.device AS SERIAL_NUMBER, (CASE WHEN si.hardware_serial = '0' OR si.hardware_serial IS NULL or si.hardware_serial = '' THEN '1' ELSE '0' END) AS OS_VM FROM system_info AS si JOIN os_version AS ov JOIN file AS f WHERE path LIKE '%' LIMIT 1;

  • Linux query:

SELECT si.uuid AS UNIQUE_ID, si.computer_name AS NAME, ov.name AS OSNAME, ov.version AS OSVERSION, ov.build AS RELEASE_ID, si.cpu_type AS ARCH, f.device AS SERIAL_NUMBER, (CASE WHEN si.hardware_serial = '0' OR si.hardware_serial IS NULL or si.hardware_serial = '' THEN '1' ELSE '0' END) AS OS_VM FROM system_info AS si JOIN os_version AS ov JOIN file AS f WHERE path LIKE '%' LIMIT 1;

6.1.5.1.9. Security Center

  • Identifier:

SECURITY_CENTER

  • Windows query:

SELECT type AS CATEGORY, (CASE WHEN windows_security_center_service = 'Good' THEN '1' ELSE '0' END) AS SC_ENABLED, name AS COMPANY, name AS PRODUCT, (CASE WHEN state <> 'Off' THEN '1' ELSE '0' END) AS ENABLED, signatures_up_to_date AS UPTODATE FROM windows_security_products JOIN windows_security_center;

  • Mac query:

Not compatible. Mac OS does not have security center.

  • Linux query:

Not compatible. Linux does not have security center.

6.1.5.1.10. WIFI

  • Identifier:

WIFI

  • Windows query:

Not compatible yet.

  • Mac query:

SELECT wsu.ssid AS INTERFACE_GUID, wsu.network_name AS SSID, rssi AS SIGNAL_QUALITY, (CASE WHEN wn.security_type = 'Open' THEN '0' WHEN wn.security_type IS NULL THEN 'Unknown' ELSE '1' END) AS SECURITY_ENABLED, wn.security_type AS SECURITY, (CASE WHEN wn.disabled <> '0' THEN '0' ELSE '1' END) AS CONNECTABLE, (SELECT count(*) FROM wifi_status AS wst WHERE wst.ssid = wsu.ssid) AS IS_CONNECTED FROM wifi_survey AS wsu LEFT JOIN wifi_networks AS wn ON wn.ssid = wsu.ssid GROUP BY wsu.ssid, wsu.network_name, wn.security_type, wn.disabled, wsu.ssid;

  • Linux query:

Not compatible.

6.1.5.1.11. Bluetooth

  • Identifier:

BLUETOOTH

  • Windows query:

Not compatible yet.

  • Mac query:

Not compatible yet.

  • Linux query:

Not compatible.

6.1.5.1.12. Chrome extensions

  • Identifier:

CHROME_EXT

  • Windows query:

SELECT uid AS USER_ID, identifier AS EXTENSION_ID, name AS NAME, description AS description, version AS VERSION, path AS PATH FROM chrome_extensions;

  • Mac query:

SELECT uid AS USER_ID, identifier AS EXTENSION_ID, name AS NAME, description AS description, version AS VERSION, path AS PATH FROM chrome_extensions;

  • Linux query:

SELECT uid AS USER_ID, identifier AS EXTENSION_ID, name AS NAME, description AS description, version AS VERSION, path AS PATH FROM chrome_extensions;

6.1.5.1.13. Firefox extensions

  • Identifier:

FIREFOX_EXT

  • Windows query:

SELECT uid AS USER_ID, identifier AS EXTENSION_ID, name AS NAME, description AS description, version AS VERSION, creator AS PUBLISHER, source_url AS INSTALLATION_URL active AS ACTIVE, path AS PATH FROM firefox_addons;

  • Mac query:

SELECT uid AS USER_ID, identifier AS EXTENSION_ID, name AS NAME, description AS description, version AS VERSION, creator AS PUBLISHER, source_url AS INSTALLATION_URL active AS ACTIVE, path AS PATH FROM firefox_addons;

  • Linux query:

SELECT uid AS USER_ID, identifier AS EXTENSION_ID, name AS NAME, description AS description, version AS VERSION, creator AS PUBLISHER, source_url AS INSTALLATION_URL active AS ACTIVE, path AS PATH FROM firefox_addons;

6.1.5.1.14. Internet Explorer extensions

  • Identifier:

IE_EXT

  • Windows query:

SELECT name AS NAME, identifier AS EXTENSION_ID, version AS VERSION, path AS PATH FROM ie_extensions;

  • Mac query:

Not compatible yet.

  • Linux query:

Not compatible yet.

6.1.5.1.15. Processes running

  • Identifier:

PROCESSES

  • Windows/Mac/Linux query:

SELECT pid AS PID, name AS NAME, parent AS PARENT, threads AS THREADS, total_size AS TOTAL_SIZE, path AS PATH, cmdline AS CMDLINE, start_time AS START_TIME FROM processes ORDER BY start_time DESC;

6.1.5.1.16. Established connections

  • Identifier:

ESTABLISHED_CONNECTIONS

  • Windows/Mac/Linux query:

SELECT pid AS PID, family AS FAMILY, protocol AS PROTOCOL, local_address AS LOCAL_ADDRESS, remote_address AS REMOTE_ADDRESS, local_port AS LOCAL_PORT, remote_port AS REMOTE_PORT, state AS STATE FROM process_open_sockets WHERE state != ‘LISTEN’ and remote_port != ‘0’ AND local_port != ‘0’;

6.1.5.1.17. Open ports

  • Identifier:

OPEN_PORTS

  • Windows/Mac/Linux query:

SELECT pid AS PID, family AS FAMILY, protocol AS PROTOCOL, local_address AS LOCAL_ADDRESS, remote_address AS REMOTE_ADDRESS, local_port AS LOCAL_PORT, remote_port AS REMOTE_PORT, state AS STATE FROM process_open_sockets WHERE state == ‘LISTEN’;