CODEWITHSUNDEEP
prometheusgrafana
Wolfgang Pausch
Wolfgang Pausch

Reputation: 13

Grafana / PromQL query to chart a value per hour in a bar chart

I have a database which I monitor using the oracle db exporter, scraped by Prometheus in a 60 seconds interval. Next to the database metrics like tablespace filling degree etc. I would like to present a couple of metrics from my application.

Now I would like to have a bar chart in Grafana which displays the result of a select using a "group By" in SQL, in my case the number of events per hour.

The metric looks like this

target_full_counter{timeBase="2023-12-11 13"} 8
target_full_counter{timeBase="2023-12-11 14"} 3

The timeBase label contains the hour (including day). Every value will appear every minute in that time series and will vanish one day when the data from the database is deleted. (Could also just select the value for the last 36 hours or so from the database)

Now I would like to have these values charted in a bar chart for every hour. The problem is only that I get 60 bars drawn for every hour. In Grafana I set the query format to Table and use a transformation to use the timeBase label as the time value.

Bar chart with multiple values

I can get around that by setting the minStep in Grafana to 1h, but then it will not give me the value for the current hour. And every attempt I made with different PromQL I fail to basically get the "last value" for each "timeBase"

Upvotes: 1

Views: 1239

Answers (1)

markalex
markalex

Reputation: 13421

Approach of "group by hour" stored in Prometheus is incorrect:

  • time related data should not be stored in labels with rare exceptions (which this case is not),
  • if you already have query providing raw data as you need it, you should consider skipping Prometheus, and querying this data directly by Grafana.

If you cannot use Oracle Data Source*, and intend on keeping Prometheus as a source of data I recommend you changing your approach:
Instead of exporting per-hour values export full counter over current day. Then, when querying data from Prometheus use it's function increase to calculate per-hour change (it will also internally take care of counter resets on midnights).

increase(target_full_counter [1h])

Additionally, don't forget to set Min step in query options of the panel to 1h, to produce one "bar" in the one hour**.

Finally, if you intend on creating custom metrics, please refer Best practices in metric and label naming.

* : it requires premium version of Grafana. Alternatively, you probably could take existing oss plugin for other RDBMS, for example MySQL one, and modify it to work with Oracle.
** : Alternatively, to get value over last hour, you might wrap your query into avg_over_time( <your_query_here> [1h:1h]) instead of Min step option. This might or might not work for different panels and configurations of said panels.

Upvotes: 0

Related Questions