### Get Asset Summary with Risk

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/README.md

Lists top assets by risk score including vulnerability counts and OS information.

```sql
SELECT a.host_name, a.ip_address, a.os_description,
       f.vulnerabilities, f.critical_vulnerabilities, f.risk_score
FROM fact_asset f
JOIN dim_asset a ON f.asset_id = a.asset_id
ORDER BY f.risk_score DESC
LIMIT 50
```

--------------------------------

### Query vulnerability references

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-vulnerability.md

Examples for retrieving specific CVE identifiers or filtering vendor advisories by vulnerability severity.

```sql
SELECT reference_value, url
FROM dim_vulnerability_reference
WHERE vulnerability_id = 45678
  AND reference_type = 'CVE'
```

```sql
SELECT v.nexpose_id, v.title, r.reference_value, r.url
FROM dim_vulnerability_reference r
JOIN dim_vulnerability v ON r.vulnerability_id = v.vulnerability_id
WHERE r.reference_type = 'VENDOR_ADVISORY'
  AND v.severity = 'Critical'
```

--------------------------------

### Query assets by software vendor and name

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Identifies assets with a specific version of software installed, filtered by vendor and product name.

```sql
SELECT a.asset_id, a.host_name, s.version
FROM dim_asset_software s
JOIN dim_asset a ON s.asset_id = a.asset_id
WHERE s.vendor = 'Apache'
  AND s.name = 'OpenSSL'
ORDER BY s.version
```

--------------------------------

### Querying Time Intervals

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/data-types.md

Examples for filtering by duration and calculating time differences between timestamps.

```sql
SELECT solution_id, nexpose_id, estimate
FROM dim_solution
WHERE estimate < INTERVAL '4 hours'
ORDER BY estimate
```

```sql
SELECT scan_id, scan_name, (finished_date - started_date) as duration
FROM dim_scan
ORDER BY duration DESC
```

--------------------------------

### Querying Network Addresses

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/data-types.md

Examples for filtering by IP address using network operators and string casting.

```sql
SELECT ip_address, host_name
FROM dim_asset
WHERE ip_address << '192.168.1.0/24'::inet
```

```sql
SELECT ip_address::text, host_name
FROM dim_asset
WHERE ip_address::text LIKE '10.0.%'
```

--------------------------------

### Querying Boolean Flags

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/data-types.md

Examples for filtering by boolean status and performing conditional aggregations.

```sql
SELECT asset_id, host_name
FROM dim_asset
WHERE assessed_for_vulnerabilities = true
  AND assessed_for_policies = true
```

```sql
SELECT COUNT(*) as total,
       SUM(CASE WHEN assessed_for_vulnerabilities THEN 1 ELSE 0 END) as scanned,
       SUM(CASE WHEN assessed_for_policies THEN 1 ELSE 0 END) as assessed
FROM dim_asset
```

--------------------------------

### Get policy compliance summary with SQL

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-policy.md

Aggregates compliance metrics for policies, including assessed and compliant asset counts, ordered by compliance percentage.

```sql
SELECT p.title, f.assessed_assets, f.compliant_assets, f.non_compliant_assets,
       ROUND(f.compliance_percentage, 2) as compliance_pct
FROM fact_policy f
JOIN dim_policy p ON f.policy_id = p.policy_id
ORDER BY f.compliance_percentage ASC
```

--------------------------------

### Get metrics for a tag group with SQL

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-aggregate.md

Queries aggregated risk and vulnerability metrics for assets associated with a specific tag name.

```sql
SELECT t.tag_name, f.assets, f.vulnerabilities, f.critical_vulnerabilities,
       ROUND(f.average_risk_score, 2) as avg_risk
FROM fact_tag f
JOIN dim_tag t ON f.tag_id = t.tag_id
WHERE t.tag_name = 'production'
```

--------------------------------

### Find Unpatched Systems by OS

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Aggregates vulnerability counts grouped by operating system details.

