Timeseries panel using postgresql with cloudquery data

cartor79 · December 6, 2023, 12:38pm

Hi all.

Im new in Grafana, postgresql and this is my first post, so my thanks and apologies in advance in case I do something wrong.

What Grafana version and what operating system are you using?
Grafana v10.1.5 on GNU/Linux
What are you trying to achieve?
Im trying to get a TimeSeries panel graph that represents for each EC2 ‘instance_type’, the number of EC2 INSTANCES running from a postgresql DB fed with cloudquery from AWS.
How are you trying to achieve it?
I’ve got already some tables that allow me to get the number of instances of each type currently in use in a pie chart dashboard

This is the formula:
select a.instance_type,count(*) from aws_ec2_instances a, aws_organizations_accounts b WHERE a.account_id=b.id and b.name in (${account_names}) and a.region in (${regions}) and (a.tags->>'Proyecto' in ($proyecto) OR ('${sinproyecto}' in ($proyecto) and (a.tags->>'Proyecto'='' or a.tags?'Proyecto'=false or a.tags is null))) group by a.instance_type

And this is the result
imagen

In order to have historic data, I’ve being copying data on a daily basis from each table to another table with the same name with ‘h_’ at the beginning of the name.
So for the tables:
aws_ec2_instances and aws_organizations_accounts, their respective historic data tables’s names would be h_aws_ec2instances and h_aws_organizationsaccounts.

Now, based on the previous formula and the historic data tables, I want to create a panel in my dashboard that represents TimeSeries with a line for each ‘instance_type’ along the time, with a point for each day representing how many EC2 instances that existed that day for that particular instance.

I tried this formula:

select a.info_date, a.instance_type,count(*) from h_aws_ec2instances a, h_aws_organizationsaccounts b WHERE a.account_id=b.id and b.name in (${account_names}) and a.region in (${regions}) and (a.tags->>'Proyecto' in ($proyecto) OR ('${sinproyecto}' in ($proyecto) and (a.tags->>'Proyecto'='' or a.tags?'Proyecto'=false or a.tags is null))) group by a.instance_type, a.info_date order by 1 asc;

But this is what I got:

Any idea of what should be my aproach?

Thanks a lot for your time and help

Regards.

Carlos T

yosiasz · December 6, 2023, 2:23pm

Welcome @cartor79

So, is the result you are seeing incorrect? What should it be showing you?

Also can you please click on query inspector above left of the query and see what the query tab says and post it back?

cartor79 · December 12, 2023, 11:52am

Hi Yosiasz

Thank you for your support and the welcome

Considering the formula Im using:

SELECT a.info_date, b.name, count(*) from h_aws_ec2instances a, aws_organizations_accounts b WHERE a.account_id=b.id and b.name in (${account_names}) and a.region in (${regions}) AND (a.tags->>'Proyecto' in ($proyecto) OR ('${sinproyecto}' in ($proyecto) and (a.tags->>'Proyecto'='' or a.tags?'Proyecto'=false or a.tags is null))) group by b.name, a.info_date order by 1

This is the data (part of it) I’m getting when I export the cvs file from the Query inspector.

