fleetfolio/host_detail.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 'title' AS component, (SELECT COALESCE(title, caption)
    FROM sqlpage_aide_navigation
   WHERE namespace = 'prime' AND path = 'fleetfolio/host_detail.sql/index.sql') as contents;
    ;
  --- Display breadcrumb
  SELECT
      'breadcrumb' AS component;
  SELECT
      'Home' AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/'    AS link;
  SELECT
      'FleetFolio' AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/index.sql' AS link;  
  SELECT
      'Boundary' AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/boundary.sql' AS link; 
  SELECT boundary AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/host_list.sql?boundary_key=' || boundary_key  AS link
      FROM host_list WHERE host_identifier=$host_identifier LIMIT 1;
  SELECT host AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/fleetfolio/host_detail.sql?host_identifier=' || host_identifier  AS link
      FROM host_list WHERE host_identifier=$host_identifier LIMIT 1;


  --- Dsply Page Title
  SELECT
      'title'   as component,
      host as contents FROM host_list WHERE host_identifier=$host_identifier;

  SELECT
      'text'              as component,
      description as contents FROM host_list WHERE host_identifier=$host_identifier;
  --- Display Asset (Host) Details first row
  SELECT 'datagrid' as component;
      -- SELECT 'Parent Boundary' as title, parent_boundary as description FROM host_list WHERE asset_id=$host_identifier;
      SELECT 'Boundary' as title, boundary as description FROM host_list WHERE host_identifier=$host_identifier;
      SELECT 'Status' as title,
      CASE 
          WHEN status = 'Online' THEN '🟢 Online'
          WHEN status = 'Offline' THEN '🔴 Offline'
          ELSE '⚠️ Unknown'
      END AS  description FROM host_list WHERE host_identifier=$host_identifier;
      SELECT 'Issues' as title, issues as description FROM host_list WHERE host_identifier=$host_identifier;
      SELECT 'Osquery version' as title, osquery_version as description FROM host_list WHERE host_identifier=$host_identifier;
      SELECT 'Operating system' as title, operating_system as description FROM host_list WHERE host_identifier=$host_identifier;

      select 
          'html' as component,
          '<div style="display: flex; gap: 20px; width: 100%;">
              <!-- First Column -->
              <div style="display: flex; flex-direction: column; gap: 8px; padding: 12px; border: .5px solid #ccc;  border-radius: 4px; width: 33%; background-color: #ffffff;">
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Disk space</div>
                      <div>' || available_space || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Memory</div>
                      <div>' || ROUND(physical_memory / (1024 * 1024 * 1024), 2) || ' GB' || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Processor Type</div>
                      <div>' || cpu_type || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px;">
                      <div class="datagrid-title">Added to surveilr</div>
                      <div>' || added_to_surveilr_osquery_ms || '</div>
                  </div>
              </div> 

              <!-- Second Column -->
              <div style="display: flex; flex-direction: column; gap: 8px; padding: 12px; border: .5px solid #ccc; border-radius: 4px; width: 33%; background-color: #ffffff;">
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Hardware Model</div>
                      <div>' || hardware_model || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Board Model</div>
                      <div>' || board_model || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Serial Number</div>
                      <div>' || hardware_serial || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px;">
                      <div class="datagrid-title">Last restarted</div>
                      <div>' || last_restarted || '</div>
                  </div>
              </div> 

              <!-- Third Column -->
              <div style="display: flex; flex-direction: column; gap: 8px; padding: 12px; border: .5px solid #ccc; border-radius: 4px; width: 33%; background-color: #ffffff;">
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">IP Address</div>
                      <div>' || ip_address || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px; border-bottom: 1px solid #eee;">
                      <div class="datagrid-title">Mac Address</div>
                      <div>' || mac || '</div>
                  </div>
                  <div style="display: flex; justify-content: space-between; padding: 4px;">
                      <div class="datagrid-title">Last Fetched</div>
                      <div>' || last_fetched || '</div>
                  </div>
              </div>
          </div>

      ' as html FROM host_list WHERE host_identifier=$host_identifier;

  select 
  'divider' as component,
  'System Environment'   as contents;

  SELECT 'tab' AS component, TRUE AS center;
  SELECT 'Policies' AS title, '?tab=policies&host_identifier=' || $host_identifier AS link, ($tab = 'policies' OR $tab IS NULL) AS active;
  select 'Software' as title, '?tab=software&host_identifier=' || $host_identifier AS link, $tab = 'software' as active;
  select 'Users' as title, '?tab=users&host_identifier=' || $host_identifier AS link, $tab = 'users' as active;
  select 'Containers' as title, '?tab=container&host_identifier=' || $host_identifier AS link, $tab = 'container' as active;
  select 'All Process' as title, '?tab=all_process&host_identifier=' || $host_identifier AS link, $tab = 'all_process' as active;

  -- policy table and tab value Start here
  -- policy pagenation
  SET total_rows = (SELECT COUNT(*) FROM asset_policy_list WHERE host_identifier=$host_identifier);
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 'table' AS component, TRUE as sort, TRUE as search WHERE ($tab = 'policies' OR $tab IS NULL);
  SELECT 
  policy_name AS "Policy", policy_result as "Status", resolution
  FROM asset_policy_list
  WHERE host_identifier = $host_identifier AND ($tab = 'policies' OR $tab IS NULL) LIMIT $limit
  OFFSET $offset;
  -- checking
  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='policies';;

  -- Software table and tab value Start here
 
  SET total_rows = (SELECT COUNT(*) FROM asset_software_list WHERE host_identifier=$host_identifier);
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 'table' AS component, TRUE as sort, TRUE as search WHERE $tab = 'software';
  SELECT name, version, type, platform, '-' AS "Vulnerabilities"
  FROM asset_software_list
  WHERE host_identifier = $host_identifier AND $tab = 'software'
  LIMIT $limit OFFSET $offset;

  -- Software pagenation
  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='software';;

  -- User table and tab value Start here
  SET total_rows = (SELECT COUNT(*) FROM asset_user_list WHERE host_identifier=$host_identifier);
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 'table' AS component, TRUE as sort, TRUE as search WHERE $tab = 'users';
  SELECT user_name as "User Name", directory as "Directory"
  FROM asset_user_list
  WHERE host_identifier = $host_identifier AND $tab = 'users'
  LIMIT $limit OFFSET $offset;

  -- User pagenation
  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='users';;