```sql
SELECT a.os_family, a.os_name, a.os_version, COUNT(DISTINCT a.asset_id) as asset_count,
       SUM(f.vulnerabilities) as total_vulns,
       SUM(f.critical_vulnerabilities) as critical
FROM fact_asset f
JOIN dim_asset a ON f.asset_id = a.asset_id
WHERE f.vulnerabilities > 0
GROUP BY a.os_family, a.os_name, a.os_version
ORDER BY critical DESC, total_vulns DESC
```

--------------------------------

### Query HTTP services by configuration

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves host names and configuration values for assets running HTTP services on port 80 with SSL enabled.

```sql
SELECT a.host_name, sc.configuration_key, sc.configuration_value
FROM dim_asset_service_configuration sc
JOIN dim_asset a ON sc.asset_id = a.asset_id
WHERE sc.port = 80
  AND sc.configuration_key = 'ssl_enabled'
```

--------------------------------

### Find quick remediation solutions

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Filters for patch-type solutions with an estimated implementation time of less than one hour.

```sql
SELECT solution_id, nexpose_id, estimate, summary
FROM dim_solution
WHERE estimate < INTERVAL '1 hour'
  AND solution_type = 'PATCH'
ORDER BY estimate
```

--------------------------------

### Analyze Risk Distribution by OS

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Aggregates vulnerability and risk metrics grouped by operating system family.

```sql
SELECT a.os_family, 
       SUM(f.vulnerabilities) as vulns,
       SUM(f.critical_vulnerabilities) as critical,
       SUM(f.severe_vulnerabilities) as severe,
       ROUND(AVG(f.risk_score), 2) as avg_risk,
       COUNT(DISTINCT a.asset_id) as asset_count
FROM fact_asset f
JOIN dim_asset a ON f.asset_id = a.asset_id
GROUP BY a.os_family
ORDER BY vulns DESC
```

--------------------------------

### View Documentation Directory Structure

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/DOCUMENTATION_SUMMARY.md

Displays the file hierarchy for the InsightVM DWH documentation project.

```text
/output/
├── README.md                          # Main entry point
├── TABLE_INDEX.md                     # All 76 tables at a glance
├── schema-overview.md                 # Architecture and concepts
├── data-types.md                      # PostgreSQL type reference
├── query-patterns.md                  # 50+ SQL examples
├── system-tables.md                   # Metadata and monitoring
└── api-reference/
    ├── dimension-tables-asset.md      # 16 asset dimensions
    ├── dimension-tables-vulnerability.md  # 7 vulnerability dimensions
    ├── dimension-tables-policy-site-scan.md  # 16 policy/site/scan dimensions
    ├── fact-tables-core.md            # 8 core facts
    ├── fact-tables-policy.md          # 9 policy facts
    └── fact-tables-aggregate.md       # 15 aggregate facts
```

--------------------------------

### Compare security posture across sites

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-aggregate.md

Joins fact_site with dim_site to retrieve and order site-level vulnerability metrics.

```sql
SELECT s.site_name, f.assets, f.vulnerabilities, f.critical_vulnerabilities,
       ROUND(f.average_risk_score, 2) as avg_risk, f.last_scan_date
FROM fact_site f
JOIN dim_site s ON f.site_id = s.site_id
ORDER BY f.critical_vulnerabilities DESC
```

--------------------------------

### Retrieve Assets by Site

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Summarizes vulnerability and risk data for each site, ordered by the number of critical vulnerabilities.

```sql
SELECT s.site_name, sf.assets, sf.vulnerabilities, sf.critical_vulnerabilities,
       ROUND(sf.average_risk_score, 2) as avg_risk, sf.last_scan_date
FROM fact_site sf
JOIN dim_site s ON sf.site_id = s.site_id
ORDER BY sf.critical_vulnerabilities DESC
```

--------------------------------

### Backup Schema Metadata with pg_dump

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/system-tables.md

Use these commands to export schema definitions without data. Ensure you have appropriate database credentials and permissions.

