search

turbot / steampipe-plugin-aws

Use SQL to instantly query AWS resources across regions and accounts. Open source CLI. No DB required.
https://hub.steampipe.io/plugins/turbot/aws
Apache License 2.0
191 stars 103 forks source link

aws_route53_record results incomplete or broken #1374

Closed tinder-tder closed 2 years ago

tinder-tder commented 2 years ago

Describe the bug something changed between 0.71 and the current release where the number of records returned in our production account is vastly different. and the current 0.8.0 plugin will error out.

Steampipe version (steampipe -v) 0.15 - v0.16.1

Plugin version (steampipe plugin list) v0.79-v0.80.0

To reproduce Tested this locally

1042 ~/.steampipe/config % steampipe plugin list
+---------------------------------------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Name                                              | Version | Connections                                                                                                                                                                                                                                                    |
+---------------------------------------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hub.steampipe.io/plugins/turbot/aws@0.79.0        | 0.79.0  | prod                                                                                                                                                                                                                                                           |
| hub.steampipe.io/plugins/turbot/aws@latest        | 0.71.0  | aws,dev3,prod_old                                                                                                                                                                                                                                              |
+---------------------------------------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1043 ~/.steampipe/config % steampipe query      
Welcome to Steampipe v0.15.0
For more information, type .help
> select
      count(*)

    from
      prod.aws_route53_zone as aws_prodz,
      prod.aws_route53_record as aws_prodr
    where
> select
      count(*)

    from
      prod.aws_route53_zone as aws_prodz,
      prod.aws_route53_record as aws_prodr
    where
      aws_prodr.zone_id = aws_prodz.id
+-------+
| count |
+-------+
| 1582  |
+-------+
> select
      count(*)

    from
      prod_old.aws_route53_zone as aws_prodz,
      prod_old.aws_route53_record as aws_prodr
    where
      aws_prodr.zone_id = aws_prodz.id
+-------+
| count |
+-------+
| 2989  |
+-------+
>

Testing on our service node

+-------------------------------------------------------------------+
|                                                                   |
| A new version of Steampipe is available! 0.16.1 → 0.16.4          |
| You can update by downloading from https://steampipe.io/downloads |
|                                                                   |
+-------------------------------------------------------------------+

+---------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Name                                              | Version | Connections                                                                                                                                                                                                                                                                                                 |
+---------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hub.steampipe.io/plugins/turbot/aws@latest        | 0.80.0  | allaws,.... |
|                                                   |         |                                                                                                                                                                                                        |

+---------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
steampipe@ip-xxxxxxx:~$ steampipe query
Welcome to Steampipe v0.16.1
For more information, type .help
> select
      count(*)

    from
      aws_prod.aws_route53_zone as aws_prodz,
      aws_prod.aws_route53_record as aws_prodr
    where
      aws_prodr.zone_id = aws_prodz.id
Error: operation error Route 53: ListResourceRecordSets, https response error StatusCode: 400, RequestID: 6b156ee5-639f-4369-b106-1a14c8179645, InvalidInput:  (SQLSTATE HV000)
>

Expected behavior All records returned Not sure if https://github.com/turbot/steampipe-plugin-aws/pull/1356 broke it

tinder-tder commented 2 years ago

0.78 seems good

1047 ~/.steampipe/config % steampipe query                    
Welcome to Steampipe v0.15.0
For more information, type .help
> select
      count(*)

    from
      prod.aws_route53_zone as aws_prodz,
      prod.aws_route53_record as aws_prodr
    where
      aws_prodr.zone_id = aws_prodz.id
+-------+
| count |
+-------+
| 2989  |
+-------+
> .connections
+---------------------+---------------------------------------------------+
| connection          | plugin                                            |
+---------------------+---------------------------------------------------+
| allk8s              | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| aws                 | hub.steampipe.io/plugins/turbot/aws@latest        |
| cloudhub_ue1_a_v1   | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| csv                 | hub.steampipe.io/plugins/turbot/csv@latest        |
| d1_ue1_a_v1         | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| d2_ue1              | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| d3_ue1              | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| d4_ue1              | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| d99_ue1_a_v1        | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| dev3                | hub.steampipe.io/plugins/turbot/aws@latest        |
| eck                 | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| general_purpose_eks | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| github              | hub.steampipe.io/plugins/turbot/github@latest     |
| k8s                 | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| mobile_ue1_a_v1     | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| net                 | hub.steampipe.io/plugins/turbot/net@latest        |
| neteng_uw1_a_v1     | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| okta                | hub.steampipe.io/plugins/turbot/okta@latest       |
| ops_ue1             | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_as1_a_v1        | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_as1_b_v1        | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_as1_c_v1        | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_ue1az_a_v1      | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_ue1az_b_v1      | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_ue1az_c_v1      | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_ue1az_d_v1      | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prd_uw2             | hub.steampipe.io/plugins/turbot/kubernetes@latest |
| prod                | hub.steampipe.io/plugins/turbot/aws@0.78.0        |
| prod_old            | hub.steampipe.io/plugins/turbot/aws@latest        |
| stg1_ue1            | hub.steampipe.io/plugins/turbot/kubernetes@latest |
+---------------------+---------------------------------------------------+

