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 || ' "'7WHEN dve.scope_id = 'P' THEN 'All instances on this Asset Group "' || dag.name || ' "'8END AS exceptionscope, COALESCE(dve.additional_comments,'') as additional_comments, dve.submitted_date, dve.submitted_by,9dve.review_date, dve.reviewed_by, dve.review_comment, dve.expiration_date, des.description as status, der.description as reason,10dv.title, dv.nexpose_id11FROM dim_vulnerability_exception dve12LEFT OUTER JOIN dim_asset da USING (asset_id)13LEFT OUTER JOIN dim_site ds USING (site_id)14LEFT JOIN dim_asset_group dag ON dve.group_id=dag.asset_group_id15JOIN dim_exception_status des on des.status_id = dve.status_id16JOIN dim_exception_reason der on der.reason_id = dve.reason_id17JOIN dim_exception_scope descope on descope.scope_id = dve.scope_id18JOIN dim_vulnerability dv on dv.vulnerability_id = dve.vulnerability_id19WHERE dve.expiration_date >= current_date or dve.expiration_date is null
Did this page help you?