```bash
# Backup all schema definitions
pg_dump --schema-only -h hostname -U username -d database_name > schema_backup.sql

# Backup specific table definitions
pg_dump --schema-only -h hostname -U username -d database_name -t 'fact_*' > facts_backup.sql
pg_dump --schema-only -h hostname -U username -d database_name -t 'dim_*' > dimensions_backup.sql
```

--------------------------------

### Query hostnames for a specific asset

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves all hostnames and their detection sources associated with a specific asset ID.

```sql
SELECT host_name, source
FROM dim_asset_host_name
WHERE asset_id = 12345
```

--------------------------------

### Query assets by tag name

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves asset identifiers and hostnames for assets associated with a specific tag name.

```sql
SELECT a.asset_id, a.host_name
FROM dim_asset_tag at
JOIN dim_asset a ON at.asset_id = a.asset_id
WHERE at.tag_name = 'production'
```

--------------------------------

### Generate Executive Summary

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Provides a high-level overview of the environment, including total scanned assets, vulnerability counts, and average risk scores.

```sql
SELECT 
  (SELECT COUNT(*) FROM dim_asset WHERE assessed_for_vulnerabilities = true) as scanned_assets,
  (SELECT vulnerabilities FROM fact_all) as total_vulnerabilities,
  (SELECT critical_vulnerabilities FROM fact_all) as critical_count,
  (SELECT severe_vulnerabilities FROM fact_all) as severe_count,
  (SELECT pci_failing_assets FROM fact_all) as pci_failing_assets,
  (SELECT ROUND(average_risk_score::numeric, 2) FROM fact_all) as avg_risk_score
```

--------------------------------

### Retrieve latest solution version using SQL

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Joins the supercedence table with the solution dimension to fetch the summary and estimate of the highest superceding version for a specific solution ID.

```sql
SELECT h.solution_id, h.highest_superceding_solution_id,
       s.summary, s.estimate
FROM dim_solution_highest_supercedence h
JOIN dim_solution s ON h.highest_superceding_solution_id = s.solution_id
WHERE h.solution_id = 12345
```

--------------------------------

### Analyze Solution Usage

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Ranks solutions by the number of assets remediated over the last 90 days.

```sql
SELECT s.solution_id, s.nexpose_id, s.summary, s.solution_type,
       COUNT(DISTINCT r.asset_id) as deployments,
       COUNT(DISTINCT r.vulnerability_id) as vulnerabilities_addressed
FROM fact_asset_vulnerability_finding_remediation r
JOIN dim_solution s ON r.solution_id = s.solution_id
WHERE r.remediation_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY s.solution_id, s.nexpose_id, s.summary, s.solution_type
ORDER BY deployments DESC
```

--------------------------------

### Prioritize solutions by finding count

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves solutions for a specific asset, ordered by the number of findings they address.

```sql
SELECT s.solution_id, s.summary, rs.finding_count, s.estimate
FROM dim_asset_vulnerability_finding_rollup_solution rs
JOIN dim_solution s ON rs.solution_id = s.solution_id
WHERE rs.asset_id = 12345
ORDER BY rs.finding_count DESC
```

--------------------------------

### Querying MAC Addresses

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/data-types.md

Filter assets by specific hardware MAC address.

```sql
SELECT asset_id, mac_address, ip_address
FROM dim_asset_address
WHERE mac_address = '00:11:22:33:44:55'
```

--------------------------------

### List all scan templates

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Retrieves the ID, name, port selection, and credential scan status for all defined scan templates, ordered by name.

```sql
SELECT scan_template_id, template_name, port_selection, credential_scan
FROM dim_scan_template
ORDER BY template_name
```

--------------------------------

### Identify Shadow IT (Unscanned Assets)

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Lists assets that are marked as not assessed for vulnerabilities.

```sql
SELECT a.asset_id, a.host_name, a.ip_address, a.os_description
FROM dim_asset a
WHERE a.assessed_for_vulnerabilities = false
ORDER BY a.asset_id
```

--------------------------------

### List policies and benchmark associations

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Retrieves a list of policies joined with their corresponding benchmark names, ordered by benchmark and title.

