How can I graph(time series) this sql statement from postgresql data source “select count(*), usename from pg_stat_activity group by usename;”
Try&improve:
SELECT
$__timeGroupAlias(query_start, $__interval, 0),
usename AS "metric",
count(*) AS "count"
FROM pg_stat_activity
WHERE
$__timeFilter(query_start)
GROUP BY 1,2
ORDER BY 1
1 Like
Which field in your database table is your timestamp?
I don’t quite see how a “select count” grouped by username is going to result
in a time series of data for Grafana to display…
Antony.
query_start column for timestamp .
My query in table form produces the attached image. Is it possible for the time series to show the "usename"s
timeseries requires that you have a time column in your query.
datid
datname
pid
usesysid
usename
application_name
client_addr
client_hostname
client_port
backend_start
xact_start
query_start
state_change
wait_event_type
wait_event
state
backend_xid
backend_xmin
query
backend_type
these are all the columns
Don’t use table format, when you want to have time series. Did you try query which I gave you?
That’s exactly what you asked - " graph(time series)". What are you expecting?
1 Like
But it is not clear what you want. You asked for “group by usename” originally, but now I don’t see any grouping in your latest screenshot. Are you sure, that you know what you want? Because it looks like you are changing requirements and wasting time. Be exact pls.
Apologies, The query you shared is fine, and I am grouping by usename, My only issue is query_start time keeps changing, So I expected the timeseries graph to plot accordingly, like in my second screenshot. My second screenshot was just an example from another plot.
I don’t understand. See doc: PostgreSQL: Documentation: 16: 28.2. The Cumulative Statistics System
Okay, Thanks