ms/node.sql

              SELECT 'dynamic' AS component, sqlpage.run_sql('shell/shell.sql') AS properties;
              -- not including breadcrumbs from sqlpage_aide_navigation
              -- not including page title from sqlpage_aide_navigation
              

              SELECT 'breadcrumb' as component;
WITH RECURSIVE breadcrumbs AS (
    SELECT
        COALESCE(abbreviated_caption, caption) AS title,
        COALESCE(url, path) AS link,
        parent_path, 0 AS level,
        namespace
    FROM sqlpage_aide_navigation
    WHERE namespace = 'prime' AND path='ms/index.sql'
    UNION ALL
    SELECT
        COALESCE(nav.abbreviated_caption, nav.caption) AS title,
        COALESCE(nav.url, nav.path) AS link,
        nav.parent_path, b.level + 1, nav.namespace
    FROM sqlpage_aide_navigation nav
    INNER JOIN breadcrumbs b ON nav.namespace = b.namespace AND nav.path = b.parent_path
)
SELECT title ,      
sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/'||link as link        
FROM breadcrumbs ORDER BY level DESC;
SELECT $host_id || ' Node' AS title, '#' AS link;
  
-- sets up $limit, $offset, and other variables (use pagination.debugVars() to see values in web-ui)
SET total_rows = (SELECT COUNT(*) FROM surveilr_osquery_ms_node_installed_software WHERE node_key = $key AND $tab = 'software');
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1;
  
  SELECT 'title' as component, $host_id as contents, 2  as level;
  SELECT 'text' as component, 'Last fetched ' || "last_fetched" as contents, 1 as size FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  
  SELECT 'datagrid' as component;
  SELECT 'Status' as title, "node_status" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Issues' as title, "issues" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Disk space' as title, "available_space" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Memory' as title, ROUND("physical_memory" / (1024 * 1024 * 1024), 2) || ' GB' AS description FROM surveilr_osquery_ms_node_system_info WHERE node_key = $key;
  SELECT 'Processor Type' as title, "cpu_type" as description FROM surveilr_osquery_ms_node_system_info WHERE node_key = $key;
  SELECT 'Operating system' as title, "operating_system" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'osQuery' as title, "osquery_version" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Boundary' as title, "boundary" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  
  SELECT 'datagrid' as component;
  SELECT 'Added to surveilr' as title, "added_to_surveilr_osquery_ms" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Last Restarted' as title, "last_restarted" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Hardware Model' as title, "hardware_model" as description FROM surveilr_osquery_ms_node_system_info WHERE node_key = $key;
  SELECT 'Serial Number' as title, "hardware_serial" as description FROM surveilr_osquery_ms_node_system_info WHERE node_key = $key;
  SELECT 'IP Address' as title, "ip_address" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
  SELECT 'Team' as title, "-" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key;
    
  -- Define tabs
  SELECT 'tab' AS component, TRUE AS center;
  
  -- Tab 1: Software
  SELECT 'Software' AS title, '?tab=software&key=' || $key || '&host_id=' || $host_id AS link, $tab = 'software' AS active;
  
  -- Tab 2: Software
  SELECT 'Policies' AS title, '?tab=policies&key=' || $key || '&host_id=' || $host_id AS link, $tab = 'policies' AS active;
  
 
  -- SELECT 'IP Address' as title, "ip_address" as description FROM surveilr_osquery_ms_node_detail WHERE node_key = $key AND ($tab = 'details' OR $tab IS NULL);
  
  -- Tab specific content for Software
  select 'text' as component, 'Software' as title WHERE $tab = 'software';
  
  SELECT 'table' AS component, TRUE as sort, TRUE as search WHERE $tab = 'software';
  SELECT name, version, type, platform, '-' AS "Vulnerabilities"
    FROM surveilr_osquery_ms_node_installed_software
    WHERE node_key = $key AND $tab = 'software'
    LIMIT $limit OFFSET $offset;
  
  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&key=' || $key ||
'&host_id=' || $host_id ||
'&tab=' || $tab ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&key=' || $key ||
'&host_id=' || $host_id ||
'&tab=' || $tab ||  ')' ELSE '' END)
    AS contents_md 
;;
  
  -- Tab specific content for Policies
  select 'text' as component, 'Policies' as title WHERE $tab = 'software';
  SELECT 'table' AS component, TRUE as sort, TRUE as search WHERE $tab = 'policies';
  SELECT policy_name AS "Policy", policy_result as "Status", resolution
    FROM surveilr_osquery_ms_node_executed_policy
    WHERE node_key = $key AND $tab = 'policies';