SQL Query : Solutions Report with Additional Information

I’m not a big SQL user, but we’ve been trying to make projects for our sysadmins, and while it provides some information, it doesn’t provide the report they find useful to perform their duties. In discussions, we’ve found that a perfect report would contain the following:

A report based on the Solutions ID (which may actually be “nexpose_id”?) (ie; msft-kb4022717-7b6c010d-36ea-4bb4-9d06-9e44c1235690) – For these reports, we wouldn’t mind editing the SQL query each time to contain the specific solution, or if possible, contain a multitude of solutions. Each solution (whether one solution in the query or multiple) needs to be accompanied by the following information:

  • Solution Summary (ie; 2017-06 Security Only update…)
  • Solution “FIX” (Ie; download and apply patch from website.com)
  • Vulnerabilities that are remediated with Solutions ID (IE; Microsoft CVE-2017-8464: LNK REMOTE…)
    *** Asset Information:**
  •   Asset names, Asset IP, Asset Operating System (Microsoft Windows Server 2016), Asset OS Version (1067,SP1...), Asset OS Product (x86_64), Asset OS Vendor (Microsoft,Linux)
    

Thank you for any help; after a few months of back and forth, we hope to move forward with a report that will help target our biggest issues with items that both teams need to progress.

Here’s a basic query that grabs the solution + vuln + asset data that you mentioned here.

SELECT ds.summary AS Solution_Summary, ds.fix, dv.title AS Vulnerability_Title,
       da.host_name, da.ip_address, dos.description AS Operating_System, dos.vendor,
       dos.version AS OS_Version, dos.architecture
FROM asset_vulnerability_best_solution avbs
JOIN dim_solution ds ON avbs.solution_id = ds.solution_id
JOIN dim_vulnerability dv ON avbs.vulnerability_id = dv.vulnerability_id
JOIN dim_asset da ON avbs.asset_id = da.asset_id
JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id

There’s no filtering on this query because that’ll depend on how you’re looking to prioritize these solutions. Any idea on how the team wanted to do that? You could do it by assets, or by the solution itself, or by the date the vulnerability was discovered. There’s a bunch of options.

Thanks Holly. With all of the asset information in place, I believe I’d want something like the following to filter by the solution id (which is known by nexpose_id). I believe I took a chunk of this from a premade sql query on the insightvm site but change the where

WHERE solution_id IN (

SELECT solution_id

FROM dim_solution_highest_supercedence

WHERE superceding_solution_id IN (

SELECT solution_id

FROM dim_solution

WHERE nexpose_id = '(this is where the solution id from the project section would go)' 
)

)

If we could also do more than one solution at a time, that would remove this report running 9 extra times (one report per solution). Example of the solution ID could be “msft-kb4019213-2913676b-33a1-4557-9160-f36bfed5f294”

*image *