Meta pixel

Fleet documentation

Welcome to the documentation for Fleet, the lightweight management platform for laptops and servers.

search

Can't find what you're looking for? Support

{{page.title}}

Understanding host vitals

Edit page

Understanding host vitals

Following is a summary of the detail queries hardcoded in Fleet used to populate the device details:

battery

  • Platforms: darwin

  • Query:

    SELECT serial_number, cycle_count, health FROM battery;

chromeos​_profile​_user​_info

  • Platforms: chrome

  • Query:

    SELECT email FROM users

disk​_encryption​_darwin

  • Platforms: darwin

  • Query:

    SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND filevault_status = 'on' LIMIT 1

disk​_encryption​_linux

  • Platforms: linux, ubuntu, debian, rhel, centos, sles, kali, gentoo, amzn, pop, arch, linuxmint, void, nixos, endeavouros, manjaro, opensuse-leap, opensuse-tumbleweed

  • Query:

    SELECT de.encrypted, m.path FROM disk_encryption de JOIN mounts m ON m.device_alias = de.name;

disk​_encryption​_windows

  • Platforms: windows

  • Query:

    SELECT 1 FROM bitlocker_info WHERE drive_letter = 'C:' AND protection_status = 1;

disk​_space​_unix

  • Platforms: linux, ubuntu, debian, rhel, centos, sles, kali, gentoo, amzn, pop, arch, linuxmint, void, nixos, endeavouros, manjaro, opensuse-leap, opensuse-tumbleweed, darwin

  • Query:

    SELECT (blocks_available * 100 / blocks) AS percent_disk_space_available,
         round((blocks_available * blocks_size * 10e-10),2) AS gigs_disk_space_available,
         round((blocks           * blocks_size * 10e-10),2) AS gigs_total_disk_space
    FROM mounts WHERE path = '/' LIMIT 1;

disk​_space​_windows

  • Platforms: windows

  • Query:

    SELECT ROUND((sum(free_space) * 100 * 10e-10) / (sum(size) * 10e-10)) AS percent_disk_space_available,
         ROUND(sum(free_space) * 10e-10) AS gigs_disk_space_available,
         ROUND(sum(size)       * 10e-10) AS gigs_total_disk_space
    FROM logical_drives WHERE file_system = 'NTFS' LIMIT 1;

google​_chrome​_profiles

  • Platforms: all

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'google_chrome_profiles';
  • Query:

    SELECT email FROM google_chrome_profiles WHERE NOT ephemeral AND email <> ''

kubequery​_info

  • Platforms: all

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'kubernetes_info';
  • Query:

    SELECT * from kubernetes_info

mdm

  • Platforms: darwin

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'mdm';
  • Query:

    select enrolled, server_url, installed_from_dep, payload_identifier from mdm;

mdm​_config​_profiles​_darwin

  • Platforms: darwin

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'macos_profiles';
  • Query:

    SELECT display_name, identifier, install_date FROM macos_profiles where type = "Configuration";

mdm​_disk​_encryption​_key​_file​_darwin

  • Platforms: darwin

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'filevault_prk';
  • Query:

    WITH
          de AS (SELECT IFNULL((SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND filevault_status = 'on' LIMIT 1), 0) as encrypted),
          fv AS (SELECT base64_encrypted as filevault_key FROM filevault_prk)
      SELECT encrypted, filevault_key FROM de LEFT JOIN fv;

mdm​_disk​_encryption​_key​_file​_lines​_darwin

  • Platforms: darwin

  • Discovery query:

    SELECT 1 WHERE EXISTS (SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'file_lines') AND NOT EXISTS (SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'filevault_prk');
  • Query:

    WITH
          de AS (SELECT IFNULL((SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND filevault_status = 'on' LIMIT 1), 0) as encrypted),
          fl AS (SELECT line FROM file_lines WHERE path = '/var/db/FileVaultPRK.dat')
      SELECT encrypted, hex(line) as hex_line FROM de LEFT JOIN fl;

