Create a SQL query for getting a Patching Team Friendly report

Create a SQL query report to get following parameters:

Hostname, IPAddress, Operating System and Version, Architechture, Last Scanned, Credential Success, Vulnerability Title, Solution KB article, Solution Summary, Highest superceded KB article, HighestSuperceded Solution Summary, CVSS score, Exploits, Malware, Risk Score, Severity, Vulnerability Category

I have created the following query which gives duplicate solutions per asset per vulnerability since I cannot get a link between the asset OS and solutionID. Kindly help update my below query to get the desired parameters.

SELECT DISTINCT pt.Hostname, pt.OperatingSystem, pt.Arch, pt.vulnerability, pt.SolutionId, pt.Solution, pt.solution_id AS SupercedingSolutionId, ds.summary AS HighestSupercedingSolution
FROM (SELECT da.host_name AS Hostname, dos.description AS OperatingSystem, dos.architecture AS Arch, dv.title AS vulnerability, ds.solution_id AS SolutionId, ds.summary AS Solution, dshs.superceding_solution_id AS solution_id
FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN fact_asset_vulnerability_instance favi USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_solution dvs USING (vulnerability_id)
JOIN dim_solution ds USING (solution_id)
JOIN dim_solution_highest_supercedence dshs USING (solution_id)) pt JOIN dim_solution ds USING (solution_id)

1 Like

When looking to find the solutions of vulnerability findings in your environment, there are two great tables to start from:

  1. dim_asset_vulnerability_solution
  2. dim_asset_vulnerability_best_solution

If you start from one of these two, then you will be able to join dim_asset, fact_asset, dim_vulnerability, and dim_solution to get the fields you need to help communicate to a patching team. The main reason you are getting multiple results currently is because fact_asset_vulnerability_instance will result in multiple vulnerability instances for the same finding. If you want a quick change then you can look at changing fact_asset_vulnerability_instance to fact_asset_vulnerability_finding.

how do you import the table dim_asset_vuln_best_solution?

@ss153152 Zac no longer works here unfortunately, but to answer your question you don’t need to specifically import the table.

Are you asking how to JOIN that table to your existing query?