メニュー
Expert analysis of Rapid7 InsightVM data exported via Bulk Export API with strict MCP requirements
Codex または Claude でインストール この Prompt をコピーして Codex、Claude、または他のアシスタントに貼り付けると、Skill ページを確認してインストールできます。
SOC 職業分類に基づく
| name | Rapid7 Bulk Export Analysis Expert |
| description | Expert analysis of Rapid7 InsightVM data exported via Bulk Export API with strict MCP requirements |
| version | 0.5.0 |
| author | Rapid7 Bulk Export MCP Tool |
| tags | ["security","vulnerabilities","rapid7","insightvm","bulk-export","analysis","policy","remediation"] |
CRITICAL REQUIREMENTS:
get_stats() or list_exports())This skill ONLY works with the Rapid7 MCP server. You must:
Verify MCP server is available - Check if you have access to these tools:
list_exports() - Check available exports and their datesget_stats() - Check if data is loaded (covers all tables)start_export(export_type, start_date, end_date) - Kick off any export type (instant, non-blocking)check_export_status(export_id) - Check export progress (instant)download_and_load_export(export_id, export_type) - Download and load a completed exportquery(sql) - Execute SQL queries against all tablesget_schema() - View table schemas for all tablesCheck data availability FIRST - Before ANY analysis:
1. Call list_exports() to see available exports
2. Call get_stats() to check if data is loaded
3. Only start a new export if:
- No data exists
- Last export is older than 1 day
- User requests fresh data
4. For a COMPLETE dataset, ensure all three export types have been run
If MCP is not available:
The data comes from Rapid7 InsightVM's Bulk Export API, which exports three types of data:
assets, vulnerabilities, policies, vulnerability_remediation, and asset_softwarerapid7_bulk_export_tracking.db to avoid redundant downloadsFIRST ACTION: Check if data is already available
You should:
- Call list_exports() to see available exports and their dates
- Call get_stats() to check if data is loaded and see row counts for all tables
- If data exists and is from today, skip to Step 4 (analysis)
- If no data or stale (>1 day old), proceed to Step 2
- Check which export types have been loaded — for a complete dataset,
you need vulnerability, policy, remediation, AND asset_software data
To load a complete dataset, kick off all three exports, then poll and load each:
1. Call start_export(export_type="vulnerability")
→ Save the vulnerability export_id
2. Call start_export(export_type="policy")
→ Save the policy export_id
3. Call start_export(export_type="remediation", start_date="YYYY-MM-DD", end_date="YYYY-MM-DD")
→ Save the remediation export_id
→ Default to last 30 days if user doesn't specify dates
4. Call start_export(export_type="asset_software")
→ Save the asset_software export_id
All four calls return instantly with export IDs. Inform the user:
"Starting Rapid7 exports. Each takes 3-5 minutes on Rapid7's
servers. I'll check on them shortly."
4. Wait 30 seconds, then check each export:
- check_export_status(export_id) for each
- If still PENDING/PROCESSING, wait another 30 seconds and check again
- Repeat until all three are COMPLETE
5. Once an export is COMPLETE, load it:
- download_and_load_export(export_id, export_type="vulnerability")
- download_and_load_export(export_id, export_type="policy")
- download_and_load_export(export_id, export_type="remediation")
- download_and_load_export(export_id, export_type="asset_software")
6. Inform the user:
- "All exports loaded. X assets, Y vulnerabilities, Z policies,
W remediation records, V software packages."
Only after confirming data availability can you proceed with analysis. Remember that different analyses require different tables:
assets + vulnerabilities tablespolicies table (+ assets for joins)vulnerability_remediation table (+ assets for joins)The Rapid7 Bulk Export data is loaded into four separate DuckDB tables:
assets)The assets table contains core asset identification and metadata. Each row represents a unique asset in your environment.
Identification Fields:
orgId (String) - Organization IDassetId (String) - Unique asset identifieragentId (String) - Rapid7 Agent IDCloud Identifiers:
awsInstanceId (String) - AWS instance ID (if applicable)azureResourceId (String) - Azure resource ID (if applicable)gcpObjectId (String) - GCP object ID (if applicable)Network Information:
mac (String) - Primary MAC addressip (String) - Primary IP addresshostName (String) - Primary hostnameOperating System Details:
osArchitecture (String) - OS architecture (e.g., x86_64)osFamily (String) - OS family (e.g., Windows, Linux)osProduct (String) - OS product nameosVendor (String) - OS vendor (e.g., Microsoft)osVersion (String) - OS versionosType (String) - OS type (e.g., Server, Workstation)osDescription (String) - Full OS descriptionRisk and Organization:
riskScore (Double) - Asset risk score (scale: 1–1000; legacy — deprecated as of Jan 2026, use riskScoreV2_0 instead)sites (List) - Array of sites the asset belongs toassetGroups (List) - Asset groupstags (List) - Tags with name and tagTypevulnerabilities)The vulnerabilities table contains detailed vulnerability information. Each row represents a vulnerability instance on a specific asset.
Identification:
orgId (String) - Organization IDassetId (String) - Asset identifiervulnId (String) - Vulnerability identifiercheckId (String) - Unique check identifierNetwork Context:
port (Integer) - Scanned port (if applicable)protocol (String) - Protocol (e.g., TCP, UDP)nic (String) - Network interface (if applicable)Vulnerability Details:
title (String) - Vulnerability titledescription (String) - Detailed description (HTML/XML content)proof (String) - Proof of vulnerabilityCVSS v2 Metrics:
cvssScore (Double) - CVSS v2 scorecvssAccessVector (String) - Access vector (N, A, L)cvssAccessComplexity (String) - Access complexity (H, M, L)cvssAuthentication (String) - Authentication (N, S, M)cvssConfidentialityImpact (String) - Confidentiality impact (N, P, C)cvssIntegrityImpact (String) - Integrity impact (N, P, C)cvssAvailabilityImpact (String) - Availability impact (N, P, C)CVSS v3 Metrics:
cvssV3Score (Double) - CVSS v3 score (0-10)cvssV3Severity (String) - Severity rating (Low, Medium, High, Critical)cvssV3SeverityRank (Integer) - Severity rankcvssV3AttackVector (String) - Attack vector (Network, Adjacent, Local, Physical)cvssV3AttackComplexity (String) - Attack complexity (Low, High)cvssV3PrivilegesRequired (String) - Privileges required (None, Low, High)cvssV3UserInteraction (String) - User interaction (None, Required)cvssV3Scope (String) - Scope (Unchanged, Changed)cvssV3Confidentiality (String) - Confidentiality impact (None, Low, High)cvssV3Integrity (String) - Integrity impact (None, Low, High)cvssV3Availability (String) - Availability impact (None, Low, High)Severity & Risk:
severity (String) - Severity level (Critical, Severe, Moderate)severityRank (Integer) - Severity rankseverityScore (Integer) - Severity scoreriskScore (Double) - Legacy risk score (scale: 1–1000; deprecated as of Jan 2026 — use riskScoreV2_0 instead)riskScoreV2_0 (Integer) - Active Risk score (scale: 1–1000; preferred)Exploit & Threat Intelligence:
hasExploits (Boolean) - Whether exploits existthreatFeedExists (Boolean) - Whether threat feed existsskillLevel (String) - Required skill levelskillLevelRank (Integer) - Skill level rankepssscore (Double) - EPSS score (0-1, probability of exploitation in 30 days)epsspercentile (Double) - EPSS percentile (0-1)Compliance:
pciCompliant (Boolean) - PCI compliance statuspciSeverity (Integer) - PCI severity levelTemporal Information:
firstFoundTimestamp (Timestamp) - When first discovered on assetreintroducedTimestamp (Timestamp) - When reappeared after remediationdateAdded (Timestamp) - When added to vulnerability databasedateModified (Timestamp) - Last modification datedatePublished (Timestamp) - Publication dateReferences:
cves (List) - Array of CVE IDstags (List) - Associated tagsBest Solution (present only if your org is enabled for this feature):
bestSolutionType (String) - Classification of the recommended fix. Five possible values:
PATCH — a direct patch exists with an explicit download URLROLLUP — a cumulative/rollup update addresses it (common for Adobe and Windows)WORKAROUND — Rapid7's classification; in practice the fix text often describes a standard package upgrade; do not surface this label to end users as "no patch available"UNKNOWN — Rapid7 has no solution classified yet; bestSolutionFix is always null for this typenull — solution data is missing; treat identically to UNKNOWNbestSolutionSummary (String) - Short human-readable description of the recommended action (e.g. "Upgrade Apache Log4j Core to 2.25.4"). Always plain text, safe to display directly. Null when bestSolutionType is UNKNOWN or null.bestSolutionFix (String) - Detailed actionable fix instructions. May contain HTML markup (anchor tags, <p> tags) — strip or render HTML before displaying as plain text. Always null when bestSolutionType is UNKNOWN or null.policies)The policies table contains policy compliance assessment results. It combines both agent-based and scan-based policy data into a single table, distinguished by the source column.
source = 'agent'): Collected by the Rapid7 Insight Agent running on the asset. These come from Parquet files with the asset_policy prefix.source = 'scan'): Collected via network-based policy scans. These come from Parquet files with the asset_scan_policy prefix.This unified design means you can query all policy data in one table without needing UNION queries, and filter by source when you need to distinguish between agent and scan results.
Identification:
orgId (String) - Organization IDassetId (String) - Asset identifier (join with assets table for asset details)Benchmark Information:
benchmarkNaturalId (String) - Unique benchmark identifier (e.g., CIS benchmark ID)benchmarkTitle (String) - Human-readable benchmark titlebenchmarkVersion (String) - Benchmark version numberprofileNaturalId (String) - Profile identifier within the benchmarkprofileTitle (String) - Human-readable profile titlepublisher (String) - Benchmark publisher (e.g., "CIS")Rule Assessment:
ruleNaturalId (String) - Unique rule identifier within the benchmarkruleTitle (String) - Human-readable rule titlefinalStatus (String) - Assessment result (e.g., "pass", "fail", "error", "notApplicable")proof (String) - Evidence or details supporting the assessment resultlastAssessmentTimestamp (Timestamp) - When the rule was last assessedRemediation Guidance:
fixTexts (String) - Recommended fix actions for failed rulesrationales (String) - Explanation of why the rule mattersData Source:
source (String) - Values: 'agent' or 'scan'. Indicates whether the policy result came from an agent-based assessment or a scan-based assessment.vulnerability_remediation)The vulnerability_remediation table tracks the lifecycle of vulnerabilities — when they were first found, last detected, and last removed. This data is exported for a specific date range and is useful for tracking remediation progress over time.
Identification:
orgId (String) - Organization IDassetId (String) - Asset identifier (join with assets table for asset details)cveId (String) - CVE identifier (e.g., "CVE-2024-1234")vulnId (String) - Rapid7 vulnerability identifierEvidence:
proof (String) - Proof or evidence of the vulnerabilityLifecycle Timestamps:
firstFoundTimestamp (Timestamp) - When the vulnerability was first discovered on the assetreintroducedTimestamp (Timestamp) - When the vulnerability reappeared after being remediatedlastDetected (Timestamp) - When the vulnerability was last seen on the assetlastRemoved (Timestamp) - When the vulnerability was last removed/remediated (NULL if still present)Vulnerability Details:
title (String) - Vulnerability titledescription (String) - Detailed vulnerability descriptionCVSS Scores:
cvssV2Score (Double) - CVSS v2 scorecvssV3Score (Double) - CVSS v3 score (0-10)cvssV2Severity (String) - CVSS v2 severity ratingcvssV3Severity (String) - CVSS v3 severity ratingAttack Vectors:
cvssV2AttackVector (String) - CVSS v2 attack vectorcvssV3AttackVector (String) - CVSS v3 attack vectorRisk:
riskScoreV2_0 (Integer) - Active Risk score (scale: 1–1000)Temporal Information:
datePublished (Timestamp) - When the vulnerability was publisheddateAdded (Timestamp) - When added to the vulnerability databasedateModified (Timestamp) - Last modification dateExploit Prediction:
epssscore (Double) - EPSS score (0-1, probability of exploitation in 30 days)epsspercentile (Double) - EPSS percentile (0-1)asset_software)The asset_software table contains the installed software inventory for all IVM-managed assets. Each row represents one software package on one asset.
Identification:
assetId (String) - Asset identifier (join with assets table for asset details)orgId (String) - Organization IDSoftware Details:
get_schema() to discover the current columns before querying this table.Notes:
vulnerabilities table — it lists all installed software, not just software with known vulnerabilities.assetId to correlate software inventory with asset metadata or vulnerability findings.Focus on vulnerabilities with:
Use EPSS (Exploit Prediction Scoring System) metrics:
epssscore - Probability (0-1) of exploitation in next 30 daysepsspercentile - Percentile ranking compared to all CVEsSELECT COUNT(*) FROM vulnerabilities WHERE bestSolutionType IS NOT NULLbestSolutionType IN ('PATCH', 'ROLLUP', 'WORKAROUND') and bestSolutionSummary IS NOT NULLbestSolutionFix raw — strip HTML tags before presenting to usersbestSolutionType to understand the remediation landscape; note that WORKAROUND does not mean "no patch" — always show bestSolutionSummary as the primary labelbestSolutionType IN ('UNKNOWN', NULL) when generating actionable fix reportsget_schema() to discover current column names — the schema may evolveasset_software with assets on assetId to correlate software with asset metadatasource columnSELECT
assetId,
hostName,
ip,
mac,
osFamily,
osProduct,
osVersion,
osType,
riskScore
FROM assets
ORDER BY riskScore DESC
LIMIT 100;
SELECT
osFamily,
osProduct,
osVendor,
COUNT(*) as asset_count,
AVG(riskScore) as avg_risk_score
FROM assets
GROUP BY osFamily, osProduct, osVendor
ORDER BY asset_count DESC;
SELECT
CASE
WHEN awsInstanceId IS NOT NULL THEN 'AWS'
WHEN azureResourceId IS NOT NULL THEN 'Azure'
WHEN gcpObjectId IS NOT NULL THEN 'GCP'
ELSE 'On-Premise'
END as cloud_provider,
COUNT(*) as asset_count,
AVG(riskScore) as avg_risk_score
FROM assets
GROUP BY cloud_provider
ORDER BY asset_count DESC;
SELECT
assetId,
hostName,
ip,
osDescription,
riskScore,
sites,
assetGroups
FROM assets
WHERE riskScore > 800 -- Adjust threshold as needed
ORDER BY riskScore DESC
LIMIT 50;
-- Assets in specific sites
SELECT
assetId,
hostName,
ip,
osFamily,
riskScore
FROM assets
WHERE EXISTS (
SELECT 1 FROM unnest(sites) AS site
WHERE site LIKE '%Production%'
)
ORDER BY riskScore DESC;
SELECT
CASE
WHEN osFamily LIKE '%Windows%' THEN 'Windows'
WHEN osFamily LIKE '%Linux%' THEN 'Linux'
WHEN osFamily LIKE '%Unix%' THEN 'Unix'
ELSE 'Other'
END as os_category,
osType,
COUNT(*) as count,
AVG(riskScore) as avg_risk
FROM assets
GROUP BY os_category, osType
ORDER BY count DESC;
SELECT
assetId,
hostName,
vulnId,
title,
cvssV3Score,
epssscore,
epsspercentile,
hasExploits,
firstFoundTimestamp
FROM vulnerabilities
WHERE severity = 'Critical'
AND epssscore > 0.5 -- >50% chance of exploitation
ORDER BY epssscore DESC, cvssV3Score DESC
LIMIT 20;
SELECT
assetId,
hostName,
ip,
osDescription,
COUNT(*) as total_vulns,
SUM(CASE WHEN severity = 'Critical' THEN 1 ELSE 0 END) as critical_count,
SUM(CASE WHEN hasExploits = true THEN 1 ELSE 0 END) as exploitable_count,
MAX(riskScoreV2_0) as max_risk_score,
AVG(cvssV3Score) as avg_cvss_score
FROM vulnerabilities
GROUP BY assetId, hostName, ip, osDescription
ORDER BY critical_count DESC, max_risk_score DESC
LIMIT 50;
SELECT
CASE
WHEN DATEDIFF('day', firstFoundTimestamp, CURRENT_TIMESTAMP) < 30 THEN '0-30 days'
WHEN DATEDIFF('day', firstFoundTimestamp, CURRENT_TIMESTAMP) < 90 THEN '30-90 days'
WHEN DATEDIFF('day', firstFoundTimestamp, CURRENT_TIMESTAMP) < 180 THEN '90-180 days'
ELSE '180+ days'
END as age_bucket,
severity,
COUNT(*) as count,
AVG(cvssV3Score) as avg_cvss
FROM vulnerabilities
GROUP BY age_bucket, severity
ORDER BY age_bucket, severity;
SELECT
v.assetId,
v.hostName,
v.ip,
v.osDescription,
v.vulnId,
v.title,
v.cvssV3Score,
v.severity,
v.cves,
v.firstFoundTimestamp
FROM vulnerabilities v
WHERE EXISTS (
SELECT 1 FROM unnest(v.cves) AS cve
WHERE cve LIKE '%CVE-2024-1234%'
)
ORDER BY v.cvssV3Score DESC;
SELECT
vulnId,
title,
cvssV3Score,
epssscore,
epsspercentile,
hasExploits,
COUNT(DISTINCT assetId) as affected_assets,
AVG(riskScoreV2_0) as avg_risk_score
FROM vulnerabilities
WHERE epssscore > 0.1 -- >10% exploitation probability
GROUP BY vulnId, title, cvssV3Score, epssscore, epsspercentile, hasExploits
HAVING COUNT(DISTINCT assetId) > 5 -- Affects multiple assets
ORDER BY epssscore DESC, affected_assets DESC
LIMIT 25;
SELECT
bestSolutionType,
COUNT(*) as count
FROM vulnerabilities
GROUP BY bestSolutionType
ORDER BY count DESC;
SELECT
assetId,
hostName,
vulnId,
title,
severity,
cvssV3Score,
bestSolutionType,
bestSolutionSummary
-- Note: bestSolutionFix may contain HTML — strip tags before displaying
FROM vulnerabilities
WHERE bestSolutionType IN ('PATCH', 'ROLLUP', 'WORKAROUND')
AND bestSolutionSummary IS NOT NULL
ORDER BY cvssV3Score DESC
LIMIT 50;
SELECT
bestSolutionSummary,
bestSolutionType,
COUNT(DISTINCT vulnId) as distinct_vulns,
COUNT(DISTINCT assetId) as affected_assets,
MAX(cvssV3Score) as max_cvss,
SUM(CASE WHEN hasExploits = true THEN 1 ELSE 0 END) as with_exploits
FROM vulnerabilities
WHERE severity = 'Critical'
AND bestSolutionType IN ('PATCH', 'ROLLUP', 'WORKAROUND')
GROUP BY bestSolutionSummary, bestSolutionType
ORDER BY affected_assets DESC
LIMIT 25;
Always call
get_schema()first — column names for theasset_softwaretable may differ from examples below.
-- Adjust column names based on get_schema() output
SELECT
s.assetId,
a.hostName,
a.ip,
a.osFamily,
COUNT(*) as software_count
FROM asset_software s
LEFT JOIN assets a ON s.assetId = a.assetId
GROUP BY s.assetId, a.hostName, a.ip, a.osFamily
ORDER BY software_count DESC
LIMIT 50;
SELECT
benchmarkTitle,
profileTitle,
source,
COUNT(*) as total_rules,
SUM(CASE WHEN finalStatus = 'pass' THEN 1 ELSE 0 END) as passed,
SUM(CASE WHEN finalStatus = 'fail' THEN 1 ELSE 0 END) as failed,
SUM(CASE WHEN finalStatus = 'error' THEN 1 ELSE 0 END) as errors,
SUM(CASE WHEN finalStatus = 'notApplicable' THEN 1 ELSE 0 END) as not_applicable,
ROUND(100.0 * SUM(CASE WHEN finalStatus = 'pass' THEN 1 ELSE 0 END) / COUNT(*), 2) as pass_rate_pct
FROM policies
GROUP BY benchmarkTitle, profileTitle, source
ORDER BY pass_rate_pct ASC;
SELECT
ruleNaturalId,
ruleTitle,
benchmarkTitle,
COUNT(DISTINCT assetId) as affected_assets,
COUNT(*) as total_failures,
MAX(fixTexts) as fix_guidance
FROM policies
WHERE finalStatus = 'fail'
GROUP BY ruleNaturalId, ruleTitle, benchmarkTitle
ORDER BY affected_assets DESC
LIMIT 25;
SELECT
p.assetId,
a.hostName,
a.ip,
a.osFamily,
p.benchmarkTitle,
COUNT(*) as total_rules,
SUM(CASE WHEN p.finalStatus = 'pass' THEN 1 ELSE 0 END) as passed,
SUM(CASE WHEN p.finalStatus = 'fail' THEN 1 ELSE 0 END) as failed,
ROUND(100.0 * SUM(CASE WHEN p.finalStatus = 'pass' THEN 1 ELSE 0 END) / COUNT(*), 2) as pass_rate_pct
FROM policies p
LEFT JOIN assets a ON p.assetId = a.assetId
GROUP BY p.assetId, a.hostName, a.ip, a.osFamily, p.benchmarkTitle
ORDER BY pass_rate_pct ASC
LIMIT 50;
SELECT
benchmarkTitle,
ruleNaturalId,
ruleTitle,
COUNT(DISTINCT CASE WHEN source = 'agent' THEN assetId END) as agent_assessed,
COUNT(DISTINCT CASE WHEN source = 'scan' THEN assetId END) as scan_assessed,
SUM(CASE WHEN source = 'agent' AND finalStatus = 'pass' THEN 1 ELSE 0 END) as agent_pass,
SUM(CASE WHEN source = 'agent' AND finalStatus = 'fail' THEN 1 ELSE 0 END) as agent_fail,
SUM(CASE WHEN source = 'scan' AND finalStatus = 'pass' THEN 1 ELSE 0 END) as scan_pass,
SUM(CASE WHEN source = 'scan' AND finalStatus = 'fail' THEN 1 ELSE 0 END) as scan_fail
FROM policies
GROUP BY benchmarkTitle, ruleNaturalId, ruleTitle
HAVING agent_assessed > 0 AND scan_assessed > 0
ORDER BY benchmarkTitle, ruleNaturalId;
SELECT
ruleNaturalId,
ruleTitle,
benchmarkTitle,
finalStatus,
fixTexts,
rationales,
COUNT(DISTINCT assetId) as affected_assets
FROM policies
WHERE finalStatus = 'fail'
AND fixTexts IS NOT NULL
GROUP BY ruleNaturalId, ruleTitle, benchmarkTitle, finalStatus, fixTexts, rationales
ORDER BY affected_assets DESC
LIMIT 20;
SELECT
CASE
WHEN lastRemoved IS NOT NULL THEN 'Remediated'
ELSE 'Still Present'
END as status,
COUNT(*) as vuln_count,
COUNT(DISTINCT assetId) as affected_assets,
AVG(cvssV3Score) as avg_cvss,
AVG(CASE
WHEN lastRemoved IS NOT NULL
THEN DATEDIFF('day', firstFoundTimestamp, lastRemoved)
END) as avg_days_to_remediate
FROM vulnerability_remediation
GROUP BY status;
SELECT
cveId,
title,
cvssV3Score,
cvssV3Severity,
COUNT(DISTINCT assetId) as total_assets,
SUM(CASE WHEN lastRemoved IS NOT NULL THEN 1 ELSE 0 END) as remediated_count,
SUM(CASE WHEN lastRemoved IS NULL THEN 1 ELSE 0 END) as still_present_count,
ROUND(100.0 * SUM(CASE WHEN lastRemoved IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 2) as remediation_pct
FROM vulnerability_remediation
GROUP BY cveId, title, cvssV3Score, cvssV3Severity
ORDER BY still_present_count DESC
LIMIT 25;
SELECT
DATE_TRUNC('week', lastRemoved) as remediation_week,
COUNT(*) as vulns_remediated,
COUNT(DISTINCT assetId) as assets_affected,
AVG(DATEDIFF('day', firstFoundTimestamp, lastRemoved)) as avg_days_open,
AVG(cvssV3Score) as avg_cvss_remediated
FROM vulnerability_remediation
WHERE lastRemoved IS NOT NULL
GROUP BY remediation_week
ORDER BY remediation_week DESC
LIMIT 12;
SELECT
vr.assetId,
a.hostName,
a.ip,
vr.cveId,
vr.title,
vr.cvssV3Score,
vr.firstFoundTimestamp,
vr.lastRemoved,
vr.reintroducedTimestamp,
DATEDIFF('day', vr.lastRemoved, vr.reintroducedTimestamp) as days_until_reintroduction
FROM vulnerability_remediation vr
LEFT JOIN assets a ON vr.assetId = a.assetId
WHERE vr.reintroducedTimestamp IS NOT NULL
ORDER BY vr.reintroducedTimestamp DESC
LIMIT 50;
SELECT
vr.assetId,
a.hostName,
a.ip,
vr.cveId,
vr.title,
vr.cvssV3Score,
vr.cvssV3Severity,
vr.epssscore,
vr.firstFoundTimestamp,
DATEDIFF('day', vr.firstFoundTimestamp, CURRENT_TIMESTAMP) as days_open
FROM vulnerability_remediation vr
LEFT JOIN assets a ON vr.assetId = a.assetId
WHERE vr.lastRemoved IS NULL
AND vr.cvssV3Score >= 7.0
ORDER BY vr.cvssV3Score DESC, days_open DESC
LIMIT 50;
SELECT
cvssV3Severity,
COUNT(*) as remediated_count,
AVG(DATEDIFF('day', firstFoundTimestamp, lastRemoved)) as avg_mttr_days,
MIN(DATEDIFF('day', firstFoundTimestamp, lastRemoved)) as min_mttr_days,
MAX(DATEDIFF('day', firstFoundTimestamp, lastRemoved)) as max_mttr_days,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF('day', firstFoundTimestamp, lastRemoved)) as median_mttr_days
FROM vulnerability_remediation
WHERE lastRemoved IS NOT NULL
GROUP BY cvssV3Severity
ORDER BY avg_mttr_days DESC;
SELECT
a.assetId,
a.hostName,
a.ip,
a.osDescription,
a.riskScore,
COUNT(v.vulnId) as total_vulns,
SUM(CASE WHEN v.severity = 'Critical' THEN 1 ELSE 0 END) as critical_count,
SUM(CASE WHEN v.severity = 'Severe' THEN 1 ELSE 0 END) as severe_count,
SUM(CASE WHEN v.hasExploits = true THEN 1 ELSE 0 END) as exploitable_count,
AVG(v.cvssV3Score) as avg_cvss_score
FROM assets a
LEFT JOIN vulnerabilities v ON a.assetId = v.assetId
GROUP BY a.assetId, a.hostName, a.ip, a.osDescription, a.riskScore
ORDER BY critical_count DESC, a.riskScore DESC
LIMIT 50;
SELECT
a.assetId,
a.hostName,
a.ip,
CASE
WHEN a.awsInstanceId IS NOT NULL THEN 'AWS'
WHEN a.azureResourceId IS NOT NULL THEN 'Azure'
WHEN a.gcpObjectId IS NOT NULL THEN 'GCP'
ELSE 'On-Premise'
END as cloud_provider,
a.awsInstanceId,
a.azureResourceId,
a.gcpObjectId,
COUNT(v.vulnId) as critical_vuln_count,
AVG(v.cvssV3Score) as avg_cvss
FROM assets a
INNER JOIN vulnerabilities v ON a.assetId = v.assetId
WHERE v.severity = 'Critical'
GROUP BY a.assetId, a.hostName, a.ip, cloud_provider, a.awsInstanceId, a.azureResourceId, a.gcpObjectId
ORDER BY critical_vuln_count DESC
LIMIT 50;
SELECT
a.assetId,
a.hostName,
a.ip,
a.osProduct,
a.osVersion,
v.vulnId,
v.title,
v.cvssV3Score,
v.epssscore,
v.hasExploits
FROM assets a
INNER JOIN vulnerabilities v ON a.assetId = v.assetId
WHERE a.osFamily LIKE '%Windows%'
AND a.osType = 'Server'
AND v.severity = 'Critical'
AND v.epssscore > 0.3
ORDER BY v.epssscore DESC, v.cvssV3Score DESC
LIMIT 100;
SELECT
a.assetId,
a.hostName,
a.ip,
a.osDescription,
a.riskScore
FROM assets a
LEFT JOIN vulnerabilities v ON a.assetId = v.assetId
WHERE v.vulnId IS NULL
ORDER BY a.riskScore DESC;
SELECT
CASE
WHEN awsInstanceId IS NOT NULL THEN 'AWS'
WHEN azureResourceId IS NOT NULL THEN 'Azure'
WHEN gcpObjectId IS NOT NULL THEN 'GCP'
ELSE 'On-Premise'
END as cloud_provider,
COUNT(DISTINCT assetId) as asset_count,
COUNT(*) as vuln_count,
SUM(CASE WHEN severity = 'Critical' THEN 1 ELSE 0 END) as critical_vulns,
AVG(cvssV3Score) as avg_cvss
FROM vulnerabilities
GROUP BY cloud_provider
ORDER BY critical_vulns DESC;
SELECT
assetId,
hostName,
vulnId,
title,
severity,
firstFoundTimestamp,
reintroducedTimestamp,
DATEDIFF('day', firstFoundTimestamp, reintroducedTimestamp) as days_between
FROM vulnerabilities
WHERE reintroducedTimestamp IS NOT NULL
ORDER BY reintroducedTimestamp DESC
LIMIT 50;
SELECT
pciSeverity,
pciCompliant,
COUNT(*) as vuln_count,
COUNT(DISTINCT assetId) as affected_assets,
AVG(cvssV3Score) as avg_cvss
FROM vulnerabilities
WHERE pciSeverity IS NOT NULL
GROUP BY pciSeverity, pciCompliant
ORDER BY pciSeverity DESC;
get_schema() to see available columns in all tables — especially important for asset_software and for confirming whether bestSolution* columns are presentget_stats() to understand the data distribution across all tables and confirm which tables are loadedassets, vulnerabilities, policies, vulnerability_remediation, asset_softwarestart_export(export_type="vulnerability"), start_export(export_type="policy"), start_export(export_type="remediation"), and start_export(export_type="asset_software"), then check_export_status() → download_and_load_export() for each.assetId)LIMIT clauses when exploring datasource column in the policies table to distinguish between agent-based ('agent') and scan-based ('scan') assessments. Compare results from both sources to identify coverage gaps.vulnerability_remediation table contains data for a specific date range. Check which range was exported using list_exports() to understand the scope of your remediation data.firstFoundTimestamp and lastRemoved in the vulnerability_remediation table to calculate mean time to remediate (MTTR) and track improvement over time.bestSolution* columns in vulnerabilities are only present if your org is enabled for this feature. Always check bestSolutionType IS NOT NULL before using them. UNKNOWN/null types have no fix data — skip them in actionable reports. WORKAROUND does not mean "no patch" — show bestSolutionSummary instead of the type label. Strip HTML from bestSolutionFix before displaying.asset_software table schema may evolve. Always call get_schema() to discover current column names before querying — don't assume column names from examples.The MCP server provides these tools:
Export Tools (non-blocking):
start_export(export_type, start_date, end_date) — Kick off any export type (instant). export_type is one of "vulnerability", "policy", "remediation", or "asset_software". start_date and end_date are only used for remediation exports (YYYY-MM-DD format, defaults to last 30 days).check_export_status(export_id) — Check if an export is done (instant)download_and_load_export(export_id, export_type) — Download completed export and load into DB (~1 min). Pass the same export_type used in start_export.Data Management Tools:
list_exports(limit=10) - List recent exports with dates, types, and metadata (check this FIRST)get_stats() - Get summary statistics for all tables and verify data is loadedload_from_parquet(parquet_path) - Load from existing Parquet files (advanced use)Query Tools:
query(sql="...") - Execute SQL queries against all tables (assets, vulnerabilities, policies, vulnerability_remediation, asset_software)get_schema() - Get table schema for all existing tablesRecommended Workflow:
list_exports() — do we have data from today for all needed export types?get_stats() — is data loaded in the database for all tables?start_export(export_type="vulnerability") → save export_id
b. start_export(export_type="policy") → save export_id
c. start_export(export_type="remediation", start_date="...", end_date="...") → save export_id
d. start_export(export_type="asset_software") → save export_id
e. Wait 30s, then check_export_status(export_id) for each
f. Once COMPLETE: download_and_load_export(export_id, export_type="...") for eachquery(), get_schema(), get_stats()If you cannot access MCP tools:
If get_stats() shows no data:
If start_export() fails:
If check_export_status() shows FAILED:
If download_and_load_export() fails:
If start_export(export_type="remediation") fails with date validation error:
If data seems stale:
assets, vulnerabilities, policies, vulnerability_remediation, and asset_software tables for comprehensive analysispolicies table for CIS and other benchmark compliance