SQL example - vulnerability exceptions

This example query extracts vulnerability exception data.

This query will provide you with:

  • Exception Scope
  • Additional Comments
  • Submitted Date
  • Submitted By
  • Review Date
  • Review By
  • Review Comments
  • Expiration Date
  • Status of Exception
  • Reason
  • Vulnerability Title
  • Nexpose ID
sql
1
SELECT
2
   CASE
3
      WHEN dve.scope_id = 'G' THEN 'All instances across all assets'
4
      WHEN dve.scope_id = 'D' THEN 'All instances on asset on asset "' || COALESCE(da.host_name, da.ip_address) ||  ' "'
5
      WHEN dve.scope_id = 'I' THEN 'Specific instance on asset "' || da.host_name || 'or' || da.ip_address || ' "'
6
      WHEN dve.scope_id = 'S' THEN 'All instances on this site "' || ds.name ||  ' "'
7
   END AS exceptionscope, COALESCE(dve.additional_comments,'') as additional_comments, dve.submitted_date, dve.submitted_by,
8
   dve.review_date, dve.reviewed_by, dve.review_comment, dve.expiration_date, des.description as status, der.description as reason,
9
   dv.title, dv.nexpose_id
10
FROM dim_vulnerability_exception dve
11
   LEFT OUTER JOIN dim_asset da USING (asset_id)
12
   LEFT OUTER JOIN dim_site ds USING (site_id)
13
   JOIN dim_exception_status des on des.status_id = dve.status_id
14
   JOIN dim_exception_reason der on der.reason_id = dve.reason_id
15
   JOIN dim_exception_scope descope on descope.scope_id = dve.scope_id
16
   JOIN dim_vulnerability dv on dv.vulnerability_id = dve.vulnerability_id
17
WHERE dve.expiration_date >= current_date or dve.expiration_date is null
18