How do I get CSV output when querying for IAM Groups and Users?

Question

First time poster here.

I have created a small script that outputs group membership in AWS.

I have got the script to the point that it outputs the desired results but I require help getting the data in a readable format into a csv file.

$awsgroupnames = Get-IAMGroups  
$accountname = Get-IAMAccountAlias 

foreach ($awsgroupname in $awsgroupnames) {
    $groupuser = Get-IAMGroup -groupname ($awsgroupname.GroupName)
    $usernames = $groupuser.users | select UserName 
    write-host $accountname ($awsgroupname.groupname) ($usernames.username)
    }

The output looks like this.

Account Alias - Group - User

aws-dxxxd-mxxer axx-admin xxhie sxxwj bhxxwk hxxv rxxerd cxxx axxw-sf-msdsmt

aws-dxxxd-mxxer aws-casesupport 

aws-dxxxd-mxxer aws-readonly gixxxi mxxxxilj kxxng bxxnst txxxgd rxm kxxxrs

aws-dxxxd-mxxer billing-admin bsdffdk crcccdi

aws-dxxxd-mxxer Billing_Access

aws-dxxxd-mxxer vdc-axxxs mxxxab rxxxd cxxxmi aws-srxxxc-mxxt

aws-dxxxd-mxxer vpc-axxxn mxxxab mxxxj mxxxnr

How can I get this to export out to a csv in the following format?

Account Alias - Group1 - User1

Account Alias - Group1 - User2

Account Alias - Group1 - User3

Account Alias - Group2 - User1

Account Alias - Group2 - User2

Account Alias - Group3 - User1

Account Alias - Group4 -  Blank for no user

and so on.

Anthony Neace · Answer 1 · 2016-10-12T13:16:29.310

Here's an alternative solution for powershell v3.0+ that groups the data in a way that makes extraction to CSV a bit simpler.

Code:

$alias = Get-IAMAccountAlias

Get-IAMGroups | % { 
  $group = $_;
  (Get-IAMGroup -groupname $_.GroupName).Users | % { 
    [pscustomobject]@{ 
      Alias = $alias; 
      GroupName = $group.groupname; 
      UserName = $_.UserName
    } 
  }
} | ConvertTo-Csv -NoTypeInformation > mycsv.csv

Example Output:

"Alias","GroupName","UserName"
"myacct","admins","myuser1"
"myacct","admins","myuser2"
"myacct","users","myuser3"

This script:

iterates the output of Get-IAMGroups to get each GroupName
iterates the output of Get-IAMGroup to get each user, and their UserName
For each user, stores the Alias, GroupName, and UserName in a PSCustomObject with properties of the same name.
Pipes the output collection of PSCustomObject to ConvertTo-CSV with -NoTypeInformation set to get the content in CSV format. Note here that if you want to change the delimiter, there is a -Delimiter flag that you can specify.
Redirects the output to mycsv.csv to write the file out to your file system

A quick note from your attempt that probably gave you trouble: Stay away from using Write-Host to output data beyond your terminal; Write-Host does not write to the output stream. Here's a good blog post on the subject for further reading.

How do I get CSV output when querying for IAM Groups and Users?

1 Answers1