Using 2 queries for influxDB

1

Hi,
I would like to display in e.g. gauge graph what is in % the pass rate (from total). Due to that I have created 2 queries where the 2nd one should be in % of the 1st one.

In an video I have seen that this is possible but in my case it does not work and it is might because I am using influxDB?

Please can you help me whith some hints?

Thank you

PS: we are using influxDB and Grafana 4.

Blockquote

I am using influxDB and Grafana 4.

2

You will have to use influxdb subqueries, enter query editor raw mode use query menu (next to trash can).

Not sure what you want can be done in via subquery though, but maybe

3

Hello,
thank you VERY MUCH for fast answer. I will check now.
Cheers,
Emilija

4

Hi again,
sorry but can you perhaps help me as I really got stuck :slight_smile:!

I have a query A which is showing me the number of successful builds

SELECT count("build_result") FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/) AND ("build_successful" = '1') AND $timeFilter GROUP BY time($__interval) fill(null)

and than the query where it shows me the total number:

SELECT count("build_result") FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/) AND $timeFilter GROUP BY time($__interval) fill(null)

now I would like to devide query A by query B to have the successful rate (which will be shown in % - as option)

than I have prepared several queries as single but none of them work, e.g:

SELECT (SELECT count("build_result") as "success" FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/ AND "build_result" = 'SUCCESS') AND $timeFilter GROUP BY time($__interval) fill(null))/(SELECT count("build_result") as "total" FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/) AND $timeFilter GROUP BY time($__interval) fill(null))

or

SELECT count("build_result") FROM (SELECT count("build_result") as "success" FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/ AND "build_result" = 'SUCCESS') AND $timeFilter GROUP BY time($__interval) fill(null))/(SELECT count("build_result") as "total" FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/) AND $timeFilter GROUP BY time($__interval) fill(null))

What is your suggestion?

Thank you

5

You may find this plugin useful in your case:
Meta Queries plugin is built as a data source plugin and can be used in conjunction with other data source to show computed metrics like Moving Average, Time Shift..

It is not in official grafana plugins repo, you’ll have to install it manually from github.
Don’t know how stable the plugin is, but it worked for me when I tried it.

6

Did you found any work around for this issue.

I have similar requirement, where have to show the percentage of API statistics in selected duration.

7

Hi,
no… i have got info that I can use a plug-in for that but that does not work. So I still need help.

Cheers,
Emilija

8

TL;DR:
The query you need is:

SELECT 1 - mean("isFail") FROM (SELECT "build_result_ordinal"/"build_result_ordinal" AS "isFail" FROM "jenkins_data" WHERE ("project_name" =~ /^$project_name$/) AND $timeFilter) WHERE $timeFilter

Although this reply is quite late, this solution should be helpful for any future passers-by with a similar issue.

I see that you are using the Jenkins InfluxDB plugin, in which case, using the build_result_ordinal field can be used as a workaround for finding the success rate of a Jenkins build.
For reference:

Jenkins Build Status build_result_ordinal
SUCCESS 0
UNSTABLE 1
FAILURE 2
NOT BUILT 3
ABORTED 4

What’s going on in the query:
The SELECT "build_result_ordinal"/"build_result_ordinal" clause in the subquery normalizes the build_result_ordinal (bro) to:

Jenkins Build Status bro/bro
SUCCESS NaN
UNSTABLE 1
FAILURE 1
NOT BUILT 1
ABORTED 1

But InfluxQL can’t handle NaNs, so it just reports them as 0. (This may be changed: see github issue below)
So this subquery returns a list of ones and zeros, where 0 is Successful and 1 is Not Successful.
Then, by taking the mean of this list we can find the failure rate of the Jenkins builds, and then subtract this from 1 to get the success rate of your Jenkins jobs with the InfluxDB plugin.

Useful Resources:

Extra Queries!

If you want an unstable build to count as a “successful” build for reporting in Grafana, the query you need is:

SELECT 1 - mean("isFail") FROM ( SELECT ceil(("build_result_ordinal"-1)/("build_result_ordinal")) AS "isFail" FROM "jenkins_data" WHERE $timeFilter) WHERE $timeFilter

for InfluxDB v1.6+, and

SELECT 1 - mean("isFail") FROM ( SELECT ((("build_result_ordinal"-1)/("build_result_ordinal"))/(("build_result_ordinal"-1)/("build_result_ordinal"))) AS "isFail" FROM "jenkins_data" WHERE $timeFilter) WHERE $timeFilter`

for InfluxDB v1.5 and previous.

1 Like