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
      WHEN dve.scope_id = 'P' THEN 'All instances on this Asset Group "' || dag.name ||  ' "'
8
   END AS exceptionscope, COALESCE(dve.additional_comments,'') as additional_comments, dve.submitted_date, dve.submitted_by,
9
   dve.review_date, dve.reviewed_by, dve.review_comment, dve.expiration_date, des.description as status, der.description as reason,
10
   dv.title, dv.nexpose_id
11
FROM dim_vulnerability_exception dve
12
   LEFT OUTER JOIN dim_asset da USING (asset_id)
13
   LEFT OUTER JOIN dim_site ds USING (site_id)
14
   LEFT JOIN dim_asset_group dag ON dve.group_id=dag.asset_group_id
15
   JOIN dim_exception_status des on des.status_id = dve.status_id
16
   JOIN dim_exception_reason der on der.reason_id = dve.reason_id
17
   JOIN dim_exception_scope descope on descope.scope_id = dve.scope_id
18
   JOIN dim_vulnerability dv on dv.vulnerability_id = dve.vulnerability_id
19
WHERE dve.expiration_date >= current_date or dve.expiration_date is null