Time zone issues with Grafana filtering in CloudQuery for AWS instance history

CloudQuery Plugins
1

Hi guys,

How can I keep the instance history when using CloudQuery with AWS? In the PostgreSQL configuration, I added the write_mode: "append" option. However, I am unable to get a time-based filter to work in the Grafana “Instance Details” dashboard. Using the date variable, I can filter the dashboard data according to date, but the time zone difference in Grafana makes this difficult. Grafana shows the user’s time, but the variable shows the date according to UTC.

Is there any solution for this issue? I couldn’t get the __timeFilter() to work.

Another issue I’m facing is when using the append option with multiple AWS accounts. I updated the query to filter the “Instance Details” dashboard based on the most recent sync date and account_id. The dashboard works if I select a single account, but it does not work when selecting “All” or multiple accounts.

Here’s the query I am using:

SELECT
  DISTINCT instance_id,
  tags ->> 'Name' as Name,
  state ->> 'Name' as state,
  vpc_id,
  subnet_id,
  region,
  private_ip_address,
  public_ip_address
FROM aws_ec2_instances
WHERE (
  ('${ALL}' in (${VPCs}) OR aws_ec2_instances.vpc_id in (${VPCs}))
  AND ('${ALL}' in (${Subnets}) OR aws_ec2_instances.subnet_id in (${Subnets}))
  AND ('${ALL}' in (${Region}) OR aws_ec2_instances.region in (${Region}))
  AND (aws_ec2_instances.public_ip_address is not null)
  AND (aws_ec2_instances.account_id = ${account_id})
  AND aws_ec2_instances._cq_sync_time = (
    SELECT MAX(_cq_sync_time) FROM aws_ec2_instances WHERE aws_ec2_instances.account_id = ${account_id}
  )
)
GROUP BY instance_id, tags ->> 'Name', state ->> 'Name', vpc_id, subnet_id, region, private_ip_address, public_ip_address

Any help would be appreciated!

2

@sound-mongoose For the Grafana question, I’m not sure; maybe that’s a question better asked on the Grafana forums. However, one idea might be to set the dashboard’s timezone to UTC?

For the second question: Are you syncing CloudQuery using a separate config file per account for AWS? Otherwise, it seems like you could drop the WHERE aws_ec2_instances.account_id = ${account_id} part of the inner query for _cq_sync_time. But I’m not sure why it’s not working; can you elaborate?