```sql
SELECT p.policy_id, p.title, b.benchmark_name, p.scored_rules
FROM dim_policy p
JOIN dim_policy_benchmark b ON p.benchmark_id = b.benchmark_id
ORDER BY b.benchmark_name, p.title
```

--------------------------------

### Query assets in a specific site

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Joins dim_site_asset with dim_asset to list all assets associated with a site named 'Production'.

```sql
SELECT a.asset_id, a.host_name, a.ip_address
FROM dim_site_asset sa
JOIN dim_asset a ON sa.asset_id = a.asset_id
WHERE sa.site_id = (SELECT site_id FROM dim_site WHERE site_name = 'Production')
```

--------------------------------

### Find easy-to-exploit vulnerabilities

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-core.md

Identifies critical vulnerabilities that have 'Novice' level exploits available.

```sql
SELECT DISTINCT a.host_name, v.nexpose_id, v.title, e.exploit_skill_level
FROM fact_asset_vulnerability_finding_exploit e
JOIN dim_asset a ON e.asset_id = a.asset_id
JOIN dim_vulnerability v ON e.vulnerability_id = v.vulnerability_id
WHERE e.exploit_skill_level = 'Novice'
  AND v.severity = 'Critical'
```

--------------------------------

### Track vulnerability remediation progress by site

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-aggregate.md

Uses window functions to calculate daily changes in vulnerability counts for a specific site over the last 30 days.

```sql
SELECT s.site_name, d.date, d.vulnerabilities,
       LAG(d.vulnerabilities) OVER (PARTITION BY d.site_id ORDER BY d.date) as previous_day,
       LAG(d.vulnerabilities) OVER (PARTITION BY d.site_id ORDER BY d.date) - d.vulnerabilities as reduction
FROM fact_site_date d
JOIN dim_site s ON d.site_id = s.site_id
WHERE d.date >= CURRENT_DATE - INTERVAL '30 days'
  AND s.site_name = 'Production'
ORDER BY d.date
```

--------------------------------

### Find assets with successful credentials

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Filters for assets that have been successfully authenticated and assessed for vulnerabilities.

```sql
SELECT asset_id, host_name, ip_address, os_description
FROM dim_asset
WHERE credential_status = 'All credentials successful'
  AND assessed_for_vulnerabilities = true
```

--------------------------------

### Query large files across assets

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves file paths and sizes exceeding 1GB, joined with asset host names.

```sql
SELECT a.host_name, f.path, f.size
FROM dim_asset_file f
JOIN dim_asset a ON f.asset_id = a.asset_id
WHERE f.size > 1000000000 -- > 1GB
ORDER BY f.size DESC
```

--------------------------------

### Retrieve vulnerability details and solutions

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Fetches specific vulnerability metadata and associated solution identifiers for a given vulnerability ID.

```sql
SELECT v.nexpose_id, v.title, v.severity, v.cvss_v3_score,
       COUNT(f.asset_id) as affected_assets,
       STRING_AGG(DISTINCT s.nexpose_id, ', ') as solution_ids,
       STRING_AGG(DISTINCT s.solution_type, ', ') as solution_types
FROM dim_vulnerability v
LEFT JOIN fact_asset_vulnerability_finding f ON v.vulnerability_id = f.vulnerability_id
LEFT JOIN dim_vulnerability_solution vs ON v.vulnerability_id = vs.vulnerability_id
LEFT JOIN dim_solution s ON vs.solution_id = s.solution_id
WHERE v.vulnerability_id = 45678
GROUP BY v.vulnerability_id, v.nexpose_id, v.title, v.severity, v.cvss_v3_score
```

--------------------------------

### Query 90-day vulnerability trends

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-aggregate.md

Calculates daily vulnerability changes over a 90-day period using the fact_all_date table.

```sql
SELECT date, vulnerabilities, critical_vulnerabilities,
       ROUND(average_risk_score, 2) as avg_risk,
       vulnerabilities - LAG(vulnerabilities) OVER (ORDER BY date) as daily_change
FROM fact_all_date
WHERE date >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY date
```

