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

**URL:** <https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173>\
**Category:** SQL Queries\
**Created:** [June 13, 2020, 6:47pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173 "2020-06-13T18:47:01Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 13, 2020, 6:47pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/1 "2020-06-13T18:47:01Z")

</div>

Hi There ,

I have been trying to export the Policy scan result for each asset with remediation and rationale, I would like add that to the below query , Any help would be appreciated.

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,

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

Thank you.  
Manny

---

<div class="post-metadata">

**Author:** ![zyoutz](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/zyoutz/32/56_2.png) [@zyoutz](https://discuss.rapid7.com/u/zyoutz)\
**Post date:** [June 16, 2020, 2:20pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/2 "2020-06-16T14:20:33Z")

</div>

@manny_singh When exporting remediation and rationale for policy findings, you will need to use the [dim\_policy\_rule](https://insightvm.help.rapid7.com/docs/understanding-the-reporting-data-model-dimensions#section-dim-policy-rule) table. The following query is an example of the type of information you could pull:

```auto
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](http://checklists.nist.gov/xccdf/1.2)” xmlns:xccdf=“[http://checklists.nist.gov/xccdf/1.2](http://checklists.nist.gov/xccdf/1.2)” xmlns:xhtml=“[http://www.w3.org/1999/xhtml](http://www.w3.org/1999/xhtml)” xmlns:xsi=“[http://www.w3.org/2001/XMLSchema-instance](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](http://checklists.nist.gov/xccdf/1.2)” xmlns:xccdf=“[http://checklists.nist.gov/xccdf/1.2](http://checklists.nist.gov/xccdf/1.2)” xmlns:xhtml=“[http://www.w3.org/1999/xhtml](http://www.w3.org/1999/xhtml)” xmlns:xsi=“[http://www.w3.org/2001/XMLSchema-instance](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](http://checklists.nist.gov/xccdf/1.2)” xmlns:xccdf=“[http://checklists.nist.gov/xccdf/1.2](http://checklists.nist.gov/xccdf/1.2)” xmlns:xhtml=“[http://www.w3.org/1999/xhtml](http://www.w3.org/1999/xhtml)” xmlns:xsi=“[http://www.w3.org/2001/XMLSchema-instance](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](http://checklists.nist.gov/xccdf/1.2)” xmlns:xccdf=“[http://checklists.nist.gov/xccdf/1.2](http://checklists.nist.gov/xccdf/1.2)” xmlns:xhtml=“[http://www.w3.org/1999/xhtml](http://www.w3.org/1999/xhtml)” xmlns:xsi=“[http://www.w3.org/2001/XMLSchema-instance](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\> |

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 16, 2020, 6:21pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/3 "2020-06-16T18:21:16Z")

</div>

Continuing the discussion from [SQL query to export Policy scan results with Remediation, Rationale and Proof](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173):

Hi Zac ,

You are a hero , made our life easier by providing this query , we are trying to implement CIS control in our environment and need to better understand scan results and export them to CSV. One More question i have , if want we want to pull out what labeled as Checks in console in this query what would i need to add ?  
You really made our Day , We have been trying to get this results out and was not able to.  
Regards,  
Manny

---

<div class="post-metadata">

**Author:** ![zyoutz](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/zyoutz/32/56_2.png) [@zyoutz](https://discuss.rapid7.com/u/zyoutz)\
**Post date:** [June 16, 2020, 6:26pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/4 "2020-06-16T18:26:24Z")

</div>

Glad to help @manny_singh!

For your follow up question, can you expand a bit more on what you meant by “if want we want to pull out what labeled as Checks in console in this query what would i need to add”.

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 16, 2020, 6:59pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/6 "2020-06-16T18:59:11Z")

</div>

![checks2](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/1X/44455aa0278cfb9ee82a5472a298920b40a652aa.png)

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 16, 2020, 10:18pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/7 "2020-06-16T22:18:46Z")

</div>

@zyoutz  
Hi Zac,  
I looked at the dim\_policy\_rule table, I believe it is check\_existence and  
Check\_results. Correct me if i am wrong? but cannot figure out how add to them to the query you gave us. I wanted to export the expected check value and the resulted value from policy scan.  
Regards,

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 17, 2020, 6:56pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/8 "2020-06-17T18:56:19Z")

</div>

@zyoutz  
Please help , Thank you. 😟  
Regards  
Manny

---

<div class="post-metadata">

**Author:** ![zyoutz](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/zyoutz/32/56_2.png) [@zyoutz](https://discuss.rapid7.com/u/zyoutz)\
**Post date:** [June 17, 2020, 7:04pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/9 "2020-06-17T19:04:43Z")

</div>

@manny_singh Still not sure I completely follow; however, on the [fact\_asset\_policy\_rule](https://insightvm.help.rapid7.com/docs/understanding-the-reporting-data-model-facts#section-fact-asset-policy-rule) table there is a `compliance` column that indicates whether the asset is compliant for the particular rule.

This will return either `true` or `false` and the table is already being used in the query above as `fapr.compliance`. I did notice in the example query I provided you it is actually filtering by `fapr.compliance = false` so only rules that assets aren’t compliant for are being returned at the moment.

If instead you are looking for more details about the rule (description, rationale, remediation) then the [dim\_policy\_rule](https://insightvm.help.rapid7.com/docs/understanding-the-reporting-data-model-dimensions#section-dim-policy-rule) table will have more of those details.

---

<div class="post-metadata">

**Author:** ![zyoutz](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/zyoutz/32/56_2.png) [@zyoutz](https://discuss.rapid7.com/u/zyoutz)\
**Post date:** [June 17, 2020, 10:42pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/11 "2020-06-17T22:42:11Z")

</div>

@manny_singh Unfortunately I’m not aware of a way to get the values of the checks through SQL query reports. I’ll see if anyone else has any suggestions for you or maybe someone else in the community might be able to chime in.

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 17, 2020, 11:14pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/12 "2020-06-17T23:14:19Z")

</div>

@zyoutz Thanks for all your Help.

Regards,  
Manny

---

<div class="post-metadata">

**Author:** ![Aniket-Menon](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/aniket-menon/32/76_2.png) [@Aniket-Menon](https://discuss.rapid7.com/u/Aniket-Menon)\
**Post date:** [June 18, 2020, 12:03pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/13 "2020-06-18T12:03:40Z")

</div>

@manny_singh - You will need to setup a Database Warehouse (external) to reference those values you are looking for. Instructions can be found here: [Configure Data Warehouse](https://insightvm.help.rapid7.com/docs/configuring-data-warehousing-settings)

After that, you will need to construct the SQL query using the schema found here: [Data Warehouse Schema](https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html)

I think you will need to reference these tables in your SQL query to get the data you are looking for: [fact\_asset\_policy\_rule\_check](https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html#fact_asset_policy_rule_check) and [fact\_asset\_policy\_rule\_test](https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html#fact_asset_policy_rule_test)

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [June 18, 2020, 1:23pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/14 "2020-06-18T13:23:50Z")

</div>

@Aniket-Menon  
I looked at the database schema already , to me it looks like the table that needs to be referenced is dim\_policy\_rule\_test , But i am totally confused how to construct this query.

I looked at the option of connecting to database using sql query editor tools. Can i not connect directly to the database ?

Regards,  
Manny

---

<div class="post-metadata">

**Author:** ![Aniket-Menon](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/aniket-menon/32/76_2.png) [@Aniket-Menon](https://discuss.rapid7.com/u/Aniket-Menon)\
**Post date:** [June 18, 2020, 1:32pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/15 "2020-06-18T13:32:52Z")

</div>

@manny_singh - you can connect directly once you setup the Data Warehouse. It will have to be an external Postgres database. This will be a separate database from the one that comes with the Nexpose / InsightVM console.

---

<div class="post-metadata">

**Author:** ![brian\_w\_gray](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/brian_w_gray/32/112_2.png) [@brian\_w\_gray](https://discuss.rapid7.com/u/brian_w_gray)\
**Post date:** [June 18, 2020, 2:34pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/16 "2020-06-18T14:34:49Z")

</div>

Manny, something that is important to remember is that for the reports within the ui you are essentially just working within a view. You can force access to the db directly through modifying authentication configurations but it’s likely not what you’re hoping for. If you are looking for full db query access the warehouse option that was mentioned by Aniket is one of your safest options and provides the \_test tables you are trying to use. Otherwise using the reporting view you’ll be restricted to what is presented in the view’s datamodel which is different from what is available in the warehouse option. [https://nexpose.help.rapid7.com/docs/understanding-the-reporting-data-model-dimensions](https://nexpose.help.rapid7.com/docs/understanding-the-reporting-data-model-dimensions) for dimensions and [https://nexpose.help.rapid7.com/docs/understanding-the-reporting-data-model-facts](https://nexpose.help.rapid7.com/docs/understanding-the-reporting-data-model-facts) for facts.  
The view can technically be modified on the console but it’s not something that I would recommend in most cases. It doesn’t look like the idea portal exists anymore but I would still suggest filing a feature request to have the information made available in the view for reports. [https://kb.help.rapid7.com/docs/suggesting-a-feature-or-enhancement](https://kb.help.rapid7.com/docs/suggesting-a-feature-or-enhancement)

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [September 25, 2021, 8:00pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/19 "2021-09-25T20:00:42Z")

</div>

Hi @a9315024_a9315024 ,

I updated the query again to remove the spaces that was result of removing xhtml tagged data and also fixed the proof field which was not parsing out all text with the use of htmlToText.

```auto
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, (regexp_replace(fapr.proof,'<[^>]*>|^\s+|\s\s+','','g')) as proof, fapr.compliance,
  dpr.severity, (regexp_replace(dpr.rationale,'<[^>]*>|^\s+|\s\s+','','g')) AS rationale, (regexp_replace(dpr.remediation,'<[^>]*>|^\s+|\s\s+','','g')) 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

```

Hope this helps !  
Manny

---

<div class="post-metadata">

**Author:** ![anon26205435](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/a/db5fbb/32.png) [@anon26205435](https://discuss.rapid7.com/u/anon26205435)\
**Post date:** [October 7, 2021, 2:04pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/20 "2021-10-07T14:04:24Z")

</div>

You should be able to use the proofAsText function to remove the HTML tags from the data.

---

<div class="post-metadata">

**Author:** ![anon26205435](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/a/db5fbb/32.png) [@anon26205435](https://discuss.rapid7.com/u/anon26205435)\
**Post date:** [October 8, 2021, 6:01am UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/22 "2021-10-08T06:01:20Z")

</div>

Ah I see, I did not know that. Thanks 🙂

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [November 17, 2021, 2:50am UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/23 "2021-11-17T02:50:43Z")

</div>

Hi @zyoutz @holly_wilsey @tyler_schmidtke ,

Team , I have been trying to get the result from policy scan dated back in 2021-10-28 using the the query and tried using WHERE clause and it is still showing results from most recent date ( 2021-11-16.

Clause used – WHERE fapr.date\_tested\< ‘2021-10-30’

What do i need to change in the Query to get the result from 2021-10-28 ?  
Manny

---

<div class="post-metadata">

**Author:** ![manny\_singh](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/m/35a633/32.png) [@manny\_singh](https://discuss.rapid7.com/u/manny_singh)\
**Post date:** [November 18, 2021, 10:36pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/24 "2021-11-18T22:36:30Z")

</div>

@zyoutz @Aniket-Menon @holly_wilsey @tyler_schmidtke , Anyone of you have any suggestion or hints for me?

Thanks

---

<div class="post-metadata">

**Author:** ![holly\_wilsey](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/holly_wilsey/32/78_2.png) [@holly\_wilsey](https://discuss.rapid7.com/u/holly_wilsey)\
**Post date:** [November 19, 2021, 9:33pm UTC](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173/25 "2021-11-19T21:33:36Z")

</div>

Could you post the full query? I double checked the tables/fields against the WHERE clause you posted, but it looks alright at a glance. Seeing the full query might provide some more insight.

[Next page](https://discuss.rapid7.com/t/sql-query-to-export-policy-scan-results-with-remediation-rationale-and-proof/1173.md?page=2)