Last updated at Sat, 19 Aug 2017 18:22:28 GMT

Working in support, we receive a lot of request of extracting the vulnerability exception data from the UI. With this query noted below and using our new SQL Query export feature, you'll finally be able to obtain that data.

This query will provide you with:

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

To know more about our SQL Query Export feature, please check out the introductory blog.