--------------------------------

### Query Windows asset vulnerability metrics

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves vulnerability counts for Windows assets by joining dim_asset with fact_asset.

```sql
SELECT a.asset_id, a.host_name, a.ip_address, f.vulnerabilities, f.critical_vulnerabilities
FROM fact_asset f
JOIN dim_asset a ON f.asset_id = a.asset_id
WHERE a.os_family = 'Windows'
  AND f.vulnerabilities > 0
ORDER BY f.critical_vulnerabilities DESC
```

--------------------------------

### List All Tables in Schema

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/system-tables.md

Retrieves a list of all tables and their types within the public schema.

```sql
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name
```

--------------------------------

### Querying text fields in dim_vulnerability

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/data-types.md

Use for retrieving variable-length strings like titles and descriptions.

```sql
SELECT nexpose_id, title, description
FROM dim_vulnerability
WHERE title LIKE '%buffer%'
```

--------------------------------

### Find Vulnerable Containers

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Retrieves container details for assets that are either vulnerable or currently running.

```sql
SELECT a.host_name as container_host, c.name, c.status, c.image_id,
       f.vulnerabilities, f.critical_vulnerabilities
FROM dim_asset_container c
JOIN dim_asset a ON c.asset_id = a.asset_id
LEFT JOIN fact_asset f ON c.asset_id = f.asset_id
WHERE f.vulnerabilities > 0 OR c.status = 'RUNNING'
ORDER BY f.critical_vulnerabilities DESC
```

--------------------------------

### Find Assets by Credential Status

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Summarizes vulnerability and risk metrics grouped by the asset's credential status.

```sql
SELECT a.credential_status, COUNT(*) as asset_count,
       SUM(f.vulnerabilities) as total_vulns,
       ROUND(AVG(f.risk_score), 2) as avg_risk
FROM dim_asset a
LEFT JOIN fact_asset f ON a.asset_id = f.asset_id
GROUP BY a.credential_status
ORDER BY total_vulns DESC
```

--------------------------------

### Query warehouse-wide security metrics

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-aggregate.md

Retrieves high-level vulnerability and risk metrics from the fact_all table.

```sql
SELECT vulnerabilities, critical_vulnerabilities, 
       ROUND(average_risk_score, 2) as avg_risk,
       pci_failing_assets, assets
FROM fact_all
```

--------------------------------

### Query assets running HTTP or HTTPS services

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Joins the service table with the asset table to identify assets hosting specific web services.

```sql
SELECT DISTINCT a.asset_id, a.host_name, s.port, s.product
FROM dim_asset_service s
JOIN dim_asset a ON s.asset_id = a.asset_id
WHERE s.service_name = 'HTTP'
  OR s.service_name = 'HTTPS'
```

--------------------------------

### Track remediation progress with SQL

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-core.md

Aggregates remediation and verification counts for assets over the last 30 days.

```sql
SELECT a.host_name, COUNT(DISTINCT r.vulnerability_id) as remediated_vulns,
       COUNT(CASE WHEN r.verified_date IS NOT NULL THEN 1 END) as verified,
       MAX(r.remediation_date) as last_remediation
FROM fact_asset_vulnerability_finding_remediation r
JOIN dim_asset a ON r.asset_id = a.asset_id
WHERE r.remediation_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY r.asset_id, a.host_name
ORDER BY verified DESC
```

--------------------------------

### Query high-popularity malware kits

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-vulnerability.md

Retrieves vulnerability details for malware kits categorized as Favored or Popular.

```sql
SELECT v.nexpose_id, v.title, m.name, m.popularity
FROM dim_vulnerability_malware_kit m
JOIN dim_vulnerability v ON m.vulnerability_id = v.vulnerability_id
WHERE m.popularity IN ('Favored', 'Popular')
ORDER BY v.risk_score DESC
```

--------------------------------

### Query asset security summary

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-core.md

Retrieves key security metrics for assets with vulnerabilities or policy failures, joined with asset metadata.