2023-12-09 01:00:00,Account01,11
2023-12-09 01:00:00,Account02,18
2023-12-09 01:00:00,Account03,14
2023-12-09 01:00:00,Account04,25
2023-12-09 01:00:00,Account05,58
2023-12-09 01:00:00,Account06,58
2023-12-09 01:00:00,Account20,1
2023-12-09 01:00:00,Account19,2
2023-12-09 01:00:00,Account21,2
2023-12-09 01:00:00,Account07,2
2023-12-09 01:00:00,Account08,6
2023-12-09 01:00:00,Account09,4
2023-12-09 01:00:00,Account10,4
2023-12-09 01:00:00,Account11,2
2023-12-09 01:00:00,Account12,6
2023-12-09 01:00:00,Account13,35
2023-12-09 01:00:00,Account14,17
2023-12-09 01:00:00,Account15,33
2023-12-09 01:00:00,Account16,24
2023-12-09 01:00:00,Account17,62
2023-12-09 01:00:00,Account18,17
2023-12-10 01:00:00,Account01,11
2023-12-10 01:00:00,Account02,18
2023-12-10 01:00:00,Account03,14
2023-12-10 01:00:00,Account04,25
2023-12-10 01:00:00,Account05,58
2023-12-10 01:00:00,Account06,58
2023-12-10 01:00:00,Account20,1
2023-12-10 01:00:00,Account19,2
2023-12-10 01:00:00,Account21,2
2023-12-10 01:00:00,Account07,2
2023-12-10 01:00:00,Account08,6
2023-12-10 01:00:00,Account09,4
2023-12-10 01:00:00,Account10,4
2023-12-10 01:00:00,Account11,2
2023-12-10 01:00:00,Account12,6
2023-12-10 01:00:00,Account13,35
2023-12-10 01:00:00,Account14,17
2023-12-10 01:00:00,Account15,33
2023-12-10 01:00:00,Account16,24
2023-12-10 01:00:00,Account17,62
2023-12-10 01:00:00,Account18,17
2023-12-11 01:00:00,Account01,11
2023-12-11 01:00:00,Account02,18
2023-12-11 01:00:00,Account03,14
2023-12-11 01:00:00,Account04,25
2023-12-11 01:00:00,Account05,58
2023-12-11 01:00:00,Account06,58
2023-12-11 01:00:00,Account20,1
2023-12-11 01:00:00,Account19,2
2023-12-11 01:00:00,Account21,2
2023-12-11 01:00:00,Account07,2
2023-12-11 01:00:00,Account08,6
2023-12-11 01:00:00,Account09,4
2023-12-11 01:00:00,Account10,4
2023-12-11 01:00:00,Account11,2
2023-12-11 01:00:00,Account12,6
2023-12-11 01:00:00,Account13,35
2023-12-11 01:00:00,Account14,17
2023-12-11 01:00:00,Account15,33
2023-12-11 01:00:00,Account16,24
2023-12-11 01:00:00,Account17,62
2023-12-11 01:00:00,Account18,17
2023-12-12 01:00:00,Account01,11
2023-12-12 01:00:00,Account02,18
2023-12-12 01:00:00,Account03,14
2023-12-12 01:00:00,Account04,25
2023-12-12 01:00:00,Account05,58
2023-12-12 01:00:00,Account06,58
2023-12-12 01:00:00,Account20,1
2023-12-12 01:00:00,Account19,2
2023-12-12 01:00:00,Account21,2
2023-12-12 01:00:00,Account07,2
2023-12-12 01:00:00,Account08,6
2023-12-12 01:00:00,Account09,4
2023-12-12 01:00:00,Account10,4
2023-12-12 01:00:00,Account11,2
2023-12-12 01:00:00,Account12,6
2023-12-12 01:00:00,Account13,35
2023-12-12 01:00:00,Account14,17
2023-12-12 01:00:00,Account15,36
2023-12-12 01:00:00,Account16,47
2023-12-12 01:00:00,Account17,62
2023-12-12 01:00:00,Account18,17

And the panel shows this:

I expect a panel that would show a line for each account (second field), representing the count value (third field in Y axi), along the time (first field X axi),

I’m realising right now that the value the panel is representing are those of the last and first account of everyday (17,11,17,11 ,)

Thanks a lot and regards.

Carlos T

yosiasz · December 12, 2023, 1:11pm

Try this instead and see what happens for the select part of the query (not formula)

SELECT a.info_date as time, 
b.name as metric, 
count(*) as value

cartor79 · December 12, 2023, 1:30pm

Hi again.

When I export the resulting CSV the result is exactly the same but with this first line:


