I’ve been asked to create a report that lists high/medium/low for missing patches. I’ve tried to make something work with the standard reports in InsightVM but haven’t gotten exactly what my director is looking for. I’m thinking I might need to do a SQL Query but I don’t have much experience there or know where to start.
Is it possible to create an Asset Group of missing patches then use a report to scope that Asset Group?
Has anyone had any luck with anything similar? Thanks in advance.
It’s definitely possible to create a custom SQL report that scopes by asset groups (or various other types of data). I’m not sure you’d be able to scope that asset group the exact way you want, though.
I kinda talked about this in this post, but I don’t think there’s a way to be 100% certain that a vulnerability/vuln solution is a patch versus something like a config issue. You can filter to a degree based on solution type, which can be things like “PATCH” or “ROLLUP” or “WORKAROUND.” There’s also vulnerability categories for further filtering. You can see which ones exist if you go into Scan Template Configuration and then scroll down to Vulnerability Checks. The query in the post I linked shows how that filtering is done.
I hope that helps somewhat, but if not maybe you can provide some more details and we can try to figure something out.
Thanks for the reply. I’ve been reading and trying to work on my own some.
I have been able to create and run a query that pulls total assets, vulnerabilities, and severity for given/named asset groups (we have created). So that is part of what I’ve been tasked with. But also list specific assets (instead of just a number total) and list Machine/Asset name, OS, IP, Severity, Vulnerability
when I run the SQL query from the post you link, it doesn’t validate.
@dylan_prichard
Are you working with the data warehouse or trying to query to the console directly?
Sorry Jacob, I didn’t get notice of your reply.
I’m unsure, I’m using the SQL builder within the Reports section of InsightVM. I found this query that returns a lot of the information I am looking for. Still trying to figure out how to tie a specific IP/Host to which exact vulnerabilities it has.
I think this might help you with creating your query. Once you put this sql query in, you can then select the desired Asset Group via the “Select Sites, Assets, Asset Groups or Tags” settings. You can also apply the vulnerability filter of include specific (i.e. Microsoft Patch) from the web reporting interface. If you copy/paste from here, you might have to delete the quotations (") and type them in manually if you experience errors.
Query:
SELECT da.ip_address,da.host_name,dos.description,vuln.title AS "Vulnerability Title", vuln.severity AS "Severity" FROM fact_asset_vulnerability_finding AS favf JOIN dim_vulnerability AS vuln ON favf.vulnerability_id = vuln.vulnerability_id JOIN dim_asset_group_asset AS daga ON favf.asset_id = daga.asset_id JOIN dim_asset_group AS dag ON daga.asset_group_id = dag.asset_group_id JOIN dim_scope_asset_group AS dsag ON dag.asset_group_id = dsag.asset_group_id JOIN dim_asset AS da ON favf.asset_id = da.asset_id JOIN dim_operating_system as dos ON dos.operating_system_id = da.operating_system_id WHERE favf.vulnerability_instances > 0 GROUP BY vuln.title, da.ip_address, dag.name, vuln.severity, da.host_name,dos.description ORDER BY dag.name