mdm​_windows

  • Platforms: windows

  • Query:

    WITH registry_keys AS (
                          SELECT *
                          FROM registry
                          WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%'
                      ),
                      enrollment_info AS (
                          SELECT
                              MAX(CASE WHEN name = 'UPN' THEN data END) AS upn,
                              MAX(CASE WHEN name = 'DiscoveryServiceFullURL' THEN data END) AS discovery_service_url,
                              MAX(CASE WHEN name = 'ProviderID' THEN data END) AS provider_id,
                              MAX(CASE WHEN name = 'EnrollmentState' THEN data END) AS state,
                              MAX(CASE WHEN name = 'AADResourceID' THEN data END) AS aad_resource_id
                          FROM registry_keys
                          GROUP BY key
                      ),
                      installation_info AS (
                          SELECT data AS installation_type
                          FROM registry
                          WHERE path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\InstallationType'
                          LIMIT 1
                      )
                      SELECT
                          e.aad_resource_id,
                          e.discovery_service_url,
                          e.provider_id,
                          i.installation_type
                      FROM installation_info i
                      LEFT JOIN enrollment_info e ON e.upn IS NOT NULL
              -- coalesce to 'unknown' and keep that state in the list
              -- in order to account for hosts that might not have this
              -- key, and servers
                      WHERE COALESCE(e.state, '0') IN ('0', '1', '2', '3')
                      LIMIT 1;

munki​_info

  • Platforms: darwin

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'munki_info';
  • Query:

    select version, errors, warnings from munki_info;

network​_interface​_chrome

  • Platforms: chrome

  • Query:

    SELECT ipv4 AS address, mac FROM network_interfaces LIMIT 1

network​_interface​_unix

  • Platforms: linux, ubuntu, debian, rhel, centos, sles, kali, gentoo, amzn, pop, arch, linuxmint, void, nixos, endeavouros, manjaro, opensuse-leap, opensuse-tumbleweed, darwin

  • Query:

    SELECT
      ia.address,
      id.mac
    FROM
      interface_addresses ia
      JOIN interface_details id ON id.interface = ia.interface
      -- On Unix ia.interface is the name of the interface,
      -- whereas on Windows ia.interface is the IP of the interface.
      JOIN routes r ON r.interface = ia.interface
    WHERE
      -- Destination 0.0.0.0/0 is the default route on route tables.
      r.destination = '0.0.0.0' AND r.netmask = 0
      -- Type of route is "gateway" for Unix, "remote" for Windows.
      AND r.type = 'gateway'
      -- We are only interested on private IPs (some devices have their Public IP as Primary IP too).
      AND (
          -- Private IPv4 addresses.
          inet_aton(ia.address) IS NOT NULL AND (
              split(ia.address, '.', 0) = '10'
              OR (split(ia.address, '.', 0) = '172' AND (CAST(split(ia.address, '.', 1) AS INTEGER) & 0xf0) = 16)
              OR (split(ia.address, '.', 0) = '192' AND split(ia.address, '.', 1) = '168')
          )
          -- Private IPv6 addresses start with 'fc' or 'fd'.
          OR (inet_aton(ia.address) IS NULL AND regex_match(lower(ia.address), '^f[cd][0-9a-f][0-9a-f]:[0-9a-f:]+', 0) IS NOT NULL)
      )
    ORDER BY
      r.metric ASC,
      -- Prefer IPv4 addresses over IPv6 addresses if their route have the same metric.
      inet_aton(ia.address) IS NOT NULL DESC
    LIMIT 1;

