SQL Query Export Example: Policy Reporting

Document created by erikc Employee on Apr 2, 2014
Version 1Show Document
  • View in full screen mode

With policy scanning becoming increasingly crucial for customers who require compliance, the option to report on policy results has become a very popular Support request.  Thankfully, our Development team has been making huge strides to accommodate these requests by introducing new policy tables in the dimensional model.  These new tables can provide a high level view into the results of policy scans against your assets. However, policy reporting is still a work in progress; our Development team is still working toward providing more granular information on policy scan results.

 

The following charts provide descriptions of the policy tables and their columns:

 

fact_asset_policy

Level of Grain: A policy result on an asset

Fact Type: accumulating snapshot

Description: This table provides an accumulating snapshot of policies test results on an asset. It displays a record for each policy that was tested on an asset in its most recent scan. Only policies scanned within the scope of report are included.

Column

Data Type

Nullable

Description

Associated Dimension

asset_id

bigint

No

The identifier of the asset

dim_asset

last_scan_id

bigint

No

The identifier of the scan

dim_scan

policy_id

bigint

No

The identifier of the policy

dim_policy

scope

text

No

The identifier for scope of policy. Policies that are automatically available have "Built-in" scope, whereas policies created by users have scope as "Custom".

 

date_tested

timestamp without timezone

 

The end date and time for the scan of the asset that was tested for the policy, in the time zone specified in the report configuration.

 

passed_rules

bigint

 

The total number of each policy's rules for which the asset passed in the most recent scan.

 

failed_rules

bigint

 

The total number of each policy's rules for which the asset failed in the most recent scan.

 

not_applicable_rules

bigint

 

The total number of each policy's rules that were not applicable to the asset in the most recent scan.

 

rule_compliance

numeric

 

The ratio of PASS results for the rules to the total number of rules in each policy.

 

 

fact_asset_scan_policy

Level of Grain: A policy result for an asset in a scan

Fact Type: transaction

Description: This table provides the details of policy test results on an asset during a scan. Each record provides the policy test results for an asset for a specific scan. Only policies within the scope of report are included.

Columns

Column

Data Type

Nullable

Description

Associated Dimension

asset_id

bigint

No

The identifier of the asset

dim_asset

scan_id

bigint

No

The identifier of the scan

dim_scan

policy_id

bigint

No

The identifier of the policy

dim_policy

scope

text

No

The identifier for scope of policy. Policies that are automatically available have "Built-in" scope, whereas policies created by users have scope as "Custom".

 

date_tested

timestamp without timezone

 

The end date and time for the scan of the asset that was tested for the policy, in the time zone specified in the report configuration.

 

passed_rules

bigint

 

The total number of each policy's rules for which the asset passed in the most recent scan.

 

failed_rules

bigint

 

The total number of each policy's rules for which the asset failed in the most recent scan.

 

not_applicable_rules

bigint

 

The total number of each policy's rules that were not applicable to the asset in the most recent scan.

 

rule_compliance

numeric

 

The ratio of PASS results for the rules to the total number of rules in each policy.

 

 

dim_policy

Description: This is the dimension for all metadata related to a policy. It contains one record for every policy that currently exists in Nexpose.

Type: slowly changing (Type I)

Columns

Column

Data Type

Nullable

Description

policy_id

bigint

No

The identifier of the policy.

scope

text

No

The identifier for scope of policy. Policies that are automatically available have "Built-in" scope, whereas policies created by users have scope as "Custom".

title

text

No

The title of the policy as visible to the user.

description

text

 

A description of the policy.

total_rules

bigint

 

The sum of all the rules within the policy

benchmark_name

text

 

The name of the collection of policies sharing the same source data to which the policy belongs. It includes metadata such as title, name, and applicable systems.

benchmark_version

text

 

The version number of the benchmark that includes the policy

category

text

 

A grouping of similar benchmarks based on their source, purpose, or other criteria. Examples include FDCC, USGCB, and CIS.

category_description

text

 

A description of the category

 

dim_policy_rule

Description: This is the dimension for all the metadata for each rule within a policy. It contains one record for every rule within each policy.

Type: slowly changing (Type I)

Columns

Column

Data Type

Nullable

Description

policy_id

bigint

No

The identifier of the policy.

scope

text

No

The identifier for scope of policy. Policies that are automatically available have "Built-in" scope, whereas policies created by users have scope as "Custom".

rule_id

bigint?

No

The identifier of the rule.

title

text

 

The title of the rule, for each policy, that is visible to the user. It describes a state or condition with which a tested asset should comply.

description

text

 

A description of the rule.

 

 

To demonstrate the capability of this feature, please observe the following example query:

 

SELECT da.ip_address, da.host_name, fasp.scope, fasp.date_tested, fasp.passed_rules, fasp.failed_rules, fasp.not_applicable_rules, fasp.rule_compliance,  dp.title, dp.total_rules, dp.benchmark_name, dp.category,

dpr.title, dpr.description, dpr.scope

FROM fact_asset_scan_policy fasp

JOIN dim_policy dp using (policy_id)

JOIN dim_policy_rule dpr using (policy_id)

JOIN dim_asset da using (asset_id)

order by da.ip_address

 

This Query Will Provide:

  • 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.


To learn more about our SQL Query Export feature, please check out the introductory blog.

To learn more about reporting Datamodel, please check the Nexpose help under “Creating reports based on SQL queries” in the “Working with reports” section.

1 person found this helpful

Attachments

    Outcomes