How to add "proof" column in benchmark test sql based report

Hi Team

We have custom SQL based query report for mapping CIS benchmark test compliance status. Need your kind inputs on how to append “proof” column in this existing custom report:-

with dpr_1 as (
select
dp.title as policy_title,
dp.benchmark_version as bm_vers,
dpr.title as rule_title,
dpr.rule_id
from dim_policy_rule dpr
join dim_policy dp
on dp.policy_id = dpr.policy_id
and dp.scope = dpr.scope
and dp.title like ‘Custom%CIS %’
),

fapr_1 as (
select * from (
select
fap.asset_id,
fap.policy_id,
fap.scope,
fapr.rule_id,
fap.date_tested,
fapr.status_id,
fapr.compliance,
fapr.override_id,
max(fap.date_tested) over (partition by fap.asset_id) as max_date
from fact_asset_policy fap
join fact_asset_policy_rule fapr using (asset_id,policy_id)
where fap.scope = ‘Custom’
and (fap.date_tested > now() - INTERVAL ‘30 days’)
) x
where date_tested = max_date
),

tags_os as (
select
da.asset_id,
dt.tag_name as os
from dim_asset da
join dim_tag_asset dta using (asset_id)
join dim_tag dt using (tag_id)
where tag_name like ‘:OS: %’
),

tags as (
select *
from tags_os
),

fapr_2 as (
select
fapr_1.*,
lower(split_part(da.host_name,’.’,1)) as asset_name,
da.ip_address as “IP”,
dprs.status_id,
dprs.description as “Result”,
dpr_1.rule_title,
dpr_1.bm_vers as “Benchmark Version”,
dpr_1.policy_title,
right(tags.os, -5) as “OS”
from fapr_1
left join tags
on tags.asset_id = fapr_1.asset_id
join dim_asset da
on da.asset_id = fapr_1.asset_id
join dim_policy_result_status dprs
on dprs.status_id = fapr_1.status_id
join dpr_1
on dpr_1.rule_id = fapr_1.rule_id
order by date_tested DESC
),

fapr_3 as (
select distinct on (asset_name, rule_title, policy_title)
asset_name as “Asset”,
“IP”,
“Result”,
substring(rule_title, ‘^([0-9.]+[0-9]+)[.:]*’) as rule_id,
rule_title as “Rule”,
policy_title as “Standard”,
“Benchmark Version”,
to_char(date_tested, ‘YYYY/MM/DD HH24:MI:SS’) as “Test Date”,
“OS”
from fapr_2
order by asset_name, rule_title, policy_title, “Test Date” DESC
)

select *
from fapr_3
where (“Standard” like ‘Custom - CIS Oracle Database 11g R2%’)
or (“Standard” like ‘Custom - CIS Oracle Database 12c%’)
or (“Standard” like ‘Custom - CIS Oracle Database 19c%’)
or (“Standard” like ‘Custom - CIS Microsoft SQL Server 2012%’)
or (“Standard” like ‘Custom - CIS Microsoft SQL Server 2014%’)
or (“Standard” like ‘Custom - CIS Microsoft SQL Server 2016%’)
or (“Standard” like ‘Custom - CIS Microsoft SQL Server 2017%’)

Hi there! I have done quite a lot of CIS scanning. I don’t believe there is any ‘proof’ available. That is normally only vulnerability scanning that has the proof/solution available.

You would just need to add fapr.proof to the first temp table of fapr_1, nothing to fapr_2 because it’s already using *. Then add fapr_2.proof to the fapr_3 table. That should be it.

Thanks John but when we add “fapr.proof” - its giving output as “The status of compliance for this rule was derived from its parent rule(s).”

However, the GUI console shows the values correctly. Any idea how can we get exact export of proof in csv format for policy scans?

@john_hartman - We intend to know which DB name has contributed to failing the rule under consideration of a policy-based scan. Can this information be exported in a SQL based CSV report? We are running DB scans on a server with multiple DBs running on it.