network​_interface​_windows

  • Platforms: windows

  • Query:

    SELECT
      ia.address,
      id.mac
    FROM
      interface_addresses ia
      JOIN interface_details id ON id.interface = ia.interface
      -- On Unix ia.interface is the name of the interface,
      -- whereas on Windows ia.interface is the IP of the interface.
      JOIN routes r ON r.interface = ia.address
    WHERE
      -- Destination 0.0.0.0/0 is the default route on route tables.
      r.destination = '0.0.0.0' AND r.netmask = 0
      -- Type of route is "gateway" for Unix, "remote" for Windows.
      AND r.type = 'remote'
      -- We are only interested on private IPs (some devices have their Public IP as Primary IP too).
      AND (
          -- Private IPv4 addresses.
          inet_aton(ia.address) IS NOT NULL AND (
              split(ia.address, '.', 0) = '10'
              OR (split(ia.address, '.', 0) = '172' AND (CAST(split(ia.address, '.', 1) AS INTEGER) & 0xf0) = 16)
              OR (split(ia.address, '.', 0) = '192' AND split(ia.address, '.', 1) = '168')
          )
          -- Private IPv6 addresses start with 'fc' or 'fd'.
          OR (inet_aton(ia.address) IS NULL AND regex_match(lower(ia.address), '^f[cd][0-9a-f][0-9a-f]:[0-9a-f:]+', 0) IS NOT NULL)
      )
    ORDER BY
      r.metric ASC,
      -- Prefer IPv4 addresses over IPv6 addresses if their route have the same metric.
      inet_aton(ia.address) IS NOT NULL DESC
    LIMIT 1;

orbit​_info

  • Platforms: all

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'orbit_info';
  • Query:

    SELECT version FROM orbit_info

os​_chrome

  • Platforms: chrome

  • Query:

    SELECT
          os.name,
          os.major,
          os.minor,
          os.patch,
          os.build,
          os.arch,
          os.platform,
          os.version AS version,
          os.version AS kernel_version
      FROM
          os_version os

os​_unix​_like

  • Platforms: linux, ubuntu, debian, rhel, centos, sles, kali, gentoo, amzn, pop, arch, linuxmint, void, nixos, endeavouros, manjaro, opensuse-leap, opensuse-tumbleweed, darwin

  • Query:

    SELECT
          os.name,
          os.major,
          os.minor,
          os.patch,
          os.extra,
          os.build,
          os.arch,
          os.platform,
          os.version AS version,
          k.version AS kernel_version
      FROM
          os_version os,
          kernel_info k

os​_version

  • Platforms: all

  • Query:

    SELECT * FROM os_version LIMIT 1

os​_version​_windows

  • Platforms: windows

  • Query:

    WITH display_version_table AS (
              SELECT data as display_version
              FROM registry
              WHERE path = 'HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion\\DisplayVersion'
          )
          SELECT
              os.name,
              COALESCE(d.display_version, '') AS display_version,
              k.version
          FROM
              os_version os,
              kernel_info k
          LEFT JOIN
              display_version_table d

os​_windows

  • Platforms: windows

  • Query:

    WITH display_version_table AS (
          SELECT data as display_version
          FROM registry
          WHERE path = 'HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion\\DisplayVersion'
      )
      SELECT
          os.name,
          os.platform,
          os.arch,
          k.version as kernel_version,
          os.version,
          COALESCE(d.display_version, '') AS display_version
      FROM
          os_version os,
          kernel_info k
      LEFT JOIN
          display_version_table d

osquery​_flags

  • Platforms: all

  • Query:

    select name, value from osquery_flags where name in ("distributed_interval", "config_tls_refresh", "config_refresh", "logger_tls_period")

osquery​_info

  • Platforms: all

  • Query:

    select * from osquery_info limit 1

scheduled​_query​_stats

  • Platforms: all

  • Query:

    SELECT *,
                  (SELECT value from osquery_flags where name = 'pack_delimiter') AS delimiter
              FROM osquery_schedule

software​_chrome

  • Platforms: chrome

  • Query:

    SELECT
    name AS name,
    version AS version,
    identifier AS extension_id,
    browser_type AS browser,
    'Browser plugin (Chrome)' AS type,
    'chrome_extensions' AS source,
    '' AS vendor,
    '' AS installed_path
    FROM chrome_extensions

