Asset Management - Asset Discovery Coverage

Description

Using our scanning tools, we identify systems that may exist in the environment that may not have been recorded in the asset management system.

How we measure it

Find all computers seen in the last 30 days by CrowdStrike (excluding cloud instances and Linux servers), Kandji or Workspace ONE, and match them by serial number to the Salesforce asset register. Computers are considered compliant if their serial number is recorded in the register.

Meta Data

Attribute	Value
Metric id	am_known_assets
Category	Asset Management
SLO	90.00% - 95.00%
Weight	0.5
Type

References

Framework	Ref	Domain	Control
ISO 27001:2022	A.5.9	5 Organizational controls	Inventory of information and other associated assets
CIS 8.1	1.1	Inventory and Control of Enterprise Assets	Establish and Maintain Detailed Enterprise Asset Inventory
NIST CSF v2.0	ID.AM-01	Asset Management (ID.AM)	ID.AM-01: Inventories of hardware managed by the organization are maintained
Essential8-ML1	ISM-1807	Patch applications	An automated method of asset discovery is used at least fortnightly to support the detection of assets for subsequent vulnerability scanning activities.
Essential8-ML2	ISM-1807	Patch applications	An automated method of asset discovery is used at least fortnightly to support the detection of assets for subsequent vulnerability scanning activities.
Essential8-ML3	ISM-1807	Patch applications	An automated method of asset discovery is used at least fortnightly to support the detection of assets for subsequent vulnerability scanning activities.
Essential8-ML1	ISM-1807	Patch operating systems	An automated method of asset discovery is used at least fortnightly to support the detection of assets for subsequent vulnerability scanning activities.
Essential8-ML2	ISM-1807	Patch operating systems	An automated method of asset discovery is used at least fortnightly to support the detection of assets for subsequent vulnerability scanning activities.
Essential8-ML3	ISM-1807	Patch operating systems	An automated method of asset discovery is used at least fortnightly to support the detection of assets for subsequent vulnerability scanning activities.

Code

WITH discovered AS (
  SELECT upper(serial_number) AS serial, hostname AS name, 'CrowdStrike' AS source
  FROM {{ ref('crowdstrike_hosts') }}
  WHERE CURRENT_DATE - CAST(last_seen AS DATE) <= 30
    AND platform_name IN ('Mac', 'Windows')
    AND cloud_provider IS NULL
  UNION ALL
  SELECT upper(serial_number), device_name, 'Kandji'
  FROM {{ ref('kandji_devices') }}
  WHERE NOT is_missing AND NOT is_removed
    AND CURRENT_DATE - CAST(last_check_in AS DATE) <= 30
  UNION ALL
  SELECT upper(serial_number), device_friendly_name, 'Workspace ONE'
  FROM {{ ref('workspaceone_computers') }}
  WHERE enrollment_status = 'ENROLLED'
    AND CURRENT_DATE - CAST(last_seen AS DATE) <= 30
),
systems AS (
  SELECT serial, any_value(name) AS name, string_agg(DISTINCT source, ', ') AS sources
  FROM discovered
  WHERE serial IS NOT NULL AND serial <> ''
  GROUP BY serial
),
register AS (
  SELECT upper(serial_number__c) AS serial, any_value(status__c) AS status
  FROM {{ ref('salesforce_fixed_asset__c') }}
  WHERE serial_number__c IS NOT NULL
  GROUP BY 1
)
SELECT
  systems.name || ' (' || systems.serial || ')' AS resource,
  'host' AS resource_type,
  CASE WHEN register.serial IS NULL THEN 0 ELSE 1 END AS compliance,
  CASE
    WHEN register.serial IS NULL THEN 'Not in the asset register (serial ' || systems.serial || ', seen by ' || systems.sources || ')'
    ELSE 'In the asset register (status: ' || coalesce(register.status, 'none') || ')'
  END AS detail
FROM systems
LEFT JOIN register ON register.serial = systems.serial