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 dim_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”
A month later but I just happened to get back to a project that needed this query. For the priority of solutions, we would be using the key obtained from the project database. An example of what we would use is “msft-kb4580346-a33fefeb-b078-4cde-b9b9-f3473007bef2”. I’m not even sure that this key is, but it’s what given under the project solution “Solution ID” .
There’s a few other fields that we can use I guess… whatever will let us pinpoint to the EXACT solution:
We just have one customer that patches VERY slowly and we literally only give them 3-5 solutions a month.
We will filter by site with the SQL query; no need to write a query for that.
Ahh yes, so the solution ID here is one way you can reference that particular solution. And you’re right, the column in the DB is actually nexpose_id, so you can filter by that via dim_solution if you need to.
Interesting, thanks for catching that. I made an update to one of the tables used in the query, and it seems to be valid now when testing in the console. I updated the query in the post above, if you want to copy/paste and retest.
For reference, I updated asset_vulnerability_best_solution to be dim_asset_vulnerability_best_solution.
@holly_wilsey I tried to fetch the Vulnerability solution along with the fix column based on your query. In the CSV report I notice few Vulnerability Titles are not included through this query. Is there a way to fetch the entire dump? Thanks in advance.
I’m looking for something similar to this. Also an SQL newbie, how can I filter this query to show only assets with a solution that contains “weblogic” in the description? Really we just want to be able to create a report that lists all Assets with a particular solution (weblogic, or something else). There doesn’t seem to be a way to use the Query builder to filter by solution.
*
.
