There is some issue is going on with my SQL query because using the fact_asset_vulnerability_age table I have used age_in_days & first_discovered column for vulnerability ageing. But it shows the value from the scan date selected till the SQL query is executed.
Here is my query…
SELECT da.ip_address AS “IP_Address”,
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”,
ds.solution_type AS “solution_type”,
fava.age_in_days AS “Age”,
fava.first_discovered AS “first_discovered”,
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
JOIN fact_asset_vulnerability_age fava ON da.asset_id = fava.asset_id AND 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.ip_address, da.host_name, dos.description, dv.vulnerability_id, dv.exploits, dv.severity, dv.date_published, dv.title, favi.proof, fava.age_in_days, fava.first_discovered, ds.solution_type, ds.summary
Attaching output here…
Is there any fault in my query or is it something else because using the custom template
I can get the Age but I cannot use a custom template because it is not providing the latest solution. @holly_wilsey
The built-in report contains age but the problem is you will not get the latest solution( KB number) & also multiple solutions are provided for one instance.
Can you check this query and check what is the output for age?
This will help us to understand whether our query is wrong or not?
Please!!!
Hey @akhade, I’m trying to better understand what you’re looking to accomplish here. Are you wanting to pull in the single best solution for a particular vulnerability on an asset? There is a table called dim_asset_vulnerability_best_solution, so you could join to this based on the asset + vulnerability ID’s, then get the solution ID there to have a single solution mapped per asset vulnerability.
Hi, @holly_wilsey I don’t have an issue with a solution, I want vulnerability age, which I am not getting using this query. You can see the image data I am getting for age which is not correct.
Is the concern that the age is showing as “0.6666667”, and therefore it’s potentially not correct? I’m wondering if these are recently discovered vulnerabilities, and that’s why it’s showing that number for the age.
When I run the query against my test environment, I see vulnerabilities ranging in age from 10-500 days, so it appears to function as intended in that regard. If you add the below line to the end of your query, you can see what the oldest vulnerabilities are, and that may give you a better idea of the age range of them in your environment.
I have a question, while you run the query against your test environment so did you select scan or site ? Because while selecting a site we are also getting age from 10-500 but while selecting the scan the same age for all vulnerabilities is there. That is where the problem is, we can’t generate the report for the particular scan.
Those vulnerabilities are not recently discovered because using built-in reports we are getting a different age.
The fact_asset_vulnerability_instance table is an accumulating snapshot which only looks at “current vulnerabilities”. Is the data output when NOT using any report filtering producing correct data?
Level of Grain: A vulnerability instance on an asset.
Fact Type: accumulating snapshot
Description: This table provides an accumulating snapshot for all current vulnerability instances on an asset. Only vulnerability instance found to be vulnerable and with no exceptions actively applied will be present within the fact table. If the multiple occurrences of the same vulnerability are found on the asset, a row will be present for each instance.
