Skip to content

feat: Updated query of security_groups_with_open_critical_ports#13854

Merged
kodiakhq[bot] merged 4 commits intomainfrom
private/ron/EC2_19
Oct 11, 2023
Merged

feat: Updated query of security_groups_with_open_critical_ports#13854
kodiakhq[bot] merged 4 commits intomainfrom
private/ron/EC2_19

Conversation

@ronsh12
Copy link
Copy Markdown
Contributor

@ronsh12 ronsh12 commented Sep 13, 2023

A change was made because duplicate rows were inserted into the aws_policy_results table.

@cq-bot cq-bot added the aws label Sep 13, 2023
@ronsh12 ronsh12 mentioned this pull request Sep 13, 2023
Closed
2 tasks
@ronsh12 ronsh12 linked an issue Sep 13, 2023 that may be closed by this pull request
bug: security_groups_with_open_critical_ports.sql for multiple ingress rules #13767
Closed
2 tasks
@yevgenypats yevgenypats requested review from jsonpr and removed request for hermanschaaf and yevgenypats September 26, 2023 01:17
jsonpr
jsonpr suggested changes Oct 9, 2023
View reviewed changes
plugins/source/aws/policies/queries/ec2/security_groups_with_open_critical_ports.sql Outdated
end
end as status
from view_aws_security_group_ingress_rules
group by account_id, resource_id, status
Copy link
Copy Markdown
Contributor

@jsonpr jsonpr Oct 9, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Same comment as PR: #13855 (review)

What happens here when a Security Group has multiple rules - some that pass and some that fail? Will the SG show up twice - where there will be multiple rows for the same SG (but each SG will have 2 rows - one for a pass and one for a fail).

Copy link
Copy Markdown
Contributor Author

@ronsh12 ronsh12 Oct 10, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

OK

@ronsh12 ronsh12 force-pushed the private/ron/EC2_19 branch from 9a5d0ea to f94036d Compare October 10, 2023 18:39
@ronsh12
Copy link
Copy Markdown
Contributor Author

ronsh12 commented Oct 10, 2023

/gen sha=f94036dfe67da98e93e29f9e28beb60943b8cd74 plugin=aws

@cq-bot cq-bot added the website label Oct 10, 2023
jsonpr
jsonpr approved these changes Oct 11, 2023
View reviewed changes
plugins/source/aws/policies/queries/ec2/security_groups_with_open_critical_ports.sql
account_id,
resource_id,
CASE
WHEN SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END) > 0 THEN 'fail'
Copy link
Copy Markdown
Contributor

@jsonpr jsonpr Oct 11, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Approving this for now - let's choose a consistent way across our logic to do aggregate checks like this.

