如何快速构建跨多账号的云CMDB和安全基线检查方案

[profile <profile name>]
MFA_serial=arn:aws-cn:iam::<Account1 id>:MFA/<MFA id>

[Account1]
credential_process=aws-vault exec -j <profile name> --region=cn-north-1
[Account2]
source_profile=orgprefix_Account1
role_arn=arn:aws-cn:iam::<Account2 id>:role/<role id>
[Account3]
source_profile=orgprefix_Account1
role_arn=arn:aws-cn:iam::<Account3 id>:role/<role id>

connection "orgprefix_Account1" {
plugin = "aws"
profile = "Account1"
regions = ["cn-north-1","cn-northwest-1"]
}
connection "orgprefix_Account2" {
plugin = "aws"
profile = "Account2"
regions = ["cn-north-1","cn-northwest-1"]
}
connection "orgprefix_Account3" {
plugin = "aws"
profile = "Account3"
regions = ["cn-north-1","cn-northwest-1"]
}

connection "orgprefix_all" {
type = "aggregator"
plugin = "aws"
connections = ["orgprefix_*"]
}

# 启动交互式查 
Steampipe query
# 统计每个Account中的ec2数 
select Account_id,count(*) as countalb from orgprefix_all.aws_ec2_instance
group by Account_id
# 统计每个Account中的ALB数 
select Account_id,count(*) as countalb from
orgprefix_all.aws_ec2_application_load_balancer group by Account_id
# 统计每个Account中的VPC数 
select Account_id,count(*) as countalb from orgprefix_all.aws_vpc group by
Account_id

select account_id,count(*) as countalb from
org_all.aws_ec2_application_load_balancer group by account_id

select
load_balancer_arns,count(*)
from
org_all.aws_ec2_target_group
cross join jsonb_array_elements(target_health_descriptions) as target
group by load_balancer_arns

select account_id,count(*) as counteip from account_all.aws_vpc_eip group by
account_id

select group_id,ip_permission -> 'FromPort' as OpenPort from
account_all.aws_vpc_security_group cross join
jsonb_array_elements(ip_permissions) as ip_permission where (ip_permission -
>> 'FromPort'='80' and ip_permission ->> 'ToPort'='80') or (ip_permission -
>> 'FromPort'='443' and ip_permission ->> 'ToPort'='443') or (ip_permission
->> 'FromPort'='8080' and ip_permission ->> 'ToPort'='8080') or
(ip_permission ->> 'FromPort'='8081' and ip_permission ->> 'ToPort'='8081')
or (ip_permission ->> 'FromPort'='3128' and ip_permission ->>
'ToPort'='3128') or (ip_permission ->> 'FromPort'='9080' and ip_permission -
>> 'ToPort'='9080')

select eip.*,sg ->> 'GroupId' as sgid,websg.OpenPort from (select
account_id,instance_id,network_interface_id,private_ip_address,public_ip
from account_all.aws_vpc_eip) as eip inner join (select
network_interface_id,groups from account_all.aws_ec2_network_interface) as
eni on eip.network_interface_id=eni.network_interface_id cross join
jsonb_array_elements(eni.groups) as sg inner join (select
group_id,ip_permission -> 'FromPort' as OpenPort from
account_all.aws_vpc_security_group cross join
jsonb_array_elements(ip_permissions) as ip_permission where (ip_permission -
>> 'FromPort'='80' and ip_permission ->> 'ToPort'='80') or (ip_permission -
>> 'FromPort'='443' and ip_permission ->> 'ToPort'='443') or (ip_permission
->> 'FromPort'='8080' and ip_permission ->> 'ToPort'='8080') or
(ip_permission ->> 'FromPort'='8081' and ip_permission ->> 'ToPort'='8081')
or (ip_permission ->> 'FromPort'='3128' and ip_permission ->>
'ToPort'='3128') or (ip_permission ->> 'FromPort'='9080' and ip_permission -
>> 'ToPort'='9080')) as websg on sg ->> 'GroupId'=websg.group_id

select nlb.account_id, nlb.name,nlb.arn from
account_all.aws_ec2_network_load_balancer as nlb inner join
(select load_balancer_arn,port from
account_all.aws_ec2_load_balancer_listener where port in
(80,443,8080,8081,3128,9080)) as listener on
nlb.arn=listener.load_balancer_arn

select account_id, arn, domain_id, domain_name, elasticsearch_version,
enabled, endpoint from account_all.aws_elasticsearch_domain

select account_id, instance_id, instance_type, instance_state from
account_all.aws_ec2_instance where instance_type not like 'a1%' and
instance_type not like 'c5%' and instance_type not like 'c6%' and
instance_type not like 'd3%' and instance_type not like 'dl%' and
instance_type not like 'g4%' and instance_type not like 'g5%' and
instance_type not like 'hpc6%' and instance_type not like 'i3en%' and
instance_type not like 'im4gn%' and instance_type not like 'inf1%' and
instance_type not like 'is4gen%' and instance_type not like 'm5%' and
instance_type not like 'm6%' and instance_type not like 'p3dn%' and
instance_type not like 'p4%' and instance_type not like 'r5%' and
instance_type not like 'r6%' and instance_type not like 't3%' and
instance_type not like 't4%' and instance_type not like 'vt1%' and
instance_type not like 'x2gd%' and instance_type not like 'z1d%'

select instance.account_id,instance.instance_id,ami.*,instance.tags from
account_all.aws_ec2_instance as instance left outer join (select
account_id,image_id,name,platform_details,description from
account_all.aws_ec2_ami_shared where owner_id='141808717104') as ami on
instance.account_id=ami.account_id and instance.image_id=ami.image_id

select ec2.account_id as account_id, ec2.instance_id, mi.computer_name,
mi.platform_name, mi.platform_version, ec2.instance_type,
ec2.instance_state, mi.agent_version, mi.is_latest_version, mi.ping_status,
ec2.launch_time from (select
account_id,instance_id,instance_state,instance_type,launch_time from
account_all.aws_ec2_instance) as ec2 left outer join (select
account_id,agent_version,computer_name,instance_id,is_latest_version,ping_st
atus,platform_name,platform_version from
account_all.aws_ssm_managed_instance) as mi on
ec2.instance_id=mi.instance_id order by account_id

select i.*,r.* from (select rdsinstance.*,rdsinstanceip.ip from (select
account_id, db_instance_identifier, arn, endpoint_address, engine,
engine_version, vpc_id, subnets from account_all.aws_rds_db_instance) as
rdsinstance join (select domain,ip from net.net_dns_record where domain in
(select endpoint_address from account_all.aws_rds_db_instance) and type =
'A' and target is null) as rdsinstanceip on
rdsinstance.endpoint_address=rdsinstanceip.domain) as r join (select
account_id,network_interface_id,private_ip_address,association_public_ip
from account_all.aws_ec2_network_interface where
attached_instance_owner_id='amazon-rds') as i on r.account_id=i.account_id
and (r.ip=i.private_ip_address or r.ip=i.association_public_ip)

select
account_id,network_interface_id,description,private_ip_address,association_p
ublic_ip from account_all.aws_ec2_network_interface where status!='in-use'