Dynamic values are not evaluated about steampipe-plugin-aws

[ParthaI]

Hello @electriquo,

Sorry to hear that you are encountering this issue.

Based on my previous experience, the query plan generation can significantly impact the results.

I've made some adjustments to the query mentioned above to better populate the dynamic values.
Could you please try it again and let us know if it resolves the issue?

with dimensioncte as (
  select
    concat(
      '[{"Name":"LoadBalancer","Value":"app/4b511df3d24f8ea194/7bff74fbf81116"},',']'
    )::jsonb as dimensions
)
select
  sum(cw.sum)
from
  aws_cloudwatch_metric_statistic_data_point as cw
join
  dimensioncte as d
on
  cw.dimensions = d.dimensions
where
  cw.namespace = 'AWS/ApplicationELB'
  and cw.metric_name = 'RequestCount'
  and cw.region = 'us-east-1'
  and cw.timestamp >= '2024-06-02 00:00:00+00'
  and cw.period = 2592000

Related discussions #2096 (comment)

Thanks!

[electriquo]

Could you please try it again and let us know if it resolves the issue?

Hi @ParthaI, it didn't (only wondering, don't you have ability to test such things without the community feedback).
Also the following does not work

with dimensioncte as (
  select
    jsonb_build_array(
    	jsonb_build_object('Name', 'LoadBalancer', 'Value', 'app/4b511df3d24f8ea194/7bff74fbf81116')
  	)::jsonb as dimensions
)
select
  sum(cw.sum)
from
  aws_cloudwatch_metric_statistic_data_point as cw
join
  dimensioncte as d
on
  cw.dimensions = d.dimensions
where
  cw.namespace = 'AWS/ApplicationELB'
  and cw.metric_name = 'RequestCount'
  and cw.region = 'us-east-1'
  and cw.timestamp >= '2024-06-02 00:00:00+00'
  and cw.period = 2592000;

If you will examine the logs, you will that the dimensions values are not populated to the plugin.

Note that, the documentation says that

steampipe-plugin-aws/docs/tables/aws_cloudwatch_metric_statistic_data_point.md

Line 21 in 40dfa62

- To fetch aggregate statistics data, `dimensions` is not required. However, except for aggregate statistics, you must always pass `dimensions` in the query; the examples below can guide you.

[electriquo]

@ParthaI after reviewing carefully your offer and the linked issue, I got it working. The trick is to have all dynamic values computed in the Common Table Expression (CTE) then use it.