Query Rapid7 Data

Execute a SQL query against the Rapid7 database.

The database contains the following tables loaded from Rapid7 InsightVM Bulk Export API Parquet files:

assets — Asset inventory data: Key fields: orgId, assetId, agentId, hostName, ip, mac, osFamily, osProduct, osVersion, osDescription, riskScore, sites, assetGroups, tags, awsInstanceId, azureResourceId, gcpObjectId

vulnerabilities — Combined asset + vulnerability data: Key fields: orgId, assetId, vulnId, checkId, port, protocol, title, description, severity, severityRank, cvssScore, cvssV3Score, cvssV3Severity, hasExploits, epssscore, epsspercentile, riskScoreV2_0, cves, firstFoundTimestamp, reintroducedTimestamp, dateAdded, dateModified, datePublished, pciCompliant, pciSeverity

policies — Policy compliance results (agent and scan based): Key fields: orgId, assetId, benchmarkNaturalId, profileNaturalId, benchmarkVersion, ruleNaturalId, ruleTitle, finalStatus, proof, lastAssessmentTimestamp, benchmarkTitle, profileTitle, publisher, fixTexts, rationales, source ('agent' or 'scan')

vulnerability_remediation — Vulnerability remediation tracking: Key fields: orgId, assetId, cveId, vulnId, proof, firstFoundTimestamp, reintroducedTimestamp, lastDetected, lastRemoved, title, description, cvssV2Score, cvssV3Score, cvssV2Severity, cvssV3Severity, cvssV2AttackVector, cvssV3AttackVector, riskScoreV2_0, datePublished, dateAdded, dateModified, epssscore, epsspercentile

Use this tool to query any of the above tables. You can filter, aggregate, join across tables, or perform any SQL-based analysis supported by DuckDB.

Examples:

• SELECT * FROM vulnerabilities WHERE severity = 'Critical' LIMIT 10

• SELECT severity, COUNT(*) FROM vulnerabilities GROUP BY severity

• SELECT * FROM policies WHERE finalStatus = 'fail' LIMIT 10

• SELECT cveId, COUNT(*) FROM vulnerability_remediation GROUP BY cveId