steampipe-plugin-aws icon indicating copy to clipboard operation
steampipe-plugin-aws copied to clipboard

Published 20 hours ago • verified publisher icon turbot

aws_cost_by_service_daily - date filtering parameters not sent to the API

Open shaicoleman opened this issue 3 months ago • 13 comments

For the following query:

SELECT   service, period_start, period_end
FROM     aws_cost_by_service_daily
WHERE    period_start >= current_timestamp - interval '30d' AND
         period_end >= current_timestamp - interval '30d' AND
         service = 'Tax'

Generates the following filter request:

{
  "Filter": { "Dimensions": {"Key": "SERVICE", "Values": ["Tax"] }},
  "Granularity": "DAILY",
  "GroupBy": [{ "Key": "SERVICE", "Type": "DIMENSION" }],
  "Metrics": ["BlendedCost", "UnblendedCost", "NetUnblendedCost", "AmortizedCost", 
              "NetAmortizedCost", "UsageQuantity", "NormalizedUsageAmount"],
  "TimePeriod": { "End": "2024-03-27", "Start": "2023-03-27" }
}

This requests data for a whole year, instead of just for the requested period, and thus causes many unnecessary API requests which each cost $0.01.

Steampipe v0.22.0 turbot/aws v0.132.0

shaicoleman avatar Mar 27 '24 18:03 shaicoleman

Hi @shaicoleman, the aws_cost_by_service_daily table provides you with a simplified view of cost for services in your account (or all linked accounts when run against the organization master), summarized by day, for the last year.

It's crafted to deliver cost and usage insights per service on a daily basis for the previous year.

From what I gather, you're looking to obtain cost and usage details over a specific time frame.

Regrettably, the aws_cost_usage_* tables lack the capability to filter by a specified time period. Enhancing the aws_cost_usage table or introducing a new table could be potential improvements to support this feature.

I'm currently addressing this issue and will keep you posted once the table design is finalized.

Thank you!

ParthaI avatar Mar 29 '24 07:03 ParthaI

Hello @shaicoleman, I've made some updates in the issue-2149 branch for the aws_cost_usage table:

  • The table now accepts a specific time frame as input through the search_start_time and search_end_time columns in the WHERE clause, allowing you to retrieve results within a particular period.
  • In the absence of a specified time range, the table will revert to its default behavior, where the time range is determined by the granularity specified in the WHERE clause.

Query result:

> select
  period_start,
  period_end,
  dimension_1 as account_id,
  dimension_2 as service_name,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'SERVICE'
  and search_start_time = '2023-04-01T05:30:00+05:30'
  and search_end_time = '2023-04-05T05:30:00+05:30'
order by
  dimension_1,
  period_start;
+---------------------------+---------------------------+--------------+----------------------------------------+---------------------------+
| period_start              | period_end                | account_id   | service_name                           | net_unblended_cost_amount |
+---------------------------+---------------------------+--------------+----------------------------------------+---------------------------+
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Tax                                    | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Simple Storage Service          | $1.84                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Elastic Compute Cloud - Compute | $2.23                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Glacier                         | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Security Hub                       | $0.01                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS CloudTrail                         | $1.30                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Cost Explorer                      | $0.45                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Amplify                            | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Simple Queue Service            | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Service Catalog                    | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Inspector                       | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Simple Workflow Service         | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Step Functions                     | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon GuardDuty                       | $0.02                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Simple Notification Service     | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Glue                               | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS WAF                                | $1.47                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Config                             | $0.39                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | Amazon Security Lake                   | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Secrets Manager                    | $0.05                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AWS Key Management Service             | $0.94                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | AmazonCloudWatch                       | $0.23                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 33333333333 | EC2 - Other                            | $0.53                     |
+---------------------------+---------------------------+--------------+----------------------------------------+---------------------------+

Time: 7.3s. Rows fetched: 23. Hydrate calls: 23.

We'd greatly appreciate it if you would like to test the changes in the PR branch and share your feedback with us to ensure the code changes meet the requirements.

Here are the steps to test the PR branch:

  1. Clone the repository: git clone https://github.com/turbot/steampipe-plugin-aws.git
  2. Change directory: cd steampipe-plugin-aws
  3. Switch to the specific branch: git checkout issue-2149
  4. Execute the make command: make
  5. Run the query

Thank you!

ParthaI avatar Mar 29 '24 13:03 ParthaI

I'm having issues testing that branch with the instructions above:

$ steampipe -v
Steampipe v0.22.1
$ go version
go version go1.22.1 linux/amd64

$ rm -rf ~/.steampipe
$ git clone https://github.com/turbot/steampipe-plugin-aws.git
$ cd steampipe-plugin-aws
$ git checkout issue-2149
$ git show --summary
commit b2d235cb035cf1ca5fba0a018c06920bf19b2bff (HEAD -> issue-2149, origin/issue-2149)
    Enhanced the table aws_cost_usage to accept custom time frame as input param Closes #2149

