6.1.7. 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/
OSQuery is integrated into the Multiplatform Agent, ensuring it is always up-to-date with the latest version. OSQuery is integrated directly within the package for seamless functionality.
6.1.7.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.7.1.1. Active admin users
Identifier:
ACTIVE_ADMIN_USERS
Windows query:
"SELECT DISTINCT username AS NAME, groupname AS GROUPNAME FROM users AS u JOIN user_groups AS ug ON u.uid = ug.uid JOIN groups AS g ON ug.gid = g.gid JOIN logon_sessions AS ls ON u.uuid = ls.logon_sid WHERE user != '' AND ug.gid = 544"
Mac query:
"SELECT DISTINCT username AS NAME, groupname AS GROUPNAME FROM users AS u JOIN user_groups AS ug ON u.uid = ug.uid JOIN groups AS g ON ug.gid = g.gid JOIN logged_in_users AS liu ON u.username = liu.user WHERE liu.type = 'user' AND user != '' AND g.groupname = 'admin'"
Linux query:
"SELECT DISTINCT username AS NAME, groupname AS GROUPNAME FROM users AS u JOIN user_groups AS ug ON u.uid = ug.uid JOIN groups AS g ON ug.gid = g.gid JOIN processes AS p ON u.uid = p.uid WHERE g.groupname = 'sudo' OR g.groupname = 'adm' AND username != 'syslog'"
6.1.7.1.2. 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.7.1.3. Networks
Identifier:
NETWORKS
Windows query:
"SELECT details.connection_id as NAME, (CASE WHEN details.physical_adapter = '1' THEN 'Physical' WHEN details.physical_adapter = '0' THEN 'Virtual' ELSE 'Unknown' END) AS INTERFACE_TYPE, details.description as DESCRIPTION, details.dhcp_enabled as DHCP_ENABLED, (SELECT gateway FROM routes WHERE interface = addresses.address ORDER BY netmask ASC) as GATEWAY, details.type as TYPE, details.connection_status AS OPER_STATUS, details.mac as MACADDR, (CASE WHEN details.mac LIKE '%2:%:%:%:%' OR details.mac LIKE '%6:%:%:%:%' OR LOWER(details.mac) LIKE '%a:%:%:%:%' OR LOWER(details.mac) LIKE '%e:%:%:%:%' THEN 'TRUE' ELSE 'FALSE' END) AS RANDOM_MAC, 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.interface as NAME, (CASE WHEN addresses.point_to_point = '' OR addresses.point_to_point IS NULL THEN 'Unknown' ELSE 'Virtual' END) AS INTERFACE_TYPE, CONCAT_WS(' - ', details.interface, (CASE WHEN address IS NULL THEN 'Unknown' WHEN INET_ATON(address) IS NULL THEN 'v6' ELSE 'v4' END), (CASE WHEN addresses.point_to_point = '' OR addresses.point_to_point IS NULL THEN 'Unknown' ELSE 'Virtual' END)) AS DESCRIPTION, (SELECT gateway FROM routes WHERE interface = addresses.interface ORDER BY netmask ASC) as GATEWAY, details.type as TYPE, (SELECT count(DISTINCT i_add.interface) FROM interface_addresses AS i_add WHERE i_add.interface = details.interface) AS OPER_STATUS, details.mac as MACADDR, (CASE WHEN details.mac LIKE '%2:%:%:%:%' OR details.mac LIKE '%6:%:%:%:%' OR LOWER(details.mac) LIKE '%a:%:%:%:%' OR LOWER(details.mac) LIKE '%e:%:%:%:%' THEN 'TRUE' ELSE 'FALSE' END) AS RANDOM_MAC, SPLIT(addresses.address, '%', 0) as IPADDRESS FROM interface_details AS details LEFT JOIN interface_addresses AS addresses ON details.interface = addresses.interface"
Linux query:
"SELECT details.interface as NAME, (CASE WHEN addresses.point_to_point = '' OR addresses.point_to_point IS NULL THEN 'Unknown' ELSE 'Virtual' END) AS INTERFACE_TYPE, CONCAT_WS(' - ', details.interface, (CASE WHEN address IS NULL THEN 'Unknown' WHEN INET_ATON(address) IS NULL THEN 'v6' ELSE 'v4' END), (CASE WHEN addresses.point_to_point = '' OR addresses.point_to_point IS NULL THEN 'Unknown' ELSE 'Virtual' END)) AS DESCRIPTION, (SELECT gateway FROM routes WHERE interface = addresses.interface ORDER BY netmask ASC) as GATEWAY, details.type as TYPE, (SELECT count(DISTINCT i_add.interface) FROM interface_addresses AS i_add WHERE i_add.interface = details.interface) AS OPER_STATUS, details.mac as MACADDR, (CASE WHEN details.mac LIKE '%2:%:%:%:%' OR details.mac LIKE '%6:%:%:%:%' OR LOWER(details.mac) LIKE '%a:%:%:%:%' OR LOWER(details.mac) LIKE '%e:%:%:%:%' THEN 'TRUE' ELSE 'FALSE' END) AS RANDOM_MAC, SPLIT(addresses.address, '%', 0) as IPADDRESS FROM interface_details AS details LEFT JOIN interface_addresses AS addresses ON details.interface = addresses.interface"
6.1.7.1.4. Processes
Identifier:
PROCESS
Windows, Mac and, Linux query:
"SELECT pid AS PID, name AS NAME, username AS USERNAME, parent AS PARENT, threads AS THREADS, total_size AS TOTAL_SIZE, path AS PATH, cmdline AS CMDLINE, start_time AS START_TIME FROM processes AS p JOIN users AS u ON u.uid = p.uid ORDER BY start_time DESC"
6.1.7.1.5. System updates
Identifier:
SYSTEM_UPDATES
Mac query:
"SELECT name AS TITLE, package_id AS PACKAGE_ID, version AS VERSION, source AS SOURCE, time AS DATE, datetime(time, 'unixepoch') as DATE_FORMATTED FROM package_install_history WHERE source = 'softwareupdated' ORDER BY DATE DESC"
6.1.7.1.6. Softwares
Identifier:
SOFTWARES
Windows query:
"SELECT (CASE WHEN name LIKE '%(KB%)%' THEN '1' ELSE '0' END) AS ISUPDATE, name AS NAME, version AS VERSION, publisher AS PUBLISHER, (CASE WHEN install_date = '' THEN 'UNKNOWN' ELSE install_date END) 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.7.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.7.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, si.cpu_type AS ARCH, ld.device_id AS OS_VOLUME, si.hardware_serial AS SERIAL_NUMBER, (CASE WHEN si.hardware_serial = '0' OR si.hardware_serial IS NULL or si.hardware_serial = '' or si.hardware_serial = 'NO_DEVICE_SN' THEN '1' ELSE '0' END) AS OS_VM, si.hardware_model AS HARDWARE_MODEL, hardware_vendor AS HARDWARE_VENDOR, physical_memory AS RAM_MEMORY_IN_BYTES, cpu_physical_cores AS CORES, cpu_logical_cores AS LOGICAL_CORES, cpu_brand AS PROCESSOR_MODEL FROM system_info AS si JOIN os_version AS ov JOIN logical_drives AS ld WHERE boot_partition = 1 LIMIT 1"
Mac query:
"SELECT si.uuid AS UNIQUE_ID, si.computer_name AS NAME, ov.name AS OSNAME, ov.version AS OSVERSION, si.cpu_type AS ARCH, si.hardware_serial AS SERIAL_NUMBER, (CASE WHEN si.hardware_serial = '0' OR si.hardware_serial IS NULL or si.hardware_serial = '' or si.hardware_serial = 'NO_DEVICE_SN' THEN '1' ELSE '0' END) AS OS_VM, si.hardware_model AS HARDWARE_MODEL, hardware_vendor AS HARDWARE_VENDOR, physical_memory AS RAM_MEMORY_IN_BYTES, cpu_physical_cores AS CORES, cpu_logical_cores AS LOGICAL_CORES, cpu_brand AS PROCESSOR_MODEL FROM system_info AS si JOIN os_version AS ov LIMIT 1",
Linux query:
"SELECT si.uuid AS UNIQUE_ID, si.computer_name AS NAME, ov.name AS OSNAME, ov.version AS OSVERSION, si.cpu_type AS ARCH, si.hardware_serial AS SERIAL_NUMBER, (CASE WHEN si.hardware_serial = '0' OR si.hardware_serial IS NULL or si.hardware_serial = '' or si.hardware_serial = 'NO_DEVICE_SN' THEN '1' ELSE '0' END) AS OS_VM, si.hardware_model AS HARDWARE_MODEL, hardware_vendor AS HARDWARE_VENDOR, physical_memory AS RAM_MEMORY_IN_BYTES, cpu_physical_cores AS CORES, cpu_logical_cores AS LOGICAL_CORES, cpu_brand AS PROCESSOR_MODEL FROM system_info AS si JOIN os_version AS ov LIMIT 1",
6.1.7.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 PRODUCT, (CASE WHEN state = 'On' THEN '1' ELSE '0' END) AS ENABLED, signatures_up_to_date AS UPTODATE FROM windows_security_center JOIN windows_security_products"
Mac query:
Not compatible. Mac OS does not have security center.
Linux query:
Not compatible. Linux does not have security center.
6.1.7.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.7.1.11. Chrome extensions
Identifier:
CHROME_EXT
Windows, Mac, and Linux query:
"SELECT c.uid AS USER_ID, u.username AS USERNAME, c.identifier AS EXTENSION_ID, c.name AS NAME, c.description AS description, c.version AS VERSION, c.path AS PATH FROM users AS u JOIN chrome_extensions as c USING (uid)"
6.1.7.1.12. Firefox extensions
Identifier:
FIREFOX_EXT
Windows, Mac, and Linux query:
"SELECT f.uid AS USER_ID, u.username AS USERNAME, f.identifier AS EXTENSION_ID, f.name AS NAME, f.description AS description, f.version AS VERSION, f.creator AS PUBLISHER, f.source_url AS INSTALLATION_URL, f.active AS ACTIVE, f.path AS PATH FROM users AS u CROSS JOIN firefox_addons AS f USING (uid)"
6.1.7.1.13. Internet Explorer extensions
Identifier:
IE_EXT
Windows query:
"SELECT name AS NAME, GROUP_CONCAT((registry_path),'\r\n') AS EXTENSION_ID, version AS VERSION, path AS PATH FROM ie_extensions WHERE name != '' GROUP by name"
Mac query:
Not compatible yet.
Linux query:
Not compatible yet.
6.1.7.1.14. Processes running
Identifier:
PROCESSES
Windows, Mac, and Linux query:
"SELECT pid AS PID, name AS NAME, username AS USERNAME, parent AS PARENT, threads AS THREADS, total_size AS TOTAL_SIZE, path AS PATH, cmdline AS CMDLINE, start_time AS START_TIME FROM processes AS p JOIN users AS u ON u.uid = p.uid ORDER BY start_time DESC",
"AvailablePlatforms": "Windows,Linux,OSX"
6.1.7.1.15. Established connections
Identifier:
ESTABLISHED_CONNECTIONS
Windows, Mac, and 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.7.1.16. Open ports
Identifier:
OPEN_PORTS
Windows, Mac, and 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'"
6.1.7.1.17. ARP
Identifier:
ARP
Windows/Mac/Linux query:
"SELECT DISTINCT address AS ADDRESS, mac AS MAC, interface AS INTERFACE, permanent AS PERMANENT FROM arp_cache WHERE address NOT LIKE '224.%.%.%' AND address NOT LIKE '225.%.%.%' AND address NOT LIKE '226.%.%.%' AND address NOT LIKE '227.%.%.%' AND address NOT LIKE '228.%.%.%' AND address NOT LIKE '229.%.%.%' AND address NOT LIKE '230.%.%.%' AND address NOT LIKE '231.%.%.%' AND address NOT LIKE '232.%.%.%' AND address NOT LIKE '233.%.%.%' AND address NOT LIKE '234.%.%.%' AND address NOT LIKE '235.%.%.%' AND address NOT LIKE '236.%.%.%' AND address NOT LIKE '237.%.%.%' AND address NOT LIKE '238.%.%.%' AND address NOT LIKE '239.%.%.%' AND mac NOT LIKE 'FF:FF:FF:FF:FF:FF' AND mac NOT LIKE '00:00:00:00:00:00' AND mac NOT LIKE 'incomplete';"
6.1.7.1.18. BitLocker
Identifier:
BITLOCKER
Windows query:
"SELECT device_id as DEVICE_ID, drive_letter as DRIVE_LETTER, persistent_volume_id as PERSISTENT_VOLUME_ID, conversion_status as CONVERSION_STATUS, protection_status as PROTECTION_STATUS, encryption_method as ENCRYPTION_METHOD, version as VERSION, percentage_encrypted as PERCENTAGE_ENCRYPTED, lock_status as LOCK_STATUS FROM bitlocker_info;"