```sql
SELECT a.host_name, a.os_description, f.vulnerabilities, f.critical_vulnerabilities,
       f.risk_score, f.pci_status, f.policies, f.policy_failures
FROM fact_asset f
JOIN dim_asset a ON f.asset_id = a.asset_id
WHERE f.vulnerabilities > 0 OR f.policy_failures > 0
ORDER BY f.risk_score DESC
LIMIT 50
```

--------------------------------

### Query port-specific vulnerability details

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-core.md

Retrieves host names, vulnerability IDs, and port information for open vulnerabilities on specific ports.

```sql
SELECT a.host_name, v.nexpose_id, v.title, i.port, i.protocol
FROM fact_asset_vulnerability_instance i
JOIN dim_asset a ON i.asset_id = a.asset_id
JOIN dim_vulnerability v ON i.vulnerability_id = v.vulnerability_id
WHERE i.port IN (22, 3389, 445)
  AND i.fixed_date IS NULL
ORDER BY a.host_name, i.port
```

--------------------------------

### Query Windows Server 2019 systems

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Joins operating system data with asset information to identify specific OS versions.

```sql
SELECT a.asset_id, a.host_name, o.cpe
FROM dim_asset_operating_system o
JOIN dim_asset a ON o.asset_id = a.asset_id
WHERE o.name = 'Windows Server 2019'
```

--------------------------------

### Find vulnerabilities associated with malware

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Lists vulnerabilities linked to popular malware kits, including the count of assets at risk.

```sql
SELECT v.nexpose_id, v.title, v.severity, v.risk_score,
       m.name as malware_family, m.popularity,
       COUNT(DISTINCT f.asset_id) as assets_at_risk
FROM dim_vulnerability_malware_kit m
JOIN dim_vulnerability v ON m.vulnerability_id = v.vulnerability_id
LEFT JOIN fact_asset_vulnerability_finding f ON v.vulnerability_id = f.vulnerability_id
WHERE m.popularity IN ('Favored', 'Popular')
GROUP BY v.vulnerability_id, v.nexpose_id, v.title, v.severity, v.risk_score,
         m.name, m.popularity
ORDER BY assets_at_risk DESC
```

--------------------------------

### List all asset groups

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves a list of all defined asset groups along with their member counts, sorted by name.

```sql
SELECT asset_group_id, name, description, asset_count
FROM dim_asset_group
ORDER BY name
```

--------------------------------

### Identify assets with multiple IPv6 addresses

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Filters for assets containing more than one IPv6 address by checking for the colon character in the address string.

```sql
SELECT asset_id, COUNT(*) as address_count
FROM dim_asset_address
WHERE ip_address::text LIKE '%:%' -- IPv6 pattern
GROUP BY asset_id
HAVING COUNT(*) > 1
```

--------------------------------

### Query assets by group name

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves asset details for all members of a specific asset group.

```sql
SELECT a.asset_id, a.host_name, a.ip_address
FROM dim_asset_group_asset ga
JOIN dim_asset a ON ga.asset_id = a.asset_id
WHERE ga.asset_group_id = (SELECT asset_group_id FROM dim_asset_group WHERE name = 'Production')
```

--------------------------------

### Query stopped containers on hosts

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Identifies containers with an 'EXITED' status, ordered by their finish time.

```sql
SELECT a.host_name, c.name, c.status, c.finished
FROM dim_asset_container c
JOIN dim_asset a ON c.asset_id = a.asset_id
WHERE c.status = 'EXITED'
ORDER BY c.finished DESC
```

--------------------------------

### List custom tags

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Retrieves all tags categorized as 'CUSTOM' from the dim_tag table.

```sql
SELECT tag_id, tag_name, description
FROM dim_tag
WHERE tag_type = 'CUSTOM'
ORDER BY tag_name
```

--------------------------------

### Query solutions for asset vulnerabilities

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves unique solutions and their types for vulnerabilities identified on a specific asset.

