Skip to content

VIEWS.md

Latest commit

 

History

History
240 lines (187 loc) · 7.18 KB

VIEWS.md

File metadata and controls

240 lines (187 loc) · 7.18 KB
Raw

Database Views

The fact_inventory table stores arbitrary JSON in system_facts, package_facts, and local_facts. PostgreSQL views project frequently needed fields as queryable columns, eliminating repeated JSONB operator syntax in SQL.

Built-in CMDB Views

The following views are automatically created by database migrations. Refer to migration docstrings in migrations/versions/ for full column documentation and JSON path references. Schema details are maintained there; this document is not updated when view definitions change.

Views are read-only projections with no storage overhead and reflect current data at query time.

cmdb_host_interfaces

All non-loopback network interfaces per host. One row per interface.

Key columns: client_address, interface_name, device_type, mac_address, is_active, mtu, last_updated_at

-- All active interfaces across hosts
SELECT client_address, interface_name, device_type, mac_address
  FROM cmdb_host_interfaces
 WHERE is_active;

-- Find hosts with a specific interface type
SELECT DISTINCT client_address
  FROM cmdb_host_interfaces
 WHERE device_type = 'ether';

cmdb_host_interface_ipv4_addresses

IPv4 addresses and derived netmask/network info per interface. One row per address per interface per host. Excludes loopback (127.0.0.0/8).

Key columns: client_address, interface_name, ipv4_address, ipv4_netmask, ipv4_prefix, ipv4_network, ipv4_broadcast, last_updated_at

-- Find all hosts on a specific subnet
SELECT DISTINCT client_address, interface_name
  FROM cmdb_host_interface_ipv4_addresses
 WHERE ipv4_network = '10.0.0.0/24'::inet;

-- List all IPv4 addresses by host
SELECT client_address, interface_name, ipv4_address, ipv4_netmask
  FROM cmdb_host_interface_ipv4_addresses
 WHERE ipv4_address IS NOT NULL;

cmdb_host_interface_ipv6_addresses

IPv6 addresses and prefix info per interface. One row per address per interface per host. Excludes loopback (::1).

Key columns: client_address, interface_name, ipv6_address, ipv6_prefix, ipv6_scope, last_updated_at

-- Find all link-local IPv6 addresses
SELECT client_address, interface_name, ipv6_address, ipv6_scope
  FROM cmdb_host_interface_ipv6_addresses
 WHERE ipv6_scope = 'link';

-- IPv6 addresses by host
SELECT client_address, interface_name, ipv6_address, ipv6_prefix
  FROM cmdb_host_interface_ipv6_addresses
 WHERE ipv6_address IS NOT NULL;

cmdb_host_hardware

CPU, chassis, and hardware identification metadata per host. One row per host.

Key columns: client_address, board_model, product_name, product_uuid, cpu_manufacturer, cpu_model_name, cpu_socket_count, cpu_core_count, cpu_thread_count, ram_bytes, chassis_form_factor, system_arch, last_updated_at

-- Hosts grouped by CPU model
SELECT cpu_model_name, count(*) AS host_count
  FROM cmdb_host_hardware
 GROUP BY cpu_model_name
 ORDER BY host_count DESC;

-- Find hosts with insufficient cores (< 4)
SELECT client_address, cpu_model_name, cpu_core_count, ram_bytes
  FROM cmdb_host_hardware
 WHERE cpu_core_count < 4;

cmdb_host_storage_devices

Physical and virtual storage devices with partitions as JSONB. One row per device per host.

Key columns: client_address, device_name, device_model, device_vendor, device_size_bytes, is_rotational, is_virtual, is_removable, is_fibre, partitions (JSONB), last_updated_at

-- Total storage per host
SELECT client_address,
       count(*) AS device_count,
       sum(device_size_bytes) AS total_bytes
  FROM cmdb_host_storage_devices
 WHERE NOT is_virtual
 GROUP BY client_address;

-- Physical spinning disks (HDDs)
SELECT client_address, device_name, device_model, device_size_bytes
  FROM cmdb_host_storage_devices
 WHERE is_rotational AND NOT is_virtual;

cmdb_host_os_info

Operating system and machine metadata per host. One row per host.

Key columns: client_address, fqdn, machine_id, os_name, os_major_version, os_version, os_version_full, kernel, last_updated_at

-- Count hosts by OS distribution and version
SELECT os_version_full, count(*) AS host_count
  FROM cmdb_host_os_info
 GROUP BY os_version_full
 ORDER BY host_count DESC;

-- Hosts running a specific kernel version
SELECT client_address, fqdn, os_version_full, kernel
  FROM cmdb_host_os_info
 WHERE kernel ~ '^6\.1';

Example Custom Views

The following are template patterns for creating additional views tailored to your fact schema. Adjust JSON paths and field names to match the facts your clients submit.

Stale Hosts

Hosts that have not checked in within a given window. Useful for monitoring and alerting:

CREATE OR REPLACE VIEW stale_hosts AS
SELECT client_address
     , system_facts->>'hostname'  AS hostname
     , updated_at
     , now() - updated_at         AS time_since_update
FROM fact_inventory
WHERE updated_at < now() - interval '7 days';
SELECT * FROM stale_hosts ORDER BY time_since_update DESC;

Package Inventory

Unnest the package_facts JSONB object so each package name becomes its own row. This makes it straightforward to search for a specific package across all hosts:

CREATE OR REPLACE VIEW package_inventory AS
SELECT hf.client_address
     , hf.system_facts->>'hostname'  AS hostname
     , pkg.key                       AS package_name
     , pkg.value                     AS package_versions
FROM fact_inventory hf,
     LATERAL jsonb_each(hf.package_facts) AS pkg(key, value);
-- Find every host with openssl installed
SELECT client_address
     , hostname
     , package_versions
  FROM package_inventory
 WHERE package_name = 'openssl';

-- Count how many hosts have a given package
SELECT package_name, count(*) AS host_count
  FROM package_inventory
 GROUP BY package_name
 ORDER BY host_count DESC
 LIMIT 20;

Distribution Summary

Aggregate view showing how many hosts run each OS distribution and version:

CREATE OR REPLACE VIEW distribution_summary AS
SELECT system_facts->>'distribution'                AS distribution
     , system_facts->>'distribution_version'        AS version
     , system_facts->>'distribution_major_version'  AS major_version
     , count(*)                                     AS host_count
FROM fact_inventory
GROUP BY system_facts->>'distribution'
       , system_facts->>'distribution_version'
       , system_facts->>'distribution_major_version';
SELECT * FROM distribution_summary ORDER BY host_count DESC;

Network Addresses

If the Ansible setup module collects network facts, you can extract interface details:

CREATE OR REPLACE VIEW host_network AS
SELECT client_address
     , system_facts->>'hostname'                   AS hostname
     , system_facts->'default_ipv4'->>'address'    AS default_ipv4
     , system_facts->'default_ipv6'->>'address'    AS default_ipv6
     , system_facts->'default_ipv4'->>'interface'  AS ipv4_interface
     , system_facts->'default_ipv6'->>'interface'  AS ipv6_interface
FROM fact_inventory;
-- Find hosts on a specific subnet
SELECT client_address
     , hostname
     , default_ipv4
  FROM host_network
 WHERE default_ipv4 IS NOT NULL
   AND default_ipv4::inet <<= '10.0.0.0/8'::inet;