-- Container table and tab value Start here
-- Container pagenation
SET total_rows = (SELECT COUNT(*) FROM list_docker_container WHERE host_identifier=$host_identifier);
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 'table' AS component, TRUE as sort, TRUE as search,TRUE    as hover
   WHERE $tab = 'container';
  SELECT LTRIM(container_name, '/') AS name, image,host_port AS "host Port",
  port, ip_address as "IP Address", owenrship, process, state, status,created_date as created
  FROM list_docker_container
  WHERE host_identifier = $host_identifier AND $tab = 'container'
  LIMIT $limit OFFSET $offset;
  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='container';;


  -- all_process table and tab value Start here
  -- all_process pagenation
  SET total_rows = (SELECT COUNT(*) FROM ur_transform_list_container_process WHERE host_identifier=$host_identifier);
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 'table' AS component, TRUE as sort, TRUE as search WHERE $tab = 'all_process';
  SELECT process_name AS "process name"
  FROM ur_transform_list_container_process
  WHERE host_identifier = $host_identifier AND $tab = 'all_process'
  LIMIT $limit OFFSET $offset;
  SELECT 'text' AS component,
    (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) ||  '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||   ')' ELSE '' END) || ' ' ||
    '(Page ' || $current_page || ' of ' || $total_pages || ") " ||
    (SELECT CASE WHEN $current_page < $total_pages THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) ||   '&tab=' || $tab ||
'&host_identifier=' || $host_identifier ||  ')' ELSE '' END)
    AS contents_md 
 WHERE $tab='all_process';