I am new to Rapid7 and I have been tasked to generate patch compliance reports and to be honest I am not sure how SQL queries work.
I am looking for a report that would display Asset name, Asset IP address, Asset Operating System, Asset Group name and filter them based on whether a particular Microsoft KB has been applied to it or not.
I would appreciate if someone could help me with this.
Are you looking for a specific KB or a report for devices missing monthly patching?
I am looking for a specific KB that changes each month, I would need to run this each month and provide a KB number and get a list of assets that have the KB applied and assets that are missing the KB.
I would create a Remediation project for assets that have that vulnerability.
You can set a date of when this should be met and the devices that are not compliant.
You would have to create a new one each month.
I’m sure there is a SQL report you could write, but someone who is more familiar with what that sould look like will need to chime in.
I agree with Brandon on this that the Remediation Projects are going to work much better for you than trying to write up and maintain a SQL Query each month. Also the database doesn’t really track wether or not a patch is applied. It only tracks wether or not a vulnerability is present (which can sometimes be because no patch is applied).
With the Remediation Projects, you can set the conditions to be “does not have this vulnerability” e.g. !(vulnerability.title = 'microsoft cve-2017-0014: windows graphics component remote code execution vulnerability') or something like that. And then the scope could be all Windows Assets. Anything that does not have that vulnerability is assumed to have the patch applied and will show as compliant and then everything that HAS the vulnerability is assumed to not have the patch applied and is non-compliant.
Thank you for the inputs.