software​_linux

  • Platforms: linux, ubuntu, debian, rhel, centos, sles, kali, gentoo, amzn, pop, arch, linuxmint, void, nixos, endeavouros, manjaro, opensuse-leap, opensuse-tumbleweed

  • Query:

    WITH cached_users AS (WITH cached_groups AS (select * from groups)
     SELECT uid, username, type, groupname, shell
     FROM users LEFT JOIN cached_groups USING (gid)
     WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> ''))
    SELECT
    name AS name,
    version AS version,
    'Package (deb)' AS type,
    '' AS extension_id,
    '' AS browser,
    'deb_packages' AS source,
    '' AS release,
    '' AS vendor,
    '' AS arch,
    '' AS installed_path
    FROM deb_packages
    WHERE status = 'install ok installed'
    UNION
    SELECT
    package AS name,
    version AS version,
    'Package (Portage)' AS type,
    '' AS extension_id,
    '' AS browser,
    'portage_packages' AS source,
    '' AS release,
    '' AS vendor,
    '' AS arch,
    '' AS installed_path
    FROM portage_packages
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (RPM)' AS type,
    '' AS extension_id,
    '' AS browser,
    'rpm_packages' AS source,
    release AS release,
    vendor AS vendor,
    arch AS arch,
    '' AS installed_path
    FROM rpm_packages
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (NPM)' AS type,
    '' AS extension_id,
    '' AS browser,
    'npm_packages' AS source,
    '' AS release,
    '' AS vendor,
    '' AS arch,
    path AS installed_path
    FROM npm_packages
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (Chrome)' AS type,
    identifier AS extension_id,
    browser_type AS browser,
    'chrome_extensions' AS source,
    '' AS release,
    '' AS vendor,
    '' AS arch,
    path AS installed_path
    FROM cached_users CROSS JOIN chrome_extensions USING (uid)
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (Firefox)' AS type,
    identifier AS extension_id,
    'firefox' AS browser,
    'firefox_addons' AS source,
    '' AS release,
    '' AS vendor,
    '' AS arch,
    path AS installed_path
    FROM cached_users CROSS JOIN firefox_addons USING (uid)
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (Python)' AS type,
    '' AS extension_id,
    '' AS browser,
    'python_packages' AS source,
    '' AS release,
    '' AS vendor,
    '' AS arch,
    path AS installed_path
    FROM python_packages;

software​_macos

  • Platforms: darwin

  • Query:

    WITH cached_users AS (WITH cached_groups AS (select * from groups)
     SELECT uid, username, type, groupname, shell
     FROM users LEFT JOIN cached_groups USING (gid)
     WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> ''))
    SELECT
    name AS name,
    COALESCE(NULLIF(bundle_short_version, ''), bundle_version) AS version,
    'Application (macOS)' AS type,
    bundle_identifier AS bundle_identifier,
    '' AS extension_id,
    '' AS browser,
    'apps' AS source,
    '' AS vendor,
    last_opened_time AS last_opened_at,
    path AS installed_path
    FROM apps
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (Python)' AS type,
    '' AS bundle_identifier,
    '' AS extension_id,
    '' AS browser,
    'python_packages' AS source,
    '' AS vendor,
    0 AS last_opened_at,
    path AS installed_path
    FROM python_packages
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (Chrome)' AS type,
    '' AS bundle_identifier,
    identifier AS extension_id,
    browser_type AS browser,
    'chrome_extensions' AS source,
    '' AS vendor,
    0 AS last_opened_at,
    path AS installed_path
    FROM cached_users CROSS JOIN chrome_extensions USING (uid)
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (Firefox)' AS type,
    '' AS bundle_identifier,
    identifier AS extension_id,
    'firefox' AS browser,
    'firefox_addons' AS source,
    '' AS vendor,
    0 AS last_opened_at,
    path AS installed_path
    FROM cached_users CROSS JOIN firefox_addons USING (uid)
    UNION
    SELECT
    name As name,
    version AS version,
    'Browser plugin (Safari)' AS type,
    '' AS bundle_identifier,
    '' AS extension_id,
    '' AS browser,
    'safari_extensions' AS source,
    '' AS vendor,
    0 AS last_opened_at,
    path AS installed_path
    FROM cached_users CROSS JOIN safari_extensions USING (uid)
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (Homebrew)' AS type,
    '' AS bundle_identifier,
    '' AS extension_id,
    '' AS browser,
    'homebrew_packages' AS source,
    '' AS vendor,
    0 AS last_opened_at,
    path AS installed_path
    FROM homebrew_packages;

