host_list

Column Type
description
surveilr_osquery_ms_node_id VARCHAR
boundary BLOB
boundary_key BLOB
host BLOB
node_key TEXT
host_identifier TEXT
osquery_version TEXT
last_seen TIMESTAMP
created_at TIMESTAMPTZ
updated_at NUM
ip_address BLOB
mac BLOB
added_to_surveilr_osquery_ms
operating_system BLOB
available_space
status
last_fetched
last_restarted
issues
board_model BLOB
board_serial BLOB
board_vendor BLOB
board_version BLOB
computer_name BLOB
cpu_brand BLOB
cpu_logical_cores BLOB
cpu_microcode BLOB
cpu_physical_cores BLOB
cpu_sockets BLOB
cpu_subtype BLOB
cpu_type BLOB
hardware_model BLOB
hardware_serial BLOB
hardware_vendor BLOB
hardware_version BLOB
local_hostname BLOB
physical_memory BLOB
uuid BLOB
query_uri TEXT
logical_boundary

SQL DDL

CREATE VIEW host_list AS
SELECT 
    "" as description,
    nodeDet.surveilr_osquery_ms_node_id,
    boundary.boundary as boundary,
    boundary.boundary as boundary_key,
    boundary.host_identifier as host,
    nodeDet.node_key,
    nodeDet.host_identifier,
    nodeDet.osquery_version,
    nodeDet.last_seen,
    nodeDet.created_at,
    nodeDet.updated_at,
    nodeDet.ip_address,
    nodeDet.mac,
    nodeDet.added_to_surveilr_osquery_ms,
    nodeDet.operating_system,
    nodeDet.available_space,
    nodeDet.node_status as status,
    nodeDet.last_fetched,
    nodeDet.last_restarted,
    nodeDet.issues,
    sysinfo.board_model,
    sysinfo.board_serial,
    sysinfo.board_vendor,
    sysinfo.board_version,
    sysinfo.computer_name,
    sysinfo.cpu_brand,
    sysinfo.cpu_logical_cores,
    sysinfo.cpu_microcode,
    sysinfo.cpu_physical_cores,
    sysinfo.cpu_sockets,
    sysinfo.cpu_subtype,
    sysinfo.cpu_type,
    sysinfo.hardware_model,
    sysinfo.hardware_serial,
    sysinfo.hardware_vendor,
    sysinfo.hardware_version,
    sysinfo.local_hostname,
    sysinfo.physical_memory,
    sysinfo.uuid,
    boundary.query_uri,
    eal.boundaries as logical_boundary
FROM surveilr_osquery_ms_node_boundary boundary
LEFT JOIN surveilr_osquery_ms_node_detail nodeDet ON nodeDet.host_identifier=boundary.host_identifier
LEFT JOIN surveilr_osquery_ms_node_system_info sysinfo ON sysinfo.host_identifier=boundary.host_identifier
LEFT JOIN expected_asset_list eal ON nodeDet.host_identifier= eal.host
;