To get information about the tables in a connection, run .inspect {connection}
To get information about the columns in a table, run .inspect {connection}.{table}

>
cbruno10 commented 2 years ago

Hey @tinder-tder , if you run the various queries you've mentioned with a hardcoded zone_id instead of joining on the aws_route53_zone table, do you still get the same results across the board? Not sure if this will make a difference, but would like to isolate the issues as much as possible.

We're attempting to reproduce the InvalidInput error you're seeing at the moment, but haven't been able to so far.

We're also still doing some testing to see why results are getting truncated, but haven't found the exact cause yet.

tinder-tder commented 2 years ago

@cbruno10 yea i changed the query to

select
      count(*)

    from
      prod.aws_route53_record as aws_prodr
    where
      aws_prodr.zone_id in (select id from prod.aws_route53_zone)

and with 0.79 its still truncated. 0.80 still errors.

We only see 0.80 erroring out on the prod account, other accounts seem fine (havent verified truncated results) so it may be related to how many zones/records we have.

I just tried a query with everything hardcoded and unrolled like

with everything as (
select
      aws_cloudhubr.name,
      aws_cloudhubr.type,
      aws_cloudhubr.records,
      aws_cloudhubr.alias_target,
      aws_cloudhubr.zone_id,
      aws_cloudhubr.account_id,
      aws_cloudhubz.private_zone
    from
      prod_old.aws_route53_zone as aws_cloudhubz,
      prod_old.aws_route53_record as aws_cloudhubr
    where
      aws_cloudhubr.zone_id = 'AAAAAA'
      and aws_cloudhubz.id = 'AAAAAA'
    union
select
      aws_cloudhubr.name,
      aws_cloudhubr.type,
      aws_cloudhubr.records,
      aws_cloudhubr.alias_target,
      aws_cloudhubr.zone_id,
      aws_cloudhubr.account_id,
      aws_cloudhubz.private_zone
    from
      prod_old.aws_route53_zone as aws_cloudhubz,
      prod_old.aws_route53_record as aws_cloudhubr
    where
      aws_cloudhubr.zone_id = 'BBBBBB'
      and aws_cloudhubz.id = 'BBBBBB'
    union
......
)
select
  count(*)
from
  everything;

with 0.71, counts are correct with 0.78.0+ several sequential runs gave different counts

1100 ~/bug % steampipe query gen.sql
+-------+
| count |
+-------+
| 366   |
+-------+

1101 ~/bug % steampipe query gen.sql
+-------+
| count |
+-------+
| 325   |
+-------+

1102 ~/bug % steampipe query gen.sql
+-------+
| count |
+-------+
| 17    |
+-------+

1103 ~/bug % steampipe query gen.sql
+-------+
| count |
+-------+
| 16    |
+-------+

1104 ~/bug % steampipe query gen.sql
+-------+
| count |
+-------+
| 25    |
+-------+
tinder-tder commented 2 years ago

comparing 0.78 results with the same unrolled query to 0.81.1, 0.78 is returning more records consistently (at least 1 from diffing results). Not sure what the issue is


1178 ~/bug % steampipe query gensub.sql > 0.81.1 
...
1181 ~/bug % steampipe query gensub.sql > 0.78.0 
1182 ~/bug % wc -l 0.*
    2976 0.78.0
    2975 0.81.1
    5951 total
1183 ~/bug % diff 0.78.0 0.81.1 
2161d2160
< | xxxxxxxxxxx.                                                           |
1184 ~/bug %
tinder-tder commented 2 years ago

CC: @ParthaI

tinder-tder commented 2 years ago 
> select
      count(name)

    from
      prod.aws_route53_record as aws_cloudhubr
    where
      aws_cloudhubr.zone_id = 'ZZZ'
+-------+
| count |
+-------+
| 224   |
+-------+
> select
      count(name)

    from
      prod_old.aws_route53_record as aws_cloudhubr
    where
      aws_cloudhubr.zone_id = 'ZZZ'
+-------+
| count |
+-------+
| 225   |
+-------+
> .connections
+---------------------+---------------------------------------------------+
| connection          | plugin                                            |
+---------------------+---------------------------------------------------+
| prod                | hub.steampipe.io/plugins/turbot/aws@latest        |
| prod_old            | hub.steampipe.io/plugins/turbot/aws@0.78.0        |
+---------------------+---------------------------------------------------+

AWS console and cli show 225 records in the zone id