expected_asset_service_list

Column Type
name TEXT
server TEXT
host_identifier TEXT
organization_id TEXT
asset_type TEXT
asset_service_type_id TEXT
boundary
description TEXT
port TEXT
experimental_version TEXT
production_version TEXT
latest_vendor_version TEXT
resource_utilization TEXT
log_file TEXT
url TEXT
vendor_link TEXT
installation_date DATE
criticality TEXT
owner TEXT
tag TEXT
asset_criticality TEXT
asymmetric_keys TEXT
cryptographic_key TEXT
symmetric_keys TEXT

SQL DDL

CREATE VIEW expected_asset_service_list AS
SELECT
  asser.name,
  ast.name AS server,
  ast.name AS host_identifier,
  ast.organization_id,
  astyp.value AS asset_type,
  astyp.asset_service_type_id,
  GROUP_CONCAT(bnt.name, ', ') AS boundary,
  asser.description,
  asser.port,
  asser.experimental_version,
  asser.production_version,
  asser.latest_vendor_version,
  asser.resource_utilization,
  asser.log_file,
  asser.url,
  asser.vendor_link,
  asser.installation_date,
  asser.criticality,
  o.name AS owner,
  sta.value AS tag,
  ast.criticality AS asset_criticality,
  ast.asymmetric_keys_encryption_enabled AS asymmetric_keys,
  ast.cryptographic_key_encryption_enabled AS cryptographic_key,
  ast.symmetric_keys_encryption_enabled AS symmetric_keys
FROM asset_service asser
INNER JOIN asset_service_type astyp ON astyp.asset_service_type_id = asser.asset_service_type_id
INNER JOIN asset ast ON ast.asset_id = asser.asset_id
INNER JOIN organization o ON o.organization_id = ast.organization_id
INNER JOIN asset_status sta ON sta.asset_status_id = ast.asset_status_id
INNER JOIN asset_boundary ab ON ab.asset_id = ast.asset_id
INNER JOIN boundary bnt ON bnt.boundary_id = ab.boundary_id
GROUP BY asser.asset_service_id
;