logical_boundary_asset_count_list

Column Type
boundary_name TEXT
host_count

SQL DDL

CREATE VIEW logical_boundary_asset_count_list AS
SELECT 
    bnt.name AS boundary_name,
    COUNT(ast_bnt.asset_id) AS host_count
FROM boundary bnt 
INNER JOIN asset_boundary ast_bnt ON ast_bnt.boundary_id = bnt.boundary_id
INNER JOIN asset ON asset.asset_id = ast_bnt.asset_id
INNER JOIN surveilr_osquery_ms_node_detail node ON node.host_identifier=asset.name
WHERE bnt.parent_boundary_id IS NOT NULL
GROUP BY bnt.name
ORDER BY host_count DESC
;