Throwing a Hail Mary here in case anyone could share something tonight. I am trying to build the following:
Asset ID
Asset Name
Asset IP
Vulnerability Title
Vulnerability Solution
Vulnerability Proof/Evidence
Vulnerability Risk Score
Exploit Size/Count
Malware Kit Size/Count
Where: Severity=Critical AND (Exploit Size >0 or Malware Kit Size >0)
I will likely break this down into separate queries if it takes too long, but figured I would ask once and break it out myself. I just don’t have enough experience with either SQL or the IVM data model to get this rolling tonight. Really appreciate any help!
I think this is a good starting point for the query you’re looking for:
SELECT favi.asset_id, da.host_name, da.ip_address, dv.title,
ds.summary, favi.proof, dv.riskscore, dv.exploits,
dv.malware_kits
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset_vulnerability_best_solution davbs
ON davbs.asset_id = favi.asset_id
AND davbs.vulnerability_id = favi.vulnerability_id
JOIN dim_solution ds USING (solution_id)
WHERE dv.severity = 'CRITICAL'
AND (dv.exploits > 0 OR dv.malware_kits > 0)
It looked okay running on my side, but the lab instance I used has less data than a typical organization, so you could further scope it down if need-be.
I know some folks like to refer to the data model here and here to better understand what data is available for these queries and how it’s structured. We also have a list of example queries that you can copy or use for reference (on the left menu, scroll down and you’ll see the list). I hope that helps!
Hi Holly, thank you! I was able to get this going as well eventually, sorry I did not mark it as solved. Here is what I ended up doing, which looks similar. I copied the work of others who posted similar queries and got the rest from the data model pages.
SELECT da.asset_id AS “asset_id”,
da.host_name AS “Hostname”,
da.ip_address AS “IP Address”,
dv.vulnerability_id AS “vulnerability_id”,
dv.exploits AS “Exploit Count”,
dv.malware_kits AS “Malware Count”,
dv.severity AS “Vulnerability Severity Level”,
dv.riskscore AS “Risk Score”,
dv.date_published AS “Vulnerability Published Date”,
dv.title AS “Vulnerability Title”,
htmltotext(favi.proof) AS “Vulnerability Proof”,
ds.summary AS “Vulnerability Solution”
FROM fact_asset fa
JOIN dim_asset da ON fa.asset_id = da.asset_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_vulnerability_malware_kit dvm ON dvm.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_asset_vulnerability_best_solution davbs
ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
LEFT JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
WHERE (dv.exploits > 0 OR dv.malware_kits > 0)
GROUP BY da.asset_id, da.host_name, da.ip_address, dv.vulnerability_id, dv.exploits, dv.malware_kits, dv.severity, dv.riskscore, dv.date_published, dv.title, favi.proof, ds.summary
Apologies for the terrible formatting, not sure how to fix that.
Thanks for sharing back! It looks like there’s a good amount of overlap in our queries, so glad we had the same idea.
For query formatting on here, you can create a code block with two lines of the ``` character (on the same key as ~ for most keyboards) and stick your query in-between. Like this:
can I get date from fact_asset_vulnerability_finding_remediation table in this query
SELECT
da.host_name AS “Hostname”,
dos.description AS “operating_system”,
dv.title AS “Vulnerability_Title”,
dv.severity AS “Severity_level”,
dv.date_published AS “Vulnerability_Published_Date”,
htmltotext(favi.proof) AS “Vulnerability_Proof”,
ds.summary AS “Vulnerability_Solution”,
dv.exploits AS “Exploit_Count”
FROM fact_asset fa
JOIN dim_asset da ON fa.asset_id = da.asset_id
JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_asset_vulnerability_best_solution davbs
ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
LEFT JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
WHERE (dv.exploits >= 0)
GROUP BY da.host_name, dos.description, dv.vulnerability_id, dv.exploits, dv.severity, dv.date_published, dv.title, favi.proof, ds.summary
Hey @akhade, are you currently using the data warehouse for this query, or are you running it against the console? I ask because that is going to determine whether the table fact_asset_vulnerability_finding_remediation is available for use in your query.
If you’re running the query against the console, you can take a look at our reporting data model here to get a better idea of where you can retrieve the vulnerability “first found” date:
So with your query, you could add a JOIN to that table, then add “first_discovered” to your SELECT statement and your GROUP BY. Then the query would look something like this:
SELECT
da.host_name AS "Hostname",
dos.description AS "operating_system",
dv.title AS "Vulnerability_Title",
dv.severity AS "Severity_level",
dv.date_published AS "Vulnerability_Published_Date",
htmltotext(favi.proof) AS "Vulnerability_Proof",
ds.summary AS "Vulnerability_Solution",
dv.exploits AS "Exploit_Count",
fava.first_discovered AS "First Discovered"
FROM fact_asset fa
JOIN dim_asset da ON fa.asset_id = da.asset_id
JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
JOIN fact_asset_vulnerability_age fava ON dv.vulnerability_id = fava.vulnerability_id
LEFT JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_asset_vulnerability_best_solution davbs
ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
LEFT JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
WHERE (dv.exploits >= 0)
GROUP BY da.host_name, dos.description, dv.vulnerability_id, dv.exploits, dv.severity,
dv.date_published, dv.title, favi.proof, ds.summary, fava.first_discovered
It may still need some tweaking, but this should be a good starting point.
Actually, I am running a query on the Security console but I am referring data warehouse model. I am looking for a number of days the vulnerability was first found on the asset, like in the custom template rapid7 provide Vulnerability_age as a parameter. Right now with first discovered I am getting date but I want a number of days the first discovered and scan completed.
And also I am getting repetitive vulnerabilities. What should I do?
Also, the instances are repetitive. Not getting the actual count of vulnerabilities.
Please help me!!!
For 1 asset 1 vulnerability their many solutions I am getting. I just want the latest KB number solution/patch
