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
177 stars 89 forks source link

Add support for the custom date range metric data in the table aws_cost_usage_* table #2168

Open ParthaI opened 2 months ago

ParthaI commented 2 months ago

Note: Currently the changes has been made to the table aws_cost_usage

Integration test logs

Logs ``` Add passing integration test logs here ```

Example query results

Results ``` > with ranked_costs as ( select dimension_1 as account_id, dimension_2 as service_name, sum(net_unblended_cost_amount)::numeric::money as net_unblended_cost, rank() over(partition by dimension_1 order by sum(net_unblended_cost_amount) desc) from aws_cost_usage where granularity = 'MONTHLY' and dimension_type_1 = 'LINKED_ACCOUNT' and dimension_type_2 = 'SERVICE' group by dimension_1, dimension_2 order by dimension_1, net_unblended_cost desc ) select * from ranked_costs where rank <=5 +--------------+-------------------------------------------+--------------------+------+ | account_id | service_name | net_unblended_cost | rank | +--------------+-------------------------------------------+--------------------+------+ | xxxxxxxxxxxx | Amazon Route 53 | $776.67 | 1 | | xxxxxxxxxxxx | Amazon Managed Streaming for Apache Kafka | $684.66 | 2 | | xxxxxxxxxxxx | Amazon Simple Storage Service | $120.08 | 3 | | xxxxxxxxxxxx | AWS CloudTrail | $111.90 | 4 | | xxxxxxxxxxxx | AWS Cost Explorer | $87.31 | 5 | +--------------+-------------------------------------------+--------------------+------+ Time: 3.1s. Rows fetched: 356. Hydrate calls: 356. > select * from aws_cost_by_account_daily where search_start_time < '2023/10/22' +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | search_start_time | search_end_time | linked_account_id | period_start | period_end | estimated | blended_cost_amount | blended_cost_unit | unblended_cost_amount | unblended_cost_unit | net_unblend> +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | 2023-05-02T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-05-02T05:30:00+05:30 | 2023-05-03T05:30:00+05:30 | false | 7.3065749693 | USD | 7.218898293 | USD | 7.218898293> | 2023-04-25T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-04-25T05:30:00+05:30 | 2023-04-26T05:30:00+05:30 | false | 25.6414311568 | USD | 25.6508645369 | USD | 25.65086453> | 2023-06-15T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-06-15T05:30:00+05:30 | 2023-06-16T05:30:00+05:30 | false | 8.1370545102 | USD | 8.147024372 | USD | 8.147024372> | 2023-06-17T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-06-17T05:30:00+05:30 | 2023-06-18T05:30:00+05:30 | false | 8.3465296718 | USD | 8.2176107603 | USD | 8.217610 > select * from aws_cost_by_account_daily where search_start_time > '2023/10/22' +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | search_start_time | search_end_time | linked_account_id | period_start | period_end | estimated | blended_cost_amount | blended_cost_unit | unblended_cost_amount | unblended_cost_unit | net_unblend> +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | 2023-10-24T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-10-24T05:30:00+05:30 | 2023-10-25T05:30:00+05:30 | false | -0.0085295875 | USD | -0.0000005126 | USD | -0.00000051> | 2023-11-08T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-11-08T05:30:00+05:30 | 2023-11-09T05:30:00+05:30 | false | 4.7238236635 | USD | 4.7436674938 | USD | 4.743667493> | 2023-11-06T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-11-06T05:30:00+05:30 | 2023-11-07T05:30:00+05:30 | false | 4.5955332705 | USD | 4.6153781606 | USD | 4.615378160> | 2023-10-25T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-10-25T05:30:00+05:30 | 2023-10-26T05:30:00+05:30 | false | -0.0085291992 | USD | -0.0000004242 | USD | -0.00000042> | 2023-10-26T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-10-26T05:30:00+05:30 | 2023-10-27T05:30:00+05:30 | false | -0.0085293691 | USD | -0.0000004931 | USD | -0.00000049> > select * from aws_cost_by_account_daily where search_start_time = '2023/10/22' +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | search_start_time | search_end_time | linked_account_id | period_start | period_end | estimated | blended_cost_amount | blended_cost_unit | unblended_cost_amount | unblended_cost_unit | net_unblend> +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-04-24T05:30:00+05:30 | 2023-04-25T05:30:00+05:30 | false | 9.9475961318 | USD | 9.9570970142 | USD | 9.957097014> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-09-12T05:30:00+05:30 | 2023-09-13T05:30:00+05:30 | false | 4.5732790977 | USD | 4.5822746311 | USD | 4.582274631> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-11-08T05:30:00+05:30 | 2023-11-09T05:30:00+05:30 | false | 4.7238236635 | USD | 4.7436674938 | USD | 4.743667493> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-05-29T05:30:00+05:30 | 2023-05-30T05:30:00+05:30 | false | 2.2530854024 | USD | 2.2621885473 | USD | 2.262188547> > select * from aws_cost_by_account_daily where search_start_time >= '2023-10-22' +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | search_start_time | search_end_time | linked_account_id | period_start | period_end | estimated | blended_cost_amount | blended_cost_unit | unblended_cost_amount | unblended_cost_unit | net_unblend> +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-04-29T05:30:00+05:30 | 2023-04-30T05:30:00+05:30 | false | 2.3709122983 | USD | 2.3803209871 | USD | 2.380320987> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-05-01T05:30:00+05:30 | 2023-05-02T05:30:00+05:30 | false | 2.3453632762 | USD | 2.2576880954 | USD | 2.257688095> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-06-22T05:30:00+05:30 | 2023-06-23T05:30:00+05:30 | false | 32.6410264084 | USD | 32.6510974782 | USD | 32.6510974 > select * from aws_cost_by_account_daily where search_start_time <= '2023-10-22' +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | search_start_time | search_end_time | linked_account_id | period_start | period_end | estimated | blended_cost_amount | blended_cost_unit | unblended_cost_amount | unblended_cost_unit | net_unblend> +---------------------------+-----------------+-------------------+---------------------------+---------------------------+-----------+---------------------+-------------------+-----------------------+---------------------+------------> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-04-25T05:30:00+05:30 | 2023-04-26T05:30:00+05:30 | false | 25.6414311568 | USD | 25.6508645369 | USD | 25.65086453> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-05-05T05:30:00+05:30 | 2023-05-06T05:30:00+05:30 | false | 2.4394110847 | USD | 2.4485004554 | USD | 2.448500455> | 2023-10-22T05:30:00+05:30 | | xxxxxxxxxxxx | 2023-06-05T05:30:00+05:30 | 2023-06-06T05:30:00+05:30 | false | 2.1222351314 | USD | 2.1322019932 | USD | 2.1322019 ```