Hello.
I’ve searched the forum and found similar request on the query I am looking for. How can I return ONLY the solution for the OS version on which the vulnerability was found on (Server 2008, Server 2012 R2, Windows 10, etc) and not the solution for all versions of the OS? Thank you in advance!
@h3xn31_h3xn31, I’d recommend using the dim_asset_vulnerability_best_solution table. It should provide the best solution ID for the asset which could then be used to join the dim_solution table to get the details.
Thank you tyler for the info however i’m not familiar with sql. do you have a report already built that I can try?
Here’s a basic one to get you started. It provides basic host details, the vulnerability details, and the solution that is specific to that asset.
SELECT da.asset_id, da.host_name, dos.description,dv.title, htmltotext(dv.description), dv.cvss_score, round(dv.riskscore::numeric, 0), ds.summary, htmltotext(ds.fix)
FROM dim_asset da
JOIN dim_operating_system dos on da.operating_system_id = dos.operating_system_id
JOIN fact_asset_vulnerability_finding favf on da.asset_id = favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_asset_vulnerability_best_solution davbs on da.asset_id = davbs.asset_id
JOIN dim_solution ds on davbs.solution_id = ds.solution_id;
Hi tyler.
Thank you for your help on this. When I run this report against a target scan that has a total of (2) assets in it the report shows many duplicates of the scan cve and solution.
here is a snippet of the output:
Could you try it with a GROUP BY as shown below?
SELECT da.asset_id,
da.host_name,
dos.description,
dv.title,
htmltotext(dv.description),
dv.cvss_score,
round(dv.riskscore::numeric, 0),
ds.summary,
htmltotext(ds.fix)
FROM dim_asset da
JOIN dim_operating_system dos on da.operating_system_id = dos.operating_system_id
JOIN fact_asset_vulnerability_finding favf on da.asset_id = favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_asset_vulnerability_best_solution davbs on da.asset_id = davbs.asset_id
JOIN dim_solution ds on davbs.solution_id = ds.solution_id
GROUP BY da.asset_id, da.host_name, dos.description, dv.title, htmltotext(dv.description), dv.cvss_score,
round(dv.riskscore::numeric, 0), ds.summary, htmltotext(ds.fix);
That should group any of the duplicate rows.
I ran that and i’m still seeing all the affected operating systems in the “summary” column. am i looking at this wrong? i would assume the scan results would only show me the cve and the fix for the os detected on the affected server.
@h3xn31_h3xn31, if multiple operating systems appear in the summary column, that means that the summary associated with the solution is written that way. Does the solution summary in the UI match the one in the report?
Ok so after looking at one asset and a certain CVE, under the REMEDIATION section then ASSET BEST SOLUTIONS it list only the solution for the OS type which would be Server 2016. How can I get the ASSET BEST SOLUTIONS from a SQL query?
When I run this query:
SELECT da.asset_id, da.host_name, dos.description,dv.title, htmltotext(dv.description),
dv.cvss_score, round(dv.riskscore::numeric, 0), ds.summary, htmltotext(ds.fix)
FROM dim_asset da
JOIN dim_operating_system dos on da.operating_system_id = dos.operating_system_id
JOIN fact_asset_vulnerability_finding favf on da.asset_id = favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_asset_vulnerability_best_solution davbs on da.asset_id = davbs.asset_id
JOIN dim_solution ds on davbs.solution_id = ds.solution_id;
Then preview it, i am seeing 10 rows with all the same duplicate information.
So again I just ran the above query against a scan which includes (4) assets and each asset has many duplicate rows.
I see that the query for the results above was the one without the GROUP BY statement. Did you get the same results with the second query that I posted?
Without seeing the summaries/query results in their entirety it’s difficult for me to determine what might be happening. The dim_asset_vulnerability_best_solution table is the table the provides the best solution for an asset. The GROUP BY should remove any duplicate rows. If that’s not the case, then there is likely something different between them.
@h3xn31_h3xn31, my apologies. I think the issue was that the JOIN for the dim_asset_vulnerability_best_solution table need to also join where the vulnerability IDs match. Please try this query, I think the results are what you’re looking for.
SELECT da.asset_id,
da.host_name,
dos.description,
dv.title,
htmltotext(dv.description) AS description,
dv.cvss_score,
round(dv.riskscore::numeric, 0) AS riskscore,
ds.summary,
htmltotext(ds.fix) AS fix
FROM dim_asset da
JOIN dim_operating_system dos on da.operating_system_id = dos.operating_system_id
JOIN fact_asset_vulnerability_finding favf on da.asset_id = favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_asset_vulnerability_best_solution davbs
on da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
JOIN dim_solution ds on davbs.solution_id = ds.solution_id
GROUP BY da.asset_id, da.host_name, dos.description, dv.title, dv.description, dv.cvss_score,
dv.riskscore, ds.summary, ds.fix;
Thanks a bunch Tyler for your help on this. Ill check it out now.
I’m sorry but i am still seeing duplicate cve for the same asset and the solutions listed are for different os versions.
here is the output for one asset:
In this case, it would be because there are multiple solution entries in that table for the asset (now that the duplicate entry issue is sorted). It could be that a specific solution could not be found based on the vulnerability check details and the fingerprint of the host. If the user interface does not mirror those details, the best route may be to submit a support ticket.
Hello Tyler.
The UI only shows the (1) solution per CVE for the asset OS type. The SQL report shows all the solutions for all affected OS so does that mean the I do not have a DB issue, correct?
The SQL Query Export report uses a dynamically built data model (referred to as the Reporting Data Model) which is different from the product database. This is likely something that is just slightly different between the two and how the data is represented.
I’d recommend checking out the dim_solution table documentation to see if there’s maybe another field in the data that could be used to group in the way that you’re looking for.
Hello again all.
I am still having an issue with this. I currently have a support case open but that process if very slow. All I am trying to do is to run a report on a scan of Windows servers and export JUST the “solution” of the affected SCANNED OS. Currently my reports include the solution for ALL effected OS’s.
TIA!!