SQL Query Export Example: Vulnerability Exceptions

Document created by erikc Employee on Jan 2, 2014Last modified by erikc Employee on Jul 7, 2014
Version 4Show Document
  • View in full screen mode

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, 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.

Attachments

    Outcomes