software​_vscode​_extensions

  • Platforms: linux, ubuntu, debian, rhel, centos, sles, kali, gentoo, amzn, pop, arch, linuxmint, void, nixos, endeavouros, manjaro, opensuse-leap, opensuse-tumbleweed, darwin, windows

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'vscode_extensions';
  • Query:

    WITH cached_users AS (WITH cached_groups AS (select * from groups)
     SELECT uid, username, type, groupname, shell
     FROM users LEFT JOIN cached_groups USING (gid)
     WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> ''))
    SELECT
    name,
    version,
    'IDE extension (VS Code)' AS type,
    '' AS bundle_identifier,
    uuid AS extension_id,
    '' AS browser,
    'vscode_extensions' AS source,
    publisher AS vendor,
    '' AS last_opened_at,
    path AS installed_path
    FROM cached_users CROSS JOIN vscode_extensions USING (uid)

software​_windows

  • Platforms: windows

  • Query:

    WITH cached_users AS (WITH cached_groups AS (select * from groups)
     SELECT uid, username, type, groupname, shell
     FROM users LEFT JOIN cached_groups USING (gid)
     WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> ''))
    SELECT
    name AS name,
    version AS version,
    'Program (Windows)' AS type,
    '' AS extension_id,
    '' AS browser,
    'programs' AS source,
    publisher AS vendor,
    install_location AS installed_path
    FROM programs
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (Python)' AS type,
    '' AS extension_id,
    '' AS browser,
    'python_packages' AS source,
    '' AS vendor,
    path AS installed_path
    FROM python_packages
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (IE)' AS type,
    '' AS extension_id,
    '' AS browser,
    'ie_extensions' AS source,
    '' AS vendor,
    path AS installed_path
    FROM ie_extensions
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (Chrome)' AS type,
    identifier AS extension_id,
    browser_type AS browser,
    'chrome_extensions' AS source,
    '' AS vendor,
    path AS installed_path
    FROM cached_users CROSS JOIN chrome_extensions USING (uid)
    UNION
    SELECT
    name AS name,
    version AS version,
    'Browser plugin (Firefox)' AS type,
    identifier AS extension_id,
    'firefox' AS browser,
    'firefox_addons' AS source,
    '' AS vendor,
    path AS installed_path
    FROM cached_users CROSS JOIN firefox_addons USING (uid)
    UNION
    SELECT
    name AS name,
    version AS version,
    'Package (Chocolatey)' AS type,
    '' AS extension_id,
    '' AS browser,
    'chocolatey_packages' AS source,
    '' AS vendor,
    path AS installed_path
    FROM chocolatey_packages

system​_info

  • Platforms: all

  • Query:

    select * from system_info limit 1

uptime

  • Platforms: all

  • Query:

    select * from uptime limit 1

users

  • Platforms: linux, darwin, windows

  • Query:

    WITH cached_groups AS (select * from groups)
     SELECT uid, username, type, groupname, shell
     FROM users LEFT JOIN cached_groups USING (gid)
     WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> '')

users​_chrome

  • Platforms: chrome

  • Query:

    SELECT uid, username, email FROM users

windows​_update​_history

  • Platforms: windows

  • Discovery query:

    SELECT 1 FROM osquery_registry WHERE active = true AND registry = 'table' AND name = 'windows_update_history';
  • Query:

    SELECT date, title FROM windows_update_history WHERE result_code = 'Succeeded'

Did we miss anything?

If you notice something we've missed or could be improved on, please follow this link and submit a pull request to the Fleet repo.

An arrow pointing upBack to top