SQL query for Software versions , related vulnerabilities and Solutions

Please help creating the query.

Software and version info can be queried with dim_asset_software and dim_software. For “related vulnerabilities” do you mean vulnerability findings for those assets? If you have an existing query you’re working with it’d help us see what you’re looking to accomplish.

Thank you for replying me back , I am trying to find way to attach the vulnerabilities associated with different versions of software , For Example: Java
Name of Sofware, Vendor , number of asset has the sofware, version of software, known vulnerabilities associted with version, total number of vulnerabilities, nexpose cve id , CVV score , Exloitable (Yes/No) , Malware , Date the vulnerability first published , Date last discovered and Solution. Below is the query that i have
SELECT count(da.asset_id) as asset_count, ds.vendor, ds.name as software_name, ds.family, ds.version
FROM dim_asset_software das
JOIN dim_software ds using (software_id)
JOIN dim_asset da on da.asset_id = das.asset_id
GROUP BY ds.vendor, ds.name, ds.family, ds.version, ds.cpe
ORDER BY asset_count DESC


Any suggestions , Holly?

To be honest I’m not sure if there’s a way to directly tie vulnerabilities to software. You can do a join in your existing query with fact_asset_vulnerability_finding and dim_vulnerability and retrieve additional vulnerability details that way. That’ll allow you to see info about vulnerabilities for assets whose software you’re querying.


Thank you Holly, I believe there should be way since when nexpose scans , it does the scan of the software on the machine and discover vulnerabilities by performing checks on the systems , Does someone on your team knows how to achieve this ? this will greatly help in reducing vulnerabilities and vulnerable software from environment . Let’s see who can take the challenge in the community !

Hi @manny_singh,

To echo @holly_wilsey’s sentiment, there isn’t a place in the reporting data model that I’m aware of that has this correlation. The closest thing that exists that comes to mind would be dim_solution.applies_to; however, this is not going to provide the same level of normalization of the dim_software table. Accomplishing what you’re looking for would likely require writing a program to normalize the data once it is retrieved from the database.