SQL example - policy reporting

This example query provides:

  • Asset IP Address
  • Asset Host Name
  • Scope of the policy
  • When the policy was tested
  • Total Pass Rules
  • Total Fail Rules
  • Total rules not applicable
  • Rule Compliance
  • Title of the policy
  • Total Rules
  • Benchmark Name
  • The title of the policy as visible to the user.
  • A description of the policy
sql
1
SELECT da.ip_address, da.host_name, fasp.scope, fasp.date_tested, fasp.compliant_rules, fasp.noncompliant_rules, fasp.not_applicable_rules, fasp.rule_compliance,  dp.title, dp.total_rules, dp.benchmark_name, dp.category,
2
3
dpr.title, dpr.description, dpr.scope
4
5
FROM fact_asset_scan_policy fasp
6
7
JOIN dim_policy dp using (policy_id)
8
9
JOIN dim_policy_rule dpr using (policy_id)
10
11
JOIN dim_asset da using (asset_id)
12
13
order by da.ip_address