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 WHERE $path='boundary';
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 AND $path='boundary' 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 sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM host_list
LIMIT 1
);
--- 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 'Logical Boundary' as title, logical_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;
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM host_list
LIMIT 1
);
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;
-- Aggressive CSS targeting for SQLPage form horizontal layout
SELECT 'html' AS component,
'<div class="force-horizontal-layout">
<style>
/* Clean form layout without container decoration */
.force-horizontal-layout ~ form,
.force-horizontal-layout + form,
form:has(select[name="tab"]) {
display: flex !important;
flex-direction: row !important;
align-items: flex-end !important;
gap: 15px !important;
flex-wrap: wrap !important;
max-width: 706px !important;
margin-bottom: 20px !important;
background: none !important;
padding: 0 !important;
border-radius: 0 !important;
border: none !important;
}
/* Target all possible container elements */
.force-horizontal-layout ~ form > *,
.force-horizontal-layout + form > *,
form:has(select[name="tab"]) > * {
display: flex !important;
flex-direction: row !important;
align-items: flex-end !important;
gap: 15px !important;
margin: 0 !important;
flex: 0 0 auto !important;
}
/* Target Bootstrap/SQLPage row and col classes */
.force-horizontal-layout ~ form .row,
.force-horizontal-layout + form .row,
.force-horizontal-layout ~ form .col,
.force-horizontal-layout + form .col,
.force-horizontal-layout ~ form .col-12,
.force-horizontal-layout + form .col-12,
form:has(select[name="tab"]) .row,
form:has(select[name="tab"]) .col,
form:has(select[name="tab"]) .col-12 {
display: flex !important;
flex-direction: row !important;
align-items: flex-end !important;
gap: 15px !important;
width: auto !important;
margin: 0 !important;
flex: 0 0 auto !important;
min-width: 500px !important;
}
/* Target form groups */
.force-horizontal-layout ~ form .form-group,
.force-horizontal-layout + form .form-group,
form:has(select[name="tab"]) .form-group {
margin-bottom: 0 !important;
margin-right: 0 !important;
min-width: 730px !important;
flex: 0 0 auto !important;
}
/* Target labels */
.force-horizontal-layout ~ form label,
.force-horizontal-layout + form label,
form:has(select[name="tab"]) label {
margin-bottom: 5px !important;
font-weight: 500 !important;
font-size: 14px !important;
color: #495057 !important;
display: block !important;
width: 100% !important;
}
/* Target select elements */
.force-horizontal-layout ~ form select,
.force-horizontal-layout + form select,
form:has(select[name="tab"]) select {
width: 300px !important;
max-width: 300px !important;
height: 38px !important;
margin-bottom: 0 !important;
flex: 0 0 auto !important;
}
/* Target buttons */
.force-horizontal-layout ~ form button,
.force-horizontal-layout + form button,
form:has(select[name="tab"]) button {
height: 38px !important;
padding: 8px 16px !important;
margin-bottom: 0 !important;
margin-left: 0 !important;
white-space: nowrap !important;
flex: 0 0 auto !important;
align-self: flex-end !important;
}
/* Hide hidden inputs */
.force-horizontal-layout ~ form input[type="hidden"],
.force-horizontal-layout + form input[type="hidden"],
form:has(select[name="tab"]) input[type="hidden"] {
display: none !important;
}
/* Alternative CSS Grid approach */
.force-horizontal-layout ~ form,
.force-horizontal-layout + form,
form:has(select[name="tab"]) {
display: grid !important;
grid-template-columns: 1fr auto !important;
gap: 15px !important;
align-items: end !important;
}
/* Responsive behavior for mobile */
@media (max-width: 768px) {
.force-horizontal-layout ~ form,
.force-horizontal-layout + form,
form:has(select[name="tab"]) {
display: flex !important;
flex-direction: column !important;
align-items: stretch !important;
grid-template-columns: none !important;
}
.force-horizontal-layout ~ form .form-group,
.force-horizontal-layout + form .form-group,
form:has(select[name="tab"]) .form-group {
min-width: 100% !important;
margin-bottom: 10px !important;
}
.force-horizontal-layout ~ form select,
.force-horizontal-layout + form select,
form:has(select[name="tab"]) select {
width: 100% !important;
}
}
</style>
</div>' AS html;
-- Searchable SQLPage form component with horizontal layout
SELECT 'form' AS component, 'GET' AS method;
-- Hidden field to preserve host_identifier
SELECT 'hidden' AS type, 'host_identifier' AS name, $host_identifier AS value;
-- Searchable dropdown select field with all view options
-- Show all dropdown options, not just 4
WITH options AS (
SELECT '{"value":"policies","label":"Policies"}' AS option
UNION ALL SELECT '{"value":"software","label":"Software"}'
UNION ALL SELECT '{"value":"users","label":"Users"}'
UNION ALL SELECT '{"value":"container","label":"Containers"}'
UNION ALL SELECT '{"value":"all_process","label":"All Process"}'
UNION ALL SELECT '{"value":"asset_service","label":"Asset Service"}'
UNION ALL SELECT '{"value":"ssl_tls_is_enabled","label":"SSL/TLS is enabled"}'
UNION ALL SELECT '{"value":"osquery_ssl_cert_files","label":"SSL Certificate Files"}'
UNION ALL SELECT '{"value":"ssl_certificate_and_key_file_modification_times","label":"SSL Certificate and Key File Modification Times"}'
UNION ALL SELECT '{"value":"vpn_listening_ports","label":"VPN Listening Ports"}'
UNION ALL SELECT '{"value":"cron_backup_jobs","label":"Cron Jobs Related to Backup Tasks"}'
UNION ALL SELECT '{"value":"mysql_process_inventory","label":"MySQL Process Inventory"}'
UNION ALL SELECT '{"value":"postgresql_process_inventory","label":"PostgreSQL Process Inventory"}'
)
SELECT
'select' AS type,
'tab' AS name,
'' AS label,
COALESCE($tab, 'policies') AS value,
'Search views...' AS placeholder,
TRUE AS searchable,
'onchange="this.form.submit()"' AS attributes,
'[' || GROUP_CONCAT(option, ',') || ']' AS options
FROM options;
-- Dynamic title display based on selected view
SELECT 'title' AS component,
CASE
WHEN $tab = 'policies' OR $tab IS NULL THEN 'Policies'
WHEN $tab = 'software' THEN 'Software'
WHEN $tab = 'users' THEN 'Users'
WHEN $tab = 'container' THEN 'Containers'
WHEN $tab = 'all_process' THEN 'All Process'
WHEN $tab = 'asset_service' THEN 'Asset Service'
WHEN $tab = 'ssl_tls_is_enabled' THEN 'SSL/TLS is enabled'
WHEN $tab = 'osquery_ssl_cert_files' THEN 'SSL Certificate Files'
WHEN $tab = 'ssl_certificate_and_key_file_modification_times' THEN 'SSL Certificate and Key File Modification Times'
WHEN $tab = 'vpn_listening_ports' THEN 'VPN Listening Ports'
WHEN $tab = 'cron_backup_jobs' THEN 'Cron Jobs Related to Backup Tasks'
WHEN $tab = 'mysql_process_inventory' THEN 'MySQL Process Inventory'
WHEN $tab = 'postgresql_process_inventory' THEN 'PostgreSQL Process Inventory'
ELSE 'Policies'
END AS contents;
-- policy table and tab value Start here
select
'text' as component,
'Displays security policies and compliance rules configured on the system, including policy names, descriptions, and enforcement status. Useful for auditing security configurations and ensuring compliance requirements are met.' as contents WHERE $tab = 'policies';
-- policy pagenation
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source : <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM asset_policy_list WHERE host_identifier = $host_identifier LIMIT 1
) WHERE $tab = 'policies';
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 CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='policies';
;;
SELECT
'html' AS component,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>osquery</strong>
</div>' AS html
WHERE $tab = 'software';
-- Software table and tab value Start here
select
'text' as component,
'Displays installed software packages and applications on the system, including names, versions, types, and platforms. Essential for software inventory management, vulnerability assessment, and license compliance tracking.' as contents WHERE $tab = 'software';
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 CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='software';
;;
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM asset_user_list
LIMIT 1
) WHERE $tab = 'users';
-- User table and tab value Start here
select
'text' as component,
'Displays user accounts configured on the system, including usernames and home directories. Useful for user access auditing, account management, and security compliance verification.' as contents WHERE $tab = 'users';
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 CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='users';
;;
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_docker_container
LIMIT 1
) WHERE $tab = 'all_process';
-- Container table and tab value Start here
select
'text' as component,
'Displays running containers and their configurations, including container names, images, port mappings, IP addresses, and status information. Essential for container orchestration monitoring and security assessment.' as contents WHERE $tab = 'container';
-- 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 CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='container';
;;
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_container_process
LIMIT 1
) WHERE $tab = 'all_process';
-- all_process table and tab value Start here
select
'text' as component,
'Displays all active processes running on the system, including process names, start times, states, and descriptions. Critical for system monitoring, performance analysis, and security incident investigation.' as contents WHERE $tab = 'all_process';
-- all_process pagenation
SET total_rows = (SELECT COUNT(*) FROM 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",start_time as "start time", state, state_description as "state description"
FROM list_container_process
WHERE host_identifier = $host_identifier AND $tab = 'all_process'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='all_process';
;;
-- asset_service table and tab value Start here
select
'text' as component,
'Displays network services and assets running on the system, including service names, ports, protocols, and operational status. Valuable for network security assessment and service inventory management.' as contents WHERE $tab = 'asset_service';
-- asset_service pagenation
-- Display sourse lable of data
SELECT
'html' AS component,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong> Logical Data</strong>
</div>' AS html
WHERE $tab = 'asset_service';
SET total_rows = (SELECT COUNT(*) FROM expected_asset_service_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 = 'asset_service';
SELECT name AS "service",
server,asset_type as "asset type",boundary, description, port,
installation_date as "installation date"
FROM expected_asset_service_list
WHERE host_identifier = $host_identifier AND $tab = 'asset_service'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='asset_service';
;;
-- ssl_tls_is_enabled table and tab value Start here
-- ssl_tls_is_enabled pagenation
SET total_rows = (SELECT COUNT(*) FROM list_ports_443 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
'text' as component,
'This view shows all services listening on port 443 (default for HTTPS), allowing you to verify if SSL/TLS is enabled on your server.' as contents WHERE $tab = 'ssl_tls_is_enabled';
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_ports_443
LIMIT 1
) WHERE $tab = 'ssl_tls_is_enabled';
SELECT 'table' AS component, TRUE as sort, TRUE as search WHERE $tab = 'ssl_tls_is_enabled';
SELECT
address,family,fd, net_namespace,path, port,
protocol,socket
FROM list_ports_443
WHERE host_identifier = $host_identifier AND $tab = 'ssl_tls_is_enabled'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='ssl_tls_is_enabled';
;;
-- osquery_ssl_cert_files table and tab value Start here
select
'text' as component,
'This table displays metadata for files and directories under /etc/ssl/certs and /etc/ssl/private. It helps verify SSL certificate file ownership, permissions, and structural integrity across Linux systems. Use this to detect unauthorized changes or misconfigurations in certificate storage paths.' as contents WHERE $tab = 'osquery_ssl_cert_files';
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_ssl_cert_files
LIMIT 1
) WHERE $tab = 'osquery_ssl_cert_files';
-- osquery_ssl_cert_files pagenation
SET total_rows = (SELECT COUNT(*) FROM list_ssl_cert_files 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 = 'osquery_ssl_cert_files';
SELECT
path as "Full Path",
directory as "Parent Directory",
filename as "File/Directory Name",
inode,
user_name as user,
gid,
mode as Permissions,
device,
size,
block_size as "Block Size",
hard_links as "Hard Links",
type
FROM list_ssl_cert_files
WHERE host_identifier = $host_identifier AND $tab = 'osquery_ssl_cert_files'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='osquery_ssl_cert_files';
;;
-- ssl_certificate_and_key_file_modification_times table and tab value Start here
select
'text' as component,
'Displays the modification timestamps (mtime) of SSL certificate and private key files on Linux systems to monitor unauthorized or unexpected changes.' as contents WHERE $tab = 'ssl_certificate_and_key_file_modification_times';
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_ssl_cert_file_mtime
LIMIT 1
) WHERE $tab = 'ssl_certificate_and_key_file_modification_times';
-- ssl_certificate_and_key_file_modification_times pagenation
SET total_rows = (SELECT COUNT(*) FROM list_ssl_cert_file_mtime 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 = 'ssl_certificate_and_key_file_modification_times';
SELECT
path as "Path",
mtime as "Modified Time (mtime)"
FROM list_ssl_cert_file_mtime
WHERE host_identifier = $host_identifier AND $tab = 'ssl_certificate_and_key_file_modification_times'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='ssl_certificate_and_key_file_modification_times';
;;
-- ssl_certificate_and_key_file_modification_times table and tab value Start here
select
'text' as component,
'Displays information about system ports commonly used by VPN services (e.g., 1194, 443, 500, 4500), including protocol, address, file descriptor, and socket details. Useful for validating VPN service bindings and potential security exposure.' as contents WHERE $tab = 'vpn_listening_ports';
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_vpn_listening_ports
LIMIT 1
) WHERE $tab = 'vpn_listening_ports';
-- vpn_listening_ports pagenation
SET total_rows = (SELECT COUNT(*) FROM list_vpn_listening_ports 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 = 'vpn_listening_ports';
SELECT
port,
protocol,
family,
address,
fd,
socket,
path,
net_namespace as "Net Namespace"
FROM list_vpn_listening_ports
WHERE host_identifier = $host_identifier AND $tab = 'vpn_listening_ports'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='vpn_listening_ports';
;;
-- list_cron_backup_jobs table and tab value Start here
select
'text' as component,
'Displays scheduled cron jobs that include the keyword "backup" in their command. Useful for auditing automated backup routines and ensuring critical backup scripts are scheduled properly.' as contents WHERE $tab = 'cron_backup_jobs';
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_cron_backup_jobs
LIMIT 1
) WHERE $tab = 'vpn_listening_ports';
-- cron_backup_jobs pagenation
SET total_rows = (SELECT COUNT(*) FROM list_cron_backup_jobs 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 = 'cron_backup_jobs';
SELECT
command,
event,
minute,
hour,
day_of_month,
month,
day_of_week,
path,
cron_schedule as "cron schedule",
human_readable_schedule as "human readable schedule"
FROM list_cron_backup_jobs
WHERE host_identifier = $host_identifier AND $tab = 'cron_backup_jobs'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='cron_backup_jobs';
;;
-- mysql_process_inventory table and tab value Start here
select
'text' as component,
'Displays active mysql-related processes running on linux systems, including process name and binary path. useful for inventory and service validation.' as contents WHERE $tab = 'mysql_process_inventory';
-- Display sourse lable of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_mysql_process_inventory
LIMIT 1
) WHERE $tab = 'vpn_listening_ports';
-- mysql_process_inventory pagenation
SET total_rows = (SELECT COUNT(*) FROM list_mysql_process_inventory 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 = 'mysql_process_inventory';
SELECT
process_name as "process name",
process_path as "process path"
FROM list_mysql_process_inventory
WHERE host_identifier = $host_identifier AND $tab = 'mysql_process_inventory'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='mysql_process_inventory';
;;
-- postgresql_process_inventory table and tab value Start here
select
'text' as component,
'Displays active PostgreSQL-related processes running on Linux systems, including process name and binary path. Useful for database inventory and service validation.' as contents WHERE $tab = 'postgresql_process_inventory';
-- Display source label of data
SELECT
'html' AS component,
contents,
'<div style="width: 100%; padding-top: 20px; text-align: right; font-size: 14px; color: #666;">
Source: <strong>' || contents || '</strong>
</div>' AS html
FROM (
SELECT
query_uri,
CASE
WHEN query_uri LIKE '%osquery%' THEN 'osquery'
WHEN query_uri LIKE '%Steampipe%' THEN 'Steampipe'
ELSE 'Other'
END AS contents
FROM list_postgresql_process_inventory
LIMIT 1
) WHERE $tab = 'postgresql_process_inventory';
-- postgresql_process_inventory pagination
SET total_rows = (SELECT COUNT(*) FROM list_postgresql_process_inventory 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 = 'postgresql_process_inventory';
SELECT
process_name as "Process Name",
process_path as "Process Path"
FROM list_postgresql_process_inventory
WHERE host_identifier = $host_identifier AND $tab = 'postgresql_process_inventory'
LIMIT $limit OFFSET $offset;
SELECT 'text' AS component,
(SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
|| ' '
|| '(Page ' || $current_page || ' of ' || $total_pages || ") "
|| (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || '&tab=' || replace($tab, ' ', '%20') || '&host_identifier=' || replace($host_identifier, ' ', '%20') || ')' ELSE '' END)
AS contents_md
WHERE $tab='postgresql_process_inventory';
;