$ make
go build -o ~/.steampipe/plugins/hub.steampipe.io/plugins/turbot/aws@latest/steampipe-plugin-aws.plugin -tags "" *.go

$ steampipe query
> .inspect aws
Error: could not find connection or table called 'aws'. Is the plugin installed? Is the connection configured?

$ cp config/* ~/.steampipe/config
$ vi ~/.steampipe/config/aws.spc # no changes
$ steampipe query
> .inspect aws
+------------+--------------------------------------------+-------------+-------+----------------------+----------------------+
| connection | plugin                                     | schema mode | state | error                | state updated        |
+------------+--------------------------------------------+-------------+-------+----------------------+----------------------+
| aws        | hub.steampipe.io/plugins/turbot/aws@latest |             | error | plugin not installed | 2024-03-29T17:40:03Z |
+------------+--------------------------------------------+-------------+-------+----------------------+----------------------+

shaicoleman avatar Mar 29 '24 17:03 shaicoleman

Hey, @shaicoleman, could you please try running the command steampipe plugin install aws first and then run the make command from the dir ~/steampipe-plugin-aws?

ParthaI avatar Mar 30 '24 05:03 ParthaI

I can't get it to run:

  • aws_cost_by_service_daily doesn't have a search_start_time field. The date filters should be applicable to all relevant tables.
SELECT service, unblended_cost_amount, period_start, period_end
FROM   aws_cost_by_service_daily
WHERE  service = 'Tax' AND
       search_start_time >= current_timestamp - interval '30d'

Error: column "search_start_time" does not exist (SQLSTATE 42703)

  • Doesn't work with aws_cost_usage either:
SELECT *
FROM   aws_cost_usage
WHERE  search_start_time >= curerent_timestamp - interval '30d'

No results - no network activity - query doesn't seem to run

  • I tried also with a date, but still doesn't work
SELECT *
FROM   aws_cost_usage
WHERE  search_start_time >= '2024-03-01'

No results - no network activity - query doesn't seem to run

shaicoleman avatar Mar 30 '24 17:03 shaicoleman

Hi @shaicoleman, we haven't yet implemented support for the search_start_time and search_end_time columns across all aws_cost_* tables. Currently, these changes have been applied to the aws_cost_usage table only.

I've made additional updates in the issue-2149 branch. Could you please fetch these updates to your local setup, rebuild the plugin, and run your queries again?

Note: When querying the aws_cost_usage table, it's mandatory to include granularity, dimension_type_1, and dimension_type_2 in the WHERE clause. You may also optionally include search_start_time or search_end_time. For further details, kindly refer to the Important Notes section in the table documentation.

Here are the query results with different operators(=, >=, <=):


> select
  period_start,
  period_end,
  search_start_time,
  dimension_1 as account_id,
  dimension_2 as service_name,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'SERVICE'
  and search_start_time >= '2023-08-01'
+---------------------------+---------------------------+---------------------------+--------------+------------------------------------------------+---------------------------+
| period_start              | period_end                | search_start_time         | account_id   | service_name                                   | net_unblended_cost_amount |
+---------------------------+---------------------------+---------------------------+--------------+------------------------------------------------+---------------------------+
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Config                                     | $3.03                     |
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Security Hub                               | $0.06                     |
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Key Management Service                     | $2.87                     |
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Cost Explorer                              | $2.69                     |
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Cloud Map                                  | $0.20                     |
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Secrets Manager                            | $0.40                     |
| 2023-08-01T05:30:00+05:30 | 2023-09-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Step Functions                             | $0.00                     |

 select
  period_start,
  period_end,
  dimension_1 as account_id,
  dimension_2 as service_name,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'SERVICE'
  and search_start_time = '2023-04-01T05:30:00+05:30'
  and search_end_time = '2023-04-05T05:30:00+05:30'
+---------------------------+---------------------------+--------------+----------------------------------------+---------------------------+
| period_start              | period_end                | account_id   | service_name                           | net_unblended_cost_amount |
+---------------------------+---------------------------+--------------+----------------------------------------+---------------------------+
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 333333333333 | Tax                                    | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 333333333333 | AWS Config                             | $0.39                     |
| 2023-04-01T05:30:00+05:30 | 2023-04-05T05:30:00+05:30 | 333333333333 | AWS Amplify                            | $0.00                     |


 select
  period_start,
  period_end,
  search_start_time,
  dimension_1 as account_id,
  dimension_2 as service_name,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'SERVICE'
  and search_start_time <= '2023-08-01'
+---------------------------+---------------------------+---------------------------+--------------+------------------------------------------------+---------------------------+
| period_start              | period_end                | search_start_time         | account_id   | service_name                                   | net_unblended_cost_amount |
+---------------------------+---------------------------+---------------------------+--------------+------------------------------------------------+---------------------------+
| 2023-04-01T05:30:00+05:30 | 2023-05-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS CloudShell                                 | $0.00                     |
| 2023-04-01T05:30:00+05:30 | 2023-05-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Amplify                                    | $0.00                     |
| 2023-05-01T05:30:00+05:30 | 2023-06-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Config                                     | $5.47                     |
| 2023-04-01T05:30:00+05:30 | 2023-05-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS CloudTrail                                 | $10.88                    |
| 2023-05-01T05:30:00+05:30 | 2023-06-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS CloudTrail                                 | $11.35                    |
| 2023-05-01T05:30:00+05:30 | 2023-06-01T05:30:00+05:30 | 2023-08-01T05:30:00+05:30 | 333333333333 | AWS Security Hub                               | $0.06                     |

Please feel free to share your feedback.

Thanks!

ParthaI avatar Apr 01 '24 10:04 ParthaI

It seems that there's an issue returning empty cached results when repeating the same query and changing the date filters.

e.g. the first query will return results correctly:

select
  period_start,
  period_end,
  search_start_time,
  dimension_1 as account_id,
  dimension_2 as service_name,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'SERVICE'
  and search_start_time >= '2023-08-01'

But then repeating the same query with a different date will return zero resuts and will not actually execute the query.

select
  period_start,
  period_end,
  search_start_time,
  dimension_1 as account_id,
  dimension_2 as service_name,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'SERVICE'
  and search_start_time >= '2023-09-01'

+--------------+------------+-------------------+------------+--------------+---------------------------+
| period_start | period_end | search_start_time | account_id | service_name | net_unblended_cost_amount |
+--------------+------------+-------------------+------------+--------------+---------------------------+
+--------------+------------+-------------------+------------+--------------+---------------------------+

And also would be good to get that functionality into the other tables as well

shaicoleman avatar Apr 20 '24 23:04 shaicoleman

Hi @shaicoleman, thank you for testing the changes.

Regarding the issue where repeating the same query with a different date returns zero results, I have pushed a fix to the same branch (issue-2149). I hope this resolves the problem.

Additionally, I've expanded the functionality to include search_start_time and search_end_time columns for all aws_cost_* tables in the issue-2149 branch.

Could you please pull the latest changes from the issue-2149 branch to your local setup and build the plugin locally?

Let me know if you encounter any further issues.

Thanks!

ParthaI avatar Apr 23 '24 07:04 ParthaI

Hi,

It doesn't filter correctly the following query:

SELECT   service, unblended_cost_amount, period_start, period_end
FROM     aws_cost_by_service_daily
WHERE    search_start_time >= current_timestamp - interval '30d' AND service = 'Tax'

It requests the default 1 year period, e.g.

{"TimePeriod":{"End":"2024-04-24","Start":"2023-04-24"}}

It also requests unnecessary metrics which aren't part of the query:

{"Metrics":["BlendedCost","UnblendedCost","NetUnblendedCost","AmortizedCost","NetAmortizedCost","UsageQuantity","NormalizedUsageAmount"]}

It would be good if it would only request the necessary metrics.

Also, consider making the search_start_time a required field. Although it's a breaking change, I think it makes sense in this case as it currently chooses an unintuitive default, which generates a lot of extra queries and can incur additional costs.

shaicoleman avatar Apr 24 '24 17:04 shaicoleman

@shaicoleman, thank you for your insightful feedback. I've made the following updates to the branch issue-2149:

It doesn't filter correctly the following query

Apologies, that was my oversight. I neglected to push a commit from my local machine. Now, all tables should be able to accept a custom time range based on the query parameter.

It would be good if it would only request the necessary metrics.

Excellent suggestion! I've implemented it, and the results are impressive. I've added an optional string type column called metrics that can be used in the WHERE clause. For selecting a single metric, use ... where metrics = 'BlendedCost'. For multiple metrics, it should be ... where metrics = 'BlendedCost,AmortizedCost,NetUnblendedCost' (listed without spaces and separated by commas).

For example, the query select * from aws_cost_by_service_usage_type_daily where metrics = 'BlendedCost' (for a single metric) took 94.1s to complete, while the query select * from aws_cost_by_service_usage_type_daily (for all metrics) took 535.7s.

I would appreciate it if you could pull the latest changes from the issue-2149 branch, build it locally, and share your feedback.

Note: The metrics optional quals support has not been added to the tables aws_cost_forecast_daily and aws_cost_forecast_monthly, as these tables are designed to return statistics for the UNBLENDED_COST metric only.

Thanks again for your valuable input!

ParthaI avatar Apr 26 '24 12:04 ParthaI

Looks good to me! Thanks

shaicoleman avatar Apr 26 '24 17:04 shaicoleman