Need some late night help with a SQL export for asset+vuln pairings where critical and (exploitable or malware)

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.

1 Like

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:

```
Put your query here!
```