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
1SELECT2CASE3WHEN dve.scope_id = 'G' THEN 'All instances across all assets'4WHEN dve.scope_id = 'D' THEN 'All instances on asset on asset "' || COALESCE(da.host_name, da.ip_address) || ' "'5WHEN dve.scope_id = 'I' THEN 'Specific instance on asset "' || da.host_name || 'or' || da.ip_address || ' "'6WHEN dve.scope_id = 'S' THEN 'All instances on this site "' || ds.name || ' "'7END AS exceptionscope, COALESCE(dve.additional_comments,'') as additional_comments, dve.submitted_date, dve.submitted_by,8dve.review_date, dve.reviewed_by, dve.review_comment, dve.expiration_date, des.description as status, der.description as reason,9dv.title, dv.nexpose_id10FROM dim_vulnerability_exception dve11LEFT OUTER JOIN dim_asset da USING (asset_id)12LEFT OUTER JOIN dim_site ds USING (site_id)13JOIN dim_exception_status des on des.status_id = dve.status_id14JOIN dim_exception_reason der on der.reason_id = dve.reason_id15JOIN dim_exception_scope descope on descope.scope_id = dve.scope_id16JOIN dim_vulnerability dv on dv.vulnerability_id = dve.vulnerability_id17WHERE dve.expiration_date >= current_date or dve.expiration_date is null18
Did this page help you?