"time","metric","value"
2023-12-09 01:00:00,Account01,11
2023-12-09 01:00:00,Account02,18
2023-12-09 01:00:00,Account03,14
2023-12-09 01:00:00,Account04,25
2023-12-09 01:00:00,Account05,58
2023-12-09 01:00:00,Account06,58
2023-12-09 01:00:00,Account20,1
2023-12-09 01:00:00,Account19,2
2023-12-09 01:00:00,Account21,2
2023-12-09 01:00:00,Account07,2
2023-12-09 01:00:00,Account08,6
2023-12-09 01:00:00,Account09,4
2023-12-09 01:00:00,Account10,4
2023-12-09 01:00:00,Account11,2
2023-12-09 01:00:00,Account12,6
2023-12-09 01:00:00,Account13,35
2023-12-09 01:00:00,Account14,17
2023-12-09 01:00:00,Account15,33
2023-12-09 01:00:00,Account16,24
2023-12-09 01:00:00,Account17,62
2023-12-09 01:00:00,Account18,17
2023-12-10 01:00:00,Account01,11
2023-12-10 01:00:00,Account02,18
2023-12-10 01:00:00,Account03,14
2023-12-10 01:00:00,Account04,25
2023-12-10 01:00:00,Account05,58
2023-12-10 01:00:00,Account06,58
2023-12-10 01:00:00,Account20,1
2023-12-10 01:00:00,Account19,2
2023-12-10 01:00:00,Account21,2
2023-12-10 01:00:00,Account07,2
2023-12-10 01:00:00,Account08,6
2023-12-10 01:00:00,Account09,4
2023-12-10 01:00:00,Account10,4
2023-12-10 01:00:00,Account11,2
2023-12-10 01:00:00,Account12,6
2023-12-10 01:00:00,Account13,35
2023-12-10 01:00:00,Account14,17
2023-12-10 01:00:00,Account15,33
2023-12-10 01:00:00,Account16,24
2023-12-10 01:00:00,Account17,62
2023-12-10 01:00:00,Account18,17
2023-12-11 01:00:00,Account01,11
2023-12-11 01:00:00,Account02,18
2023-12-11 01:00:00,Account03,14
2023-12-11 01:00:00,Account04,25
2023-12-11 01:00:00,Account05,58
2023-12-11 01:00:00,Account06,58
2023-12-11 01:00:00,Account20,1
2023-12-11 01:00:00,Account19,2
2023-12-11 01:00:00,Account21,2
2023-12-11 01:00:00,Account07,2
2023-12-11 01:00:00,Account08,6
2023-12-11 01:00:00,Account09,4
2023-12-11 01:00:00,Account10,4
2023-12-11 01:00:00,Account11,2
2023-12-11 01:00:00,Account12,6
2023-12-11 01:00:00,Account13,35
2023-12-11 01:00:00,Account14,17
2023-12-11 01:00:00,Account15,33
2023-12-11 01:00:00,Account16,24
2023-12-11 01:00:00,Account17,62
2023-12-11 01:00:00,Account18,17
2023-12-12 01:00:00,Account01,11
2023-12-12 01:00:00,Account02,18
2023-12-12 01:00:00,Account03,14
2023-12-12 01:00:00,Account04,25
2023-12-12 01:00:00,Account05,58
2023-12-12 01:00:00,Account06,58
2023-12-12 01:00:00,Account20,1
2023-12-12 01:00:00,Account19,2
2023-12-12 01:00:00,Account21,2
2023-12-12 01:00:00,Account07,2
2023-12-12 01:00:00,Account08,6
2023-12-12 01:00:00,Account09,4
2023-12-12 01:00:00,Account10,4
2023-12-12 01:00:00,Account11,2
2023-12-12 01:00:00,Account12,6
2023-12-12 01:00:00,Account13,35
2023-12-12 01:00:00,Account14,17
2023-12-12 01:00:00,Account15,36
2023-12-12 01:00:00,Account16,47
2023-12-12 01:00:00,Account17,62
2023-12-12 01:00:00,Account18,17

And the panel looks the same.

yosiasz · December 13, 2023, 7:52am

I used both of the following transformation

cartor79 · December 13, 2023, 5:13pm

Yes Sir. You are my hero.
I tried the transformation and I’ve been able to make six panels so far without any problem.

Thank you very much for your help, time and wisdom

Regards.

Carlos T