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)