@jsonpr jsonpr added the automerge Automatically merge once required checks pass label Oct 11, 2023
@kodiakhq kodiakhq bot merged commit 6834ee9 into main Oct 11, 2023
@kodiakhq kodiakhq bot deleted the private/ron/EC2_19 branch October 11, 2023 12:04
@cq-bot cq-bot mentioned this pull request Oct 11, 2023
Merged
erezrokah added a commit that referenced this pull request Oct 13, 2023
@cq-bot @erezrokah
chore(main): Release plugins-source-aws v22.15.0 (#14402)
c163eb2 
🤖 I have created a release *beep* *boop*
---


##
[22.15.0](plugins-source-aws-v22.14.0...plugins-source-aws-v22.15.0)
(2023-10-13)


### Features

* Deleted query origin_access_identity_enabled.sql
([#13921](#13921))
([ec77ff8](ec77ff8))
* Introduce spec JSON schema
([#14296](#14296))
([c35f473](c35f473))
* **services:** Support newly added regions
([#14481](#14481))
([672772c](672772c))
* Updated query api_gw_cache_encrypted.sql
([#13860](#13860))
([dca3fe0](dca3fe0))
* Updated query of security_groups_with_access_to_unauthorized_ports
([#13855](#13855))
([efa9e34](efa9e34))
* Updated query of security_groups_with_open_critical_ports
([#13854](#13854))
([6834ee9](6834ee9))
* Updated query rds_databases_and_clusters_should_not_use_a_datab…
([#13936](#13936))
([810078f](810078f))
* Updated query secrets_configured_with_automatic_rotation_should…
([#13934](#13934))
([8b1293c](8b1293c))
* Updated query unused_acls.sql
([#13859](#13859))
([f47df0c](f47df0c))


### Bug Fixes

* Added check for empty string health_status
([#13861](#13861))
([37d8875](37d8875))
* Changed ssm.2 query to reduce redundent rows
([#13933](#13933))
([cee1fab](cee1fab))
* **deps:** Update github.com/cloudquery/arrow/go/v14 digest to d401686
([#14459](#14459))
([7ce40f8](7ce40f8))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.6 ([#14475](#14475))
([83fe7ca](83fe7ca))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.8 ([#14503](#14503))
([4056593](4056593))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.2
([#14378](#14378))
([a2e0c46](a2e0c46))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.3
([#14436](#14436))
([d529e2d](d529e2d))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.4
([#14489](#14489))
([9bb45dc](9bb45dc))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.5
([#14516](#14516))
([2d905bf](2d905bf))
* **deps:** Update module golang.org/x/net to v0.17.0 [SECURITY]
([#14500](#14500))
([9e603d5](9e603d5))
* Fallback to `eu-west-1` if a given buckets LocationConstraint is `EU`
([#14476](#14476))
([f6433e7](f6433e7))
* Lowercase policy statement in query for KMS.1
([#13858](#13858))
([b161fe1](b161fe1))
* Proper schema for `CustomECSListTasksInput.MaxResults`
([#14502](#14502))
([cdaaa99](cdaaa99))
* Redact Code Location and Fall back to other APIs to fully resolve
`aws_lambda_functions`
([#14381](#14381))
([bf402f4](bf402f4))

---
This PR was generated with [Release
Please](https://github.com/googleapis/release-please). See
[documentation](https://github.com/googleapis/release-please#release-please).

Co-authored-by: Erez Rokah <erezrokah@users.noreply.github.com>
hydratim pushed a commit to hydratim/cloudquery that referenced this pull request Oct 20, 2023
@ronsh12 @hydratim
feat: Updated query of security_groups_with_open_critical_ports (clou…
d7180bc 
…dquery#13854)

A change was made because duplicate rows were inserted into the aws_policy_results table.
hydratim pushed a commit to hydratim/cloudquery that referenced this pull request Oct 20, 2023
@cq-bot @erezrokah @hydratim
chore(main): Release plugins-source-aws v22.15.0 (cloudquery#14402)
adb2aba 
🤖 I have created a release *beep* *boop*
---


##
[22.15.0](cloudquery/cloudquery@plugins-source-aws-v22.14.0...plugins-source-aws-v22.15.0)
(2023-10-13)


### Features

* Deleted query origin_access_identity_enabled.sql
([cloudquery#13921](cloudquery#13921))
([ec77ff8](cloudquery@ec77ff8))
* Introduce spec JSON schema
([cloudquery#14296](cloudquery#14296))
([c35f473](cloudquery@c35f473))
* **services:** Support newly added regions
([cloudquery#14481](cloudquery#14481))
([672772c](cloudquery@672772c))
* Updated query api_gw_cache_encrypted.sql
([cloudquery#13860](cloudquery#13860))
([dca3fe0](cloudquery@dca3fe0))
* Updated query of security_groups_with_access_to_unauthorized_ports
([cloudquery#13855](cloudquery#13855))
([efa9e34](cloudquery@efa9e34))
* Updated query of security_groups_with_open_critical_ports
([cloudquery#13854](cloudquery#13854))
([6834ee9](cloudquery@6834ee9))
* Updated query rds_databases_and_clusters_should_not_use_a_datab…
([cloudquery#13936](cloudquery#13936))
([810078f](cloudquery@810078f))
* Updated query secrets_configured_with_automatic_rotation_should…
([cloudquery#13934](cloudquery#13934))
([8b1293c](cloudquery@8b1293c))
* Updated query unused_acls.sql
([cloudquery#13859](cloudquery#13859))
([f47df0c](cloudquery@f47df0c))


### Bug Fixes

* Added check for empty string health_status
([cloudquery#13861](cloudquery#13861))
([37d8875](cloudquery@37d8875))
* Changed ssm.2 query to reduce redundent rows
([cloudquery#13933](cloudquery#13933))
([cee1fab](cloudquery@cee1fab))
* **deps:** Update github.com/cloudquery/arrow/go/v14 digest to d401686
([cloudquery#14459](cloudquery#14459))
([7ce40f8](cloudquery@7ce40f8))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.6 ([cloudquery#14475](cloudquery#14475))
([83fe7ca](cloudquery@83fe7ca))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.8 ([cloudquery#14503](cloudquery#14503))
([4056593](cloudquery@4056593))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.2
([cloudquery#14378](cloudquery#14378))
([a2e0c46](cloudquery@a2e0c46))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.3
([cloudquery#14436](cloudquery#14436))
([d529e2d](cloudquery@d529e2d))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.4
([cloudquery#14489](cloudquery#14489))
([9bb45dc](cloudquery@9bb45dc))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.5
([cloudquery#14516](cloudquery#14516))
([2d905bf](cloudquery@2d905bf))
* **deps:** Update module golang.org/x/net to v0.17.0 [SECURITY]
([cloudquery#14500](cloudquery#14500))
([9e603d5](cloudquery@9e603d5))
* Fallback to `eu-west-1` if a given buckets LocationConstraint is `EU`
([cloudquery#14476](cloudquery#14476))
([f6433e7](cloudquery@f6433e7))
* Lowercase policy statement in query for KMS.1
([cloudquery#13858](cloudquery#13858))
([b161fe1](cloudquery@b161fe1))
* Proper schema for `CustomECSListTasksInput.MaxResults`
([cloudquery#14502](cloudquery#14502))
([cdaaa99](cloudquery@cdaaa99))
* Redact Code Location and Fall back to other APIs to fully resolve
`aws_lambda_functions`
([cloudquery#14381](cloudquery#14381))
([bf402f4](cloudquery@bf402f4))

---
This PR was generated with [Release
Please](https://github.com/googleapis/release-please). See
[documentation](https://github.com/googleapis/release-please#release-please).

Co-authored-by: Erez Rokah <erezrokah@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

1 more reviewer

@jsonpr jsonpr jsonpr approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

automerge Automatically merge once required checks pass

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

bug: security_groups_with_open_critical_ports.sql for multiple ingress rules

3 participants

@ronsh12 @jsonpr @cq-bot