---
tags: DW
---
# Embedded rules
## Ingestion of the JSON Compliance reports
The compliance reports are aggregated in the same directory and parsed.
If there are 2 versions of these reports (since the last DW workflow), these will be processed together.
The only option is to have these 2 versions in two different folders. The option of checking for the a presence of a new column will not work
```
if report.has_column(rules.families):
process_new(report)
else:
process_old(report)
```
Other option is to have the new version of the compliance reports (note that we are not removing fields, so it's back compatible with the DW) in prod at least 24 hours before passing the DW to production. So when the new code is in prod, it only encounters non processed reports with the new embedded families.
## Modifications
Assuming the new compliance reports have the format:
```json=
{
"environment_id": "b584a224-fe7e-44d2-ad8c-0270e5b5546a",
"scan_id": "944a624c-a1e6-4818-9830-8562a107a3bf",
"rules": {
"azurerm/app_service/web_app_auth.rego": {
"enabled": true,
"controls": [
"CIS-Azure_v1.1.0_9.1"
],
"families": {
"CIS-Azure_v1.1.0": {
"controls": [
"CIS-Azure_v1.1.0_9.1"
]
},
"UDF 1": {
"direct": true
}
},
"severity": "Medium",
"rule_id": "FG_R00345",
"custom": false
},
(...)
},
"resources": [
(....)
],
"controls": {
"CIS-Azure_v1.1.0_1.23": {
"standard": "CIS-Azure_v1.1.0",
"enabled": true
},
(...)
"custom/Custom rule 3": {
"standard": "Custom",
"enabled": true
}
},
"rule_waivers": []
}
```
### Glue tables
- Create a new entry (e.g. `GlueJSONComplianceReportsV4V20210616`) for the JSON schema of the Compliance reports in `cloudformation/glue-cloud-data-warehouse.yaml`:
- Modify the `rules` entry with the new embedded info:
```yaml
- Name: rules
Type:
"map<string,struct<result:string,enabled:boolean,missing_resources:array<string>,\
controls:array<string>,severity:string,rule_id:string,custom:boolean,\
families:map<string,struct<controls:array<string>,direct:boolean>>>>"
```
### Glue jobs
The current job responsible to parse the compliance reports is:
`glue_scripts/redshift/flatten_compliance_report_v4.py`
This job calls:
`spark_jobs/relationalize_compliance_report_v4/relationalize_compliance_report_v4.py`
and produces the parquet files `rule_results` and `rule_control_mappings`
The code responsible for flattening the JSON and building the `rule_control_mappings` is the following:
```python=
controls = flatten_controls(compliance_reports)
rules = flatten_rules(compliance_reports).cache()
control_rule_map = build_control_rule_map(rules, controls)
```
In the next version, we do want to keep the flattening of the controls - we want to keep the situation where we have controls without rules!
When building the control map, the flattened rules are not needed any longer. All the necessary info is in the embedded families.
```python=
embedded_families = flatten_embedded_families(df)
controls = flatten_controls(df)
control_embedded = build_control_embedded(embedded_families, controls)
```
The `build_control_embedded` function joins embedded families and controls on control name and builds the `rule_control_mappings` table.
Result:
```
+--------------------------------+------------------------------------+------------------------------------+----------------+----------------------+------------------------------------+---------+----------+----+
|id |environment_id |scan_id |family |control_name |rule_id |tenant_id|created_at|dt |
+--------------------------------+------------------------------------+------------------------------------+----------------+----------------------+------------------------------------+---------+----------+----+
|6798d9e3611936e8ad1d6ff1502e3a82|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.4 | |null |null |null|
|e15777d6284835c7b142acaeee938351|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_8.4 | |null |null |null|
|f5b165f3d5d63c7e921e539aea2a11fc|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.13 | |null |null |null|
|e2cc2a60dd943ef9b1c662c0da6bd8f5|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.1.1| |null |null |null|
|7d258e17617438d1b282b6ef4d1d4ea7|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.14 | |null |null |null|
|5eec0a393d51376aab07ee034b343ee8|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_6.2 | |null |null |null|
|51a9aae6a275347291e90f3e9e774b3b|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|Custom |custom/Custom rule 2 |6578e2e6-acec-4a81-9ab7-02a9d9102968|null |null |null|
|c11ce5effc35373e82ddf5796909aef0|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.3 | |null |null |null|
|000332ee7c183138a91a4842027a5e1b|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_1.23 | |null |null |null|
|ed5381178dfc35d4a81a63a5c10fb167|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.8| |null |null |null|
|ff4b946007ab38f79f504ab34cab720b|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.1 | |null |null |null|
|08122e73257b3d2eba3aa8f6fa2fd362|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.2| |null |null |null|
|d9e4a30dbc3d3d9280a064478fae50f8|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.6 | |null |null |null|
|07230927fcb036dda93d567647b0170b|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.12 | |null |null |null|
|c58ed67a00293790b6e1c36a0ffe23b7|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.9| |null |null |null|
|7a2d106ed9113054bd852b5415a4f306|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.10 | |null |null |null|
|637aa1ffd11d361793675f77345a2cb6|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.15 | |null |null |null|
|13bfb00e9aaa393e8a83d3ce0553717f|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_9.1 |FG_R00345 |null |null |null|
|2e59ee424c853a95a0eed2b814fdc0b6|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.6| |null |null |null|
|e37c2d8101413dfeb7002272c0ebea3b|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.7 | |null |null |null|
|64f8163539bf3368a26d43f51dac9bb5|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.16 | |null |null |null|
|c117477cb2ce394b958f66581e9b2829|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.9 | |null |null |null|
|26169a59d6ed34829b130f9ab8eaa9c2|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.1.7| |null |null |null|
|42e887716b5233e99618d4857c4273eb|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|Custom |custom/Custom rule 3 |367477b5-f4f0-4837-bb9c-192f2764cc2f|null |null |null|
|f5f3c51c106834c2954c31c37deb5721|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|UDF 2 | |6578e2e6-acec-4a81-9ab7-02a9d9102968|null |null |null|
|cbe95dcbe21335e1838e8bf914964cc3|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_6.5 | |null |null |null|
|37538acf363331d287ef7ffd6cd8e837|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.15 | |null |null |null|
|fe4026f8152f38f6a4f082626cc2ee16|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_9.4 | |null |null |null|
|6a0184fda11230f299d12905853bc96e|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_9.3 |FG_R00346 |null |null |null|
|0b585fd634fa3975a6d29895ff9ede6e|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|UDF 2 | |FG_R00346 |null |null |null|
|fbd065f4950739d1937daddc7f679aa4|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.5| |null |null |null|
|3a1a99f1fb653bcc9a9d04ed32766201|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.11 | |null |null |null|
|f8bc5cf2de493fa882441238ed6d73a6|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.8 | |null |null |null|
|2cfc245a583d3e47877c3c9e8330d665|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.4| |null |null |null|
|930ad83673193e42baf18d7934a38b09|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_3.1 | |null |null |null|
|feb05acaa18a3ab496497828a671fae7|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.3 | |null |null |null|
|7602aa8b4be839c3b8c2142c6e23abe0|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.16 | |null |null |null|
|38a2faf15bc83642bbc427233ccbe0f5|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.13 | |null |null |null|
|6ff0f1743b3232e58370b29e120d4f6b|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.1 | |null |null |null|
|f04d8686f2ba3a2d999f43b2f89a84ff|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_6.1 | |null |null |null|
|e8a47341bcf63a4582bc0b33ded7e3b2|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.14 | |null |null |null|
|7e60a5b62b0334a5ab498b44ad10a451|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_9.2 |FG_R00346 |null |null |null|
|553fc36f13843b15902f585a5c7e8518|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_7.3 | |null |null |null|
|c4f3ff3fdd203415a05dc4235e6801f5|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_6.3 | |null |null |null|
|97ce1641d2f83940ab02647e5279b495|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.5 | |null |null |null|
|d47e2fddab273739a6f60acfc886794d|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|UDF 3 | |367477b5-f4f0-4837-bb9c-192f2764cc2f|null |null |null|
|a19d362fabcd37a990b7bead84e5d69a|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.1.2| |null |null |null|
|f7f95a68d07f3e71b6665fa7becfb595|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_2.11 | |null |null |null|
|795b19aac64a3909ade4e8f22b2d4746|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.12 | |null |null |null|
|8ed141d614e13e1f81796dd0e05e5dd6|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.7| |null |null |null|
|7f72c6e8e3f03a7cad15e5bea53b9448|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.18 | |null |null |null|
|1a6b347b3e4a3c269520bd4ecdf0606a|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.3| |null |null |null|
|bc8d1a7b98f53f7d96a569e9efe34d9e|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_8.5 | |null |null |null|
|e3769705f6713c169671e7f9f84df3b0|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.1.4| |null |null |null|
|1480906c71db3814bd1ce63fc496b5d0|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_7.2 | |null |null |null|
|a1de05159de63cffa96a87f7d26f9b16|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_6.4 | |null |null |null|
|dccf25c103403bbcb3336852744b4ef7|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.2.1| |null |null |null|
|a581852c10c13085b9b303482ea23325|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_5.1.3| |null |null |null|
|25ed1affa66b347db36dd0a68c66d925|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|CIS-Azure_v1.1.0|CIS-Azure_v1.1.0_4.17 | |null |null |null|
|a4263cca38a438c0b1d3e68aa7b074e9|b584a224-fe7e-44d2-ad8c-0270e5b5546a|944a624c-a1e6-4818-9830-8562a107a3bf|UDF 1 | |FG_R00345 |null |null |null|
+--------------------------------+------------------------------------+------------------------------------+----------------+----------------------+------------------------------------+---------+----------+----+
```
### Code
Branch:
`experiment/compliance-report-v5`
See: https://github.com/LuminalHQ/fugue-data-warehouse/blob/experiment/compliance-report-v5/tests/flatten_compliance_report_v5.ipynb
for the code that generates the control-rule mapping.
### Notes
### If there's time..
Extra column in all main tables that would help to delete duplicated data:
```sql=
created_at datetime default sysdate;
```
Sign in with Wallet
Connect another wallet