Export a list of AWS EC2 Instances as CSV

AWS does not provide a way to export a list of ec2 instances to a csv format, recently there was a need to create such a list to identify what servers were being shared in different projects.

In order to create a excel file with such a list I had to use aws-shell you just need the aws_access_key_id and aws_secret_access_key, we also need jq and sed to process the output which is comming in json.

aws-shell
ec2 describe-instances --region 'us-west-1' 
--region 'us-west-2' 
--query 'Reservations[].Instances[].[LaunchTime,InstanceId,InstanceType,Placement.AvailabilityZone, Tags[?Key==`Name`].Value|[0], State.Name]' 
| jq '.[]' --compact-output 
| sed 's/\[\|\]//g'

and this will give me what I need:

"2018-09-28T14:34:40.000Z","i-0134231e3f5512345","m3.large","us-west-2c","BlacoRa-PROD-Worker-HIGH spawned: Fri Sep 28 07:35:36 PDT 2018","running"
"2018-09-28T14:34:40.000Z","i-0cbac4aafabd12345","m3.large","us-west-2c","BlacoRa-PROD-Worker-HIGH spawned: Fri Sep 28 07:35:36 PDT 2018","running"
"2018-09-28T14:34:40.000Z","i-0c32344b965612345","m3.large","us-west-2c","BlacoRa-QA-Worker-BULK spawned: Fri Sep 28 07:35:47 PDT 2018","running"
"2018-09-28T14:34:40.000Z","i-05cbdf22345c7f518","m3.large","us-west-2c","BlacoRa-QA-Worker-BULK spawned: Fri Sep 28 07:35:44 PDT 2018","running"
"2018-09-28T14:34:40.000Z","i-02512345671342567f","m3.large","us-west-2c","BlacoRa-QA-Worker-BULK spawned: Fri Sep 28 07:35:45 PDT 2018","running"
"2018-09-28T14:34:40.000Z","i-0563eabc3445344195","m3.large","us-west-2c","BlacoRa-QA-Worker-BULK spawned: Fri Sep 28 07:35:43 PDT 2018","running"
"2018-11-07T22:29:13.000Z","i-0e7812fed349834288","m3.large","us-west-2c","BlacoRa-QA-Worker-HIGH spawned: Wed Nov  7 14:30:26 PST 2018","running"
"2018-11-07T22:29:13.000Z","i-0abcd72c72bcfd3429","m3.large","us-west-2c","BlacoRa-QA-Worker-BULK spawned: Wed Nov  7 14:30:26 PST 2018","running"

So we have a tag called Name which we need to map it out to the project, if there were more tags that we need to include on the report, is just a matter of adding them to the query Tags[?Key==Project].Value|[0]