bug: ssm/instances_should_have_patch_compliance_status_of_compliant.sql grouping #13775
Description
Is there an existing issue for this?
- I have searched the existing issues
Current Behavior
[SSM.2] https://docs.aws.amazon.com/ja_jp/securityhub/latest/userguide/ssm-controls.html#ssm-2
Current sql does not have grouping by account_id and arn, so many data records got be inserted for aws_policy_result table.
Expected Behavior
Patch it works:
diff --git a/plugins/source/aws/policies/queries/ssm/instances_should_have_patch_compliance_status_of_compliant.sql b/plugins/source/aws/policies/queries/ssm/instances_should_have_patch_compliance_status_of_compliant.sql
index dcb98161e..c4a285d53 100644
--- a/plugins/source/aws/policies/queries/ssm/instances_should_have_patch_compliance_status_of_compliant.sql
+++ b/plugins/source/aws/policies/queries/ssm/instances_should_have_patch_compliance_status_of_compliant.sql
@@ -1,4 +1,16 @@
insert into aws_policy_results
+with patch_compliance_status_groups as(
+ select
+ instance_arn,
+ status
+ from
+ aws_ssm_instance_compliance_items
+ where
+ compliance_type = 'Patch'
+ group by
+ instance_arn,
+ status
+)
select
:'execution_time' as execution_time,
:'framework' as framework,
@@ -7,9 +19,8 @@ select
aws_ssm_instances.account_id,
aws_ssm_instances.arn,
case when
- aws_ssm_instance_compliance_items.compliance_type = 'Patch'
- and aws_ssm_instance_compliance_items.status is distinct from 'COMPLIANT'
+ patch_compliance_status_groups.status is distinct from 'COMPLIANT'
then 'fail' else 'pass' end as status
from
aws_ssm_instances
-inner join aws_ssm_instance_compliance_items on aws_ssm_instances.arn = aws_ssm_instance_compliance_items.instance_arn
+inner join patch_compliance_status_groups on aws_ssm_instances.arn = patch_compliance_status_groups.instance_arn
\ No newline at end of file
CloudQuery (redacted) config
N/A
Steps To Reproduce
No response
CloudQuery (redacted) logs
N/A
CloudQuery version
main
Additional Context
No response
Pull request (optional)
- I can submit a pull request
Metadata
Metadata
Assignees
Labels
No labels