PowerShell脚本导出AWS安全组CSV：按Profile列规整输出需求

我运行以下PowerShell脚本，导出开放22/3389端口给0.0.0.0/0的AWS安全组，但当前输出格式杂乱，需要将数据按AWS账户Profile分列规整展示，示例格式如下：
Profile1 | Profile2 | Profile3 | Profile4
SG data | SG data | SG data | SG data

原脚本 #

$profilelist=("mgmt","poc","prod","sdlc","drivevelocity","partner","dynamicenvironments") #Accounts we currently have in AWS

$regions=("us-east-1","us-east-2","us-west-1","us-west-2","ap-south-1","ap-northeast-2","ap-northeast-3","ap-southeast-1","ap-southeast-2","ap-northeast-1","ca-central-1","eu-central-1","eu-west-1","eu-west-2","eu-west-3","eu-north-1","sa-east-1") #Regions we currently use in AWS

$ports=("22","3389") #SSH port, RDP port

$output="$home/downloads/SG_PublicIP.csv" #Path to find the output csv file

if (Test-Path $output) {
   Remove-Item $output
}

foreach($profile in $profilelist){
    foreach($region in $regions){
      foreach($port in $ports){

    #The following AWS command will list all of the available security groups that contain public IP addresses within range of ports 22 and 3389. It will then output a csv file with the information for every account profile and region available.
          
    aws ec2 describe-security-groups --region $region --profile $profile --filters Name=ip-permission.from-port,Values=$port Name=ip-permission.to-port,Values=$port Name=ip-permission.cidr,Values='0.0.0.0/0' --query "SecurityGroups[*].[GroupName]"  | out-file $output -Encoding ASCII -append 

    }
   }
}

解决方案 #

要实现按Profile分列的规整输出，需要先收集每个Profile下的所有目标安全组，再统一构建CSV结构。修改后的脚本如下：

# 定义配置参数
$profilelist=("mgmt","poc","prod","sdlc","drivevelocity","partner","dynamicenvironments")
$regions=("us-east-1","us-east-2","us-west-1","us-west-2","ap-south-1","ap-northeast-2","ap-northeast-3","ap-southeast-1","ap-southeast-2","ap-northeast-1","ca-central-1","eu-central-1","eu-west-1","eu-west-2","eu-west-3","eu-north-1","sa-east-1")
$ports=("22","3389")
$output="$home/downloads/SG_PublicIP.csv"

# 清除旧文件
if (Test-Path $output) { Remove-Item $output }

# 哈希表存储每个Profile对应的安全组列表
$sgPerProfile = @{}
foreach($profile in $profilelist){
    $sgList = @()
    foreach($region in $regions){
        foreach($port in $ports){
            # 获取当前Profile+Region+Port下的安全组，解析JSON并提取GroupName
            $sgRaw = aws ec2 describe-security-groups --region $region --profile $profile `
                --filters Name=ip-permission.from-port,Values=$port `
                          Name=ip-permission.to-port,Values=$port `
                          Name=ip-permission.cidr,Values='0.0.0.0/0' `
                --query "SecurityGroups[*].GroupName" --output json
            
            # 转换为PowerShell数组，去重避免重复SG（同一SG可能在多端口/多区域匹配）
            if ($sgRaw) {
                $sgNames = $sgRaw | ConvertFrom-Json
                $sgList += $sgNames | Select-Object -Unique
            }
        }
    }
    # 去重后存入哈希表
    $sgPerProfile[$profile] = $sgList | Select-Object -Unique
}

# 找出最大的SG数量，确定CSV的行数
$maxRowCount = ($sgPerProfile.Values | Measure-Object -Property Count -Maximum).Maximum

# 构建CSV内容
$csvContent = @()
# 添加表头
$csvContent += $profilelist -join ","

# 添加每一行数据
for ($i=0; $i -lt $maxRowCount; $i++){
    $row = @()
    foreach($profile in $profilelist){
        # 如果当前Profile有第i个SG，就取它，否则留空
        $sgName = if ($i -lt $sgPerProfile[$profile].Count) { $sgPerProfile[$profile][$i] } else { "" }
        $row += "`"$sgName`"" # 用引号包裹避免SG名称含逗号时出错
    }
    $csvContent += $row -join ","
}

# 写入CSV文件
$csvContent | Out-File $output -Encoding UTF8

关键改动说明 #

1. 数据收集阶段：用哈希表$sgPerProfile存储每个Profile对应的所有安全组，同时通过Select-Object -Unique去重（同一个安全组可能在多端口/多区域匹配）。
2. JSON解析：将AWS CLI的JSON输出转换为PowerShell数组，避免原脚本直接输出的杂乱格式。
3. CSV构建：先确定最大的安全组数量来生成行数，每一行对应各个Profile的一个安全组（无数据则留空），用引号包裹字段值避免名称含逗号导致格式错误。
4. 编码优化：使用UTF8编码写入文件，避免ASCII编码的字符问题。

内容的提问来源于stack exchange，提问作者Chupacabra