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.

@holly_wilsey
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

@holly_wilsey

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.

@holly_wilsey

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.