```sql
SELECT DISTINCT s.solution_id, s.summary, s.solution_type
FROM dim_asset_vulnerability_finding_solution fs
JOIN dim_solution s ON fs.solution_id = s.solution_id
WHERE fs.asset_id = 12345
```

--------------------------------

### Track vulnerability trends with SQL

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-core.md

Use this query to aggregate the number of affected assets and total vulnerability instances for a specific vulnerability over the last 30 days.

```sql
SELECT date, COUNT(DISTINCT asset_id) as affected_assets,
       SUM(vulnerability_instances) as total_instances
FROM fact_asset_vulnerability_finding_date
WHERE vulnerability_id = 45678
  AND date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY date
ORDER BY date
```

--------------------------------

### Track remediation solutions

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/schema-overview.md

Joins vulnerability findings with solution data to identify remediation estimates.

```sql
SELECT f.asset_id, f.vulnerability_id, s.solution_id, s.estimate
FROM fact_asset_vulnerability_finding f
JOIN dim_vulnerability_solution vs ON f.vulnerability_id = vs.vulnerability_id
JOIN dim_solution s ON vs.solution_id = s.solution_id
```

--------------------------------

### List validated vulnerabilities for an asset

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Joins validated vulnerability records with vulnerability definitions to list details for a specific asset ID.

```sql
SELECT v.vulnerability_id, v.title, v.severity, av.validated_date
FROM dim_asset_validated_vulnerability av
JOIN dim_vulnerability v ON av.vulnerability_id = v.vulnerability_id
WHERE av.asset_id = 12345
```

--------------------------------

### Organizational Path Diagram

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/README.md

Conceptual diagram showing relationships between sites, groups, tags, and assets.

```text
dim_site ← dim_site_asset → dim_asset
     ↓
dim_site_target

dim_asset_group ← dim_asset_group_asset → dim_asset

dim_tag ← dim_asset_tag → dim_asset
```

--------------------------------

### Query IP addresses for a specific asset

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Retrieves all IP and MAC address records associated with a specific asset ID, ordered by primary status.

```sql
SELECT ip_address, mac_address, primary
FROM dim_asset_address
WHERE asset_id = 12345
ORDER BY primary DESC
```

--------------------------------

### Track Remediation Progress

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Aggregates fixed vulnerabilities and remediated assets by month over the last 6 months.

```sql
SELECT EXTRACT(MONTH FROM r.remediation_date)::int as month,
       COUNT(DISTINCT r.vulnerability_id) as vulnerabilities_fixed,
       COUNT(DISTINCT r.asset_id) as assets_remediated
FROM fact_asset_vulnerability_finding_remediation r
WHERE r.remediation_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY EXTRACT(MONTH FROM r.remediation_date)::int
ORDER BY month
```

--------------------------------

### Query asset by unique identifier

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-asset.md

Filters assets based on specific identifier types such as hardware serial numbers or SMBIOS UUIDs.

```sql
SELECT a.asset_id, a.host_name
FROM dim_asset_unique_id ui
JOIN dim_asset a ON ui.asset_id = a.asset_id
WHERE ui.unique_id_type = 'HARDWARE_SERIAL'
  AND ui.unique_id = 'SN12345678'
```

--------------------------------

### List scan targets for a specific site

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Retrieves the range and type of scan targets associated with a site named 'Production'.

```sql
SELECT range, range_type
FROM dim_site_target
WHERE site_id = (SELECT site_id FROM dim_site WHERE site_name = 'Production')
```

--------------------------------

### Query sites with recent scan activity

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Retrieves site details for sites scanned within the last 7 days, ordered by the most recent scan date.

```sql
SELECT site_id, site_name, asset_count, last_scan_date
FROM dim_site
WHERE last_scan_date >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY last_scan_date DESC
```

--------------------------------

### Map policy rules to NIST controls

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/dimension-tables-policy-site-scan.md

Joins policy rules with mapping data to identify rules associated with specific NIST control families.

