Excluded findings - DWH Reporting


Using our Datawarehouse, I am writing a number of postsql queries so I can more effectively monitor / report on the status of exceptions with our business. Unfortunately, although the capability is in the platform, this is one area where it is lacking and definitely could be improved.

The challenge which I am having is, there are 3 specific categories in use on our platform which are

-Asset Group

When I look at the reports, using a query such as –

DISTINCT da.asset_id
,da.ip_address as “IP Address”
,da.host_name as “Host Name”
,dve.vulnerability_exception_id AS “Exception ID”
,dve.vulnerability_id AS “Vulnerability ID”
,dv.nexpose_id AS “Nexpose ID”
,dv.title AS “Vulnerability Title”
,dv.severity As “Severity”
,dve.scope As “Scope”
,dag.name AS “Risk Accepted Group”
,dve.reason AS “Reason”
,dve.additional_comments as “Comments”
,dve.review_comment as “Review Comments”
,to_char(dve.submitted_date,‘DD/MM/YYYY’) As “Submitted date”
,to_char(dve.expiration_date,‘DD/MM/YYYY’) As “Expiration Date”
dim_vulnerability_exception dve
JOIN dim_vulnerability dv ON dv.vulnerability_id = dve.vulnerability_id
JOIN dim_asset da USING (asset_id)
LEFT JOIN dim_asset_group dag ON dag.asset_group_id = dve.group_id
dve.status NOT LIKE ‘Recalled’
AND dve.expiration_date > now()

I can get a report which shows all the current active exceptions. But what I need to do, is get a report which shows all applied exceptions.

So for example, if I had a group which was called “EOL Servers” which contained all EOL Windows and Linux servers and we had decided to accept the risk of windows 2008 post EOL vulnerabilties as an example. Using the DWH, how could I get alist of all servers which have the exception applied? Is there a specific table which is best to be used to validate that the vulnerability is applicable to a server which is a member of the group used to exclude the findings?

We have a report which does this on our console but the process time is day, so it often fails if its running over a weekend or non busy time when mainteance jobs are working.