Variable for day / timeframe

Grafana
1

Hello,

I’m trying to do a calculation within Grafana based on “days” is there a hidden variable that can be used to to determine the days between a timeframe?

Thanks
Trevor

2

Do you mean you want to group by day? What data source (time series database) are you using?

3

What I mean is that I have a Elasticsearch Query for a single stat under the “Script” field I would like to find a way to apply how many days are between whatever is selected.
Also is it possible to use a Sum from lets sat Query A for a calculation in Query B? using Elasticsearch as a data source.

4

I still don’t understand. What is “whatever” - the time range in Grafana or the interval between values that are returned from the query? Can you give a concrete example.

Subqueries are not possible like you can do with Graphite and InfluxDB but maybe Pipeline Aggregations would work?

5

Sorry the “whatever” would be the timeframe selected but displayed in days. So if days would be (now -2) it would be 2 days or Last week = 7 days. Last Month = 30 days. This is probably not possible I was just wondering.

I’m trying to do a query like so with Elasticsearch. I’m basically trying to take the result of Metric A and use that to divide against Metric 2. Since the metric is a sum and changes based on “timerange” the “/ MetricA” can’t be a static number it needs to be the SUM…

6

Hi,

So I’ve been looking at Script:

For some reason when running multiplication on the result, the return value is not correct. Single Stat output is set to “total”

double a = (_value * 1); double b = (1 * doc[‘count_value’].value); return b*a
or
double a = (_value * 1); double b = (1 * doc[‘count_value’].value); return b/a

but if I change the return b+a then the value output math is correct.

double a = (_value * 1); double b = (1 * doc[‘count_value’].value); return b+a

Returning each value is correct
double a = (_value * 1); double b = (1 * doc[‘count_value’].value); return b
double a = (_value * 1); double b = (1 * doc[‘count_value’].value); return a

7
  1. There are two builtin variables that return the currently selected date range:
  • $__interval
  • $__interval_ms
  1. For SingleStat, you should nearly always be using current and not total. Depending on the time range, using total can have unexpected results so it is better to aggregate your results down to one value.
  2. I don’t think what you are doing with Script: (_value * $Cost) / #A is going to work. It has to be in the same query. A bucket script might work but there is currently no support for this in Grafana. See feature request.
8

Thanks,

These internal variables return “15m” “30m” not the duration between the (today) and now -2.

My issue seems to be that the “inline” script is per bucket and not the entire result thus doc[‘count_value’].value returns a value of 1 instead of the sum 24 per bucket. I found that from the result using chrome dev tools.

count_value is a value of 1 for every hour. I use this to “sum” the hours in a day or multiple days selected from the time range. example a range of “yesterday” = 24.

 ((_value * 0.10) / (doc['count_value'].value * 10))*100

This works well, the result is 24 for “yesterday” for each hour in the day. But the above doc[‘count_value’].value is not the total for the range but rather the value for the hour = 1.

9

They should return whatever interval you have chosen in your time picker. Might have to save and refresh to be sure they are not cached as old values.

Also see interval template variables: Variable syntax | Grafana documentation