SQL query to export Policy scan results with Remediation, Rationale and Proof

InsightVM SQL Queries
2

@manny_singh When exporting remediation and rationale for policy findings, you will need to use the dim_policy_rule table. The following query is an example of the type of information you could pull:

SELECT
  dp.policy_id, dp.title as policy_title, dpr.rule_id, dpr.title as policy_rule_title,
  dp.benchmark_name, da.ip_address, da.host_name, dpr.description, dp.category,
  fapr.date_tested, htmlToText(fapr.proof) as proof, fapr.compliance,
  dpr.severity, htmlToText(dpr.rationale) as rationale, htmlToText(dpr.remediation) as remediation
FROM fact_asset_policy_rule fapr
  JOIN dim_policy dp on dp.policy_id = fapr.policy_id
  JOIN dim_policy_rule dpr on dpr.policy_id = fapr.policy_id and fapr.rule_id = dpr.rule_id
  JOIN dim_asset da on da.asset_id = fapr.asset_id
WHERE fapr.compliance = false order by dp.title, dpr.title

This would result in report similar to:

policy_id policy_title rule_id policy_rule_title benchmark_name ip_address host_name description category date_tested proof compliance severity rationale remediation
115 CIS Ubuntu 16.04 LTS Server Benchmark Level One - Server v1.0.0 18615 1.1.1.1 Ensure mounting of cramfs filesystems is disabled xccdf_org.cisecurity.benchmarks_benchmark_1.0.0_CIS_Ubuntu_Linux_16.04_LTS_Benchmark 127.0.0.1 hostname-1 CIS 2018-04-18 07:31:54.021 This is a complex check. Operator = AND * oval-org.cisecurity.benchmarks.o_canonical_ubuntu_linux-def-1002: FAILBased on the following 1 results: * * At least one specified ShellCommand entry must match the given criteria. At least one evaluation must pass.FAIL false unknown <xhtml:p xmlns=“http://checklists.nist.gov/xccdf/1.2” xmlns:xccdf=“http://checklists.nist.gov/xccdf/1.2” xmlns:xhtml=“http://www.w3.org/1999/xhtml” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>Removing support for unneeded filesystem types reduces the local attack surface of the server. If this filesystem type is not needed, disable it.</xhtml:p> <xhtml:div xmlns=“http://checklists.nist.gov/xccdf/1.2” xmlns:xccdf=“http://checklists.nist.gov/xccdf/1.2” xmlns:xhtml=“http://www.w3.org/1999/xhtml” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>xhtml:pxhtml:pEdit or create the file <xhtml:span class=“inline_block”>/etc/modprobe.d/CIS.conf </xhtml:span>and add the following line:</xhtml:p><xhtml:code class=“code_block”>install cramfs /bin/true</xhtml:code>xhtml:p </xhtml:p> </xhtml:p> </xhtml:div>
115 CIS Ubuntu 16.04 LTS Server Benchmark Level One - Server v1.0.0 18615 1.1.1.1 Ensure mounting of cramfs filesystems is disabled xccdf_org.cisecurity.benchmarks_benchmark_1.0.0_CIS_Ubuntu_Linux_16.04_LTS_Benchmark 127.0.0.1 hostname-1 CIS 2018-04-18 07:31:22.962 This is a complex check. Operator = AND * oval-org.cisecurity.benchmarks.o_canonical_ubuntu_linux-def-1002: FAILBased on the following 1 results: * * At least one specified ShellCommand entry must match the given criteria. At least one evaluation must pass.FAIL false unknown <xhtml:p xmlns=“http://checklists.nist.gov/xccdf/1.2” xmlns:xccdf=“http://checklists.nist.gov/xccdf/1.2” xmlns:xhtml=“http://www.w3.org/1999/xhtml” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>Removing support for unneeded filesystem types reduces the local attack surface of the server. If this filesystem type is not needed, disable it.</xhtml:p> <xhtml:div xmlns=“http://checklists.nist.gov/xccdf/1.2” xmlns:xccdf=“http://checklists.nist.gov/xccdf/1.2” xmlns:xhtml=“http://www.w3.org/1999/xhtml” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>xhtml:pxhtml:pEdit or create the file <xhtml:span class=“inline_block”>/etc/modprobe.d/CIS.conf </xhtml:span>and add the following line:</xhtml:p><xhtml:code class=“code_block”>install cramfs /bin/true</xhtml:code>xhtml:p </xhtml:p> </xhtml:p> </xhtml:div>
2 Likes