SQL example - vulnerability exception categorization

You can "ignore" vulnerabilities based on several exception categories. You can change the following dve.reason_id field to look at the information from different perspectives:

  • False Positive = 'F'
  • Compensating Control = 'C'
  • Acceptable Use = 'U'
  • Acceptable Risk = 'R'
  • Other = 'O'
sql
1
SELECT da.ip_address as "IP Address", da.host_name as "Host Name", dv.title as "Vulnerability", descope.description as "Scope", dve.additional_comments as "Comments", dest.description as "Exception Type", dve.submitted_by "Submitted by", dve.reviewed_by as "Reviewed by", dve.review_comment as "Reviewer's Comment", dve.expiration_date as "Exception Expiration Date"
2
3
FROM dim_asset da
4
5
  JOIN dim_vulnerability_exception dve using (asset_id)
6
7
  JOIN dim_vulnerability dv using (vulnerability_id)
8
9
  JOIN dim_exception_scope descope using (scope_id)
10
11
  JOIN dim_exception_status dest using (status_id)
12
13
WHERE dve.reason_id = 'F'
14
15
GROUP BY da.ip_address, dv.title, da.host_name, descope.description, dve.additional_comments, dest.description, dve.reason_id, dve.submitted_by, dve.reviewed_by, dve.review_comment, dve.expiration_date
16
17
ORDER BY da.ip_address DESC