```sql
SELECT pr.rule_number, pr.title, m.nist_control_id, m.nist_family
FROM dim_policy_rule_cce_platform_nist_control_mapping m
JOIN dim_policy_rule pr ON m.policy_rule_id = pr.policy_rule_id
WHERE m.nist_control_id LIKE 'SI-%'
ORDER BY m.nist_control_id
```

--------------------------------

### Track Remediation Progress

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/README.md

Compares affected versus remediated asset counts per vulnerability.

```sql
SELECT v.nexpose_id, v.title,
       COUNT(DISTINCT f.asset_id) as affected,
       COUNT(DISTINCT r.asset_id) as remediated
FROM dim_vulnerability v
LEFT JOIN fact_asset_vulnerability_finding f ON v.vulnerability_id = f.vulnerability_id
LEFT JOIN fact_asset_vulnerability_finding_remediation r 
  ON v.vulnerability_id = r.vulnerability_id
GROUP BY v.vulnerability_id, v.nexpose_id, v.title
ORDER BY affected DESC
```

--------------------------------

### Retrieve Policy Compliance Summary

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Calculates compliance percentages per policy and includes the total count of assessed assets.

```sql
SELECT p.title, f.passed_rules, f.failed_rules, f.unscored_passed_rules,
       ROUND(f.compliance_score, 2) as compliance_pct,
       (SELECT COUNT(DISTINCT a.asset_id) FROM dim_asset a 
        WHERE a.assessed_for_policies = true) as total_assessed
FROM fact_policy f
JOIN dim_policy p ON f.policy_id = p.policy_id
ORDER BY f.compliance_score
```

--------------------------------

### Identify assets with failing policies

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/api-reference/fact-tables-policy.md

Filters for Windows assets that have failed one or more policy rules, ordered by the number of failures.

```sql
SELECT a.host_name, p.title, f.failed_rules, f.last_evaluated_date
FROM fact_asset_policy f
JOIN dim_asset a ON f.asset_id = a.asset_id
JOIN dim_policy p ON f.policy_id = p.policy_id
WHERE f.failed_rules > 0
  AND a.os_family = 'Windows'
ORDER BY f.failed_rules DESC
```

--------------------------------

### Retrieve Column Definitions

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/system-tables.md

Fetches column metadata for tables matching the 'dim_%' pattern.

```sql
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name LIKE 'dim_%'
ORDER BY table_name, ordinal_position
```

--------------------------------

### Monitor Data Freshness

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/system-tables.md

Calculates the age of the latest data relative to the current date.

```sql
SELECT MAX(day) as latest_data,
       CURRENT_DATE - MAX(day) as days_behind,
       COUNT(*) as historical_days
FROM periods
```

--------------------------------

### Track Policy Compliance Trends

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Uses window functions to calculate daily changes in compliance percentage over the last 30 days.

```sql
SELECT p.title, d.date,
       ROUND(d.compliance_percentage, 2) as compliance_pct,
       LAG(ROUND(d.compliance_percentage, 2)) OVER (PARTITION BY p.policy_id ORDER BY d.date) as previous_day,
       ROUND(d.compliance_percentage, 2) - LAG(ROUND(d.compliance_percentage, 2)) OVER (PARTITION BY p.policy_id ORDER BY d.date) as daily_change
FROM fact_policy_date d
JOIN dim_policy p ON d.policy_id = p.policy_id
WHERE d.date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY p.policy_id, d.date
```

--------------------------------

### Identify Non-Compliant Windows Assets

Source: https://github.com/riza/rapid7-insightvm-dwh-docs/blob/main/_autodocs/query-patterns.md

Filters for assets with failed policy rules, specifically targeting Windows systems.

```sql
SELECT a.host_name, p.title, ap.failed_rules,
       ROUND(ap.compliance_score, 2) as compliance_pct
FROM fact_asset_policy ap
JOIN dim_asset a ON ap.asset_id = a.asset_id
JOIN dim_policy p ON ap.policy_id = p.policy_id
WHERE ap.failed_rules > 0
  AND a.os_family = 'Windows'
ORDER BY ap.failed_rules DESC
```