Strange behavior with aws_health_event and regions

[wesley-staples]

Describe the bug If I have a connection in aws.spc like so:

connection "my_aws" {
  plugin  = "aws"
  profile = "my-aws"
  regions = ["us-east-1", "us-west-2"]
}

I am able to test the connection by querying for EC2 or RDS instances. Everything works great and I'm very impressed with steampipe so far.

When I query the aws_health_event table select * from my_aws.aws_health_event

an empty table is returned. The only way I can seem to get this working is to set aws.spc region to be: regions = ["*"] aka all regions.

now when I run the sql query it will return results. unfortunately both the aws_health_event and aws_health_affected_entity tables will return the region as "global". When I query this api directly from aws using python / boto3 a proper region is returned. is returning "global" for all events the desired behavior? it seems like a bug.

Also setting my aws.spc to * for regions has an unintended side effect. if I run a simple query for EC2 and RDS instances I will run into error messages since I do not have permissions to regions we do not use.

To summarize:

• I need to be able to query the health table while having the correct regions setup in my .spc file.
• I need the results of the health table to have the correct region

Steampipe version (steampipe -v) v0.23.3

Plugin version (steampipe plugin list) hub.steampipe.io/plugins/turbot/aws@latest 0.145.0

[ParthaI]

Hello, @wesley-staples,

I apologize for any inconvenience this may have caused.

Based on my understanding, AWS Health is a global service. According to Steampipe's table development standards, for global services or resources, the region column should reflect the value as global. Additionally, every Steampipe table should include a region column.

I need to be able to query the health table while having the correct regions setup in my .spc file.

In the aws_health_event table, we use the DescribeEvents API to list available events. The supported endpoints for this API are us-east-1 and us-east-2. If these regions are set up in the connection config, you should be able to retrieve the relevant results.

I need the results of the health table to have the correct region

I noticed that the API response includes a Region property, which specifies the AWS region where the event occurred. This column wasn't originally included in the table due to a potential naming conflict. However, I've now added a new column named event_region in this PR to populate the Region property from the API response.

Additionally, in the current table setup, the region value can be derived by extracting it from the arn column. For example:

• Table: aws_health_event

  select
  arn,
  split_part(arn, ':', 4) as event_region
  from
  aws_health_event;

• Table: aws_health_affected_entity

  select
  arn,
  split_part(arn, ':', 4) as entity_region
  from
  aws_health_affected_entity;

Note: We have not added any new column to provide region information in the aws_health_affected_entity table because the DescribeAffectedEntities API does not provide any region details in its response.

I hope this update helps you in obtaining the necessary region details.

Thank you!

[wesley-staples]

Thank you for your detailed a fast reply. The split_part suggestion you made works perfectly until the event_region column gets adde to the table. Thanks again for the fast reply.