Does Steampipe work with multiple AWS accounts or regions?

[e-gineer]

[e-gineer]

Currently Steampipe works with your default AWS credentials (account & region). So, currently this is one at a time.

You can switch the creds using environment variables though:

AWS_PROFILE=profilename steampipe query

or to change region:

AWS_PROFILE=profilename AWS_REGION=us-west-2 steampipe query

We launched with this as a starting point, but are planning support for multiple accounts and regions. There is an issue open discussing the approach here, please share your input and use cases so we can get it right!

[morkot]

Found this one https://steampipe.io/blog/release-0-2-0. We can query multiple connection and using union can aggregate results.

> select account_id, count(*) as buckets
  from dmi_scranton.aws_s3_bucket
  group by account_id
  
  union
  
  select account_id, count(*) as buckets
  from dmi_albany.aws_s3_bucket
  group by account_id
 
  union
 
  select account_id, count(*) as buckets
  from dmi_global.aws_s3_bucket
  group by account_id;


   +--------------+-------+
   | account_id   | count |
   +--------------+-------+
   | 111222333444 | 2     |
   | 444555666777 | 15    |
   | 888899990000 | 34    |
   +--------------+-------+
    

[morkot]

But this is not scalable approach. I have to repeat the same query over and over for each connection.

Is it possible to have some kind of virtual table that will correspond to multiple connections?

[morkot]

Currently I see only somthing like this

for p in $(echo $profiles); do echo " --> $p"; export AWS_PROFILE="$p"; steampipe query 'select private_ip_address, instance_id, image_id, tags from aws_ec2_instance'; done

[morkot]

So it would be awesome if we could say to steampipe that this will be our virtual table that combines thease connections. Like
in config

org_connection = {
  connection1
  connection2
}

and then

select instance_id from org_connection.aws_ec2_instance

[e-gineer]

Hey @morkot ... We agree that multi-account would be awesome. There is a long discussion of approaches over at turbot/steampipe-plugin-aws#10.

In particular, see the awesome example from @johnsmyth of a postgres function to automatically build multi-account views for all AWS tables.

[davidhammturner]

Hi,

I've been reading through the multi-region discussions and not sure the current approach is good long term. Each service may or may not have an endpoint in a specific region. Currently if you set AWS_DEFAULT_REGION=ap-northeast-3 and then run 'steampipe query "select * from aws_inspector_template"' you'll receive an error after the attempt times out.

AWS's python SDK provides this call boto3.session.Session().get_available_regions( "service" ). I've not been able to find a comparable call via the Node SDK and suspect it is also missing in GoLang. Perhaps a desirable approach would be to allow a wild card "*" for the region value in the connection which would then populate an ephemeral table with service and available regions to iterate. Then rely on the "where region=" as a part of subsequent queries allow for region selection. The resulting query being select * from aws_inspector_template where region like 'us-%'

[e-gineer]

Hey @davidhammturner ... thanks for raising this, it's an interesting issue.

I think there are two cases where this can be a problem:

• Invalid region (e.g. ap-northeast-3 for aws_inspector_template)
• Specific invalid API for a service in a region (e.g. if the tags don't work for AWS SQS in us-gov-west-1)

If a region is not available, then I believe requests to that region should return zero rows. If a specific column is not available then it should return null or empty (as appropriate to the case).

I believe the BuildRegionList function to accept a service argument, which can then be used with this Golang function to get the regions for the service.

[davidhammturner]

I agree with returning null, within the table's column, in both cases is a desired response.

CloudSploit, which is written in Node and has been around for a while, uses an array of regions to address the missing regions issue. I believe CloudSploit pre-dates the get_available_regions call. So CloudSploit has a default list of regions and each service uses that default list unless otherwise specified. Hoping the get_available_regions approach can eliminates this need in SteamPipe. However, for larger "enterprise" implementations, there will likely be a need to allow the user to specify a limited regions list.

Thanks. for your response on this.