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%’)