Get multiple group wise data query from InfluxDB in Grafana

Question

I want to get stage name per build and parent folder wise in influxdb and show on grafana dashboard.

I'm using InfluxDB to collect other jenkins build metrics but stage name is not built-in supported hence I've created customData for stage name, hence through whichever stage build will parse, it collects the stage name and send to influxdb and I get data as below.

time                 build_number build_time project_name      project_name_1    project_path                project_path_1              stage1                  stage2                       stage3                       stage4                     stage5                     stage6 stage7              stage8
----                 ------------ ---------- ------------      --------------    ------------                --------------              ------                  ------                       ------                       ------                     ------                     ------ ------              ------
1588084985624000000  3            19824      TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic    Checkout SCM                                                                                                                                   Publish to InfluxDB petclinic
1588085162281000000  4            38600      testPipeline      testPipeline      petclinic/testPipeline      petclinic/testPipeline                              Get application version      Send Parameters To Spinnaker Build                                                        Publish to InfluxDB petclinic
1588085231934000000  4            38785      TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic                            Get application version      Send Parameters To Spinnaker Build                                                        Publish to InfluxDB petclinic
1588085356705000000  5            3616       TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic    Checkout SCM                                                                                                                                   Publish to InfluxDB petclinic
1588085797787000000  7            41916      TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic    Checkout SCM            Get application version                                                                                                Publish to InfluxDB petclinic
1588086095058000000  10           81730      TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic    Checkout SCM            Get application version                                                                                                Publish to InfluxDB TestFolder
1588087475800000000  11           43199      TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic    Checkout SCM            Get application version                                                                                                Publish to InfluxDB TestFolder
1588087564498000000  12           18748      TestPetclinic     TestPetclinic     TestFolder/TestPetclinic    TestFolder/TestPetclinic    Checkout SCM            Get application version                                                                                                Publish to InfluxDB TestFolder

From this I can easily get data per build wise(job wise) but struggling with getting data folder wise.

Below screenshot is from grafana which shows per job wise data that counts each job how many times parsed though per stage

But like I mentioned not getting data folder wise. Hence I'm also parsing hard-coded value in each job inside folder, last column in above table data ex.

Folder Name: abc > Job 1 contains custom data field key: stage8 value: "abc"
Folder Name: abc > Job 2 contains custom data field key: stage8 value: "abc"

Folder Name: xyz > Job 1 contains custom data field key: stage8 value: "xyz"
Folder Name: xyz > Job 2 contains custom data field key: stage8 value: "xyz"

Through which I get extra stage8 with 2 different values as per folder name.

Next in grafana, I'm trying to create the query, to get expected data like

stage8      Checkout SCM    Get application version     Send Parameters To Spinnaker        Build   Unit Test & Publish Report      Jacoco
petclinic        14              15                          8                               7       4                               3
TestFolder       11              12                          9                               8       8                               8

But with this query

SELECT count("stage1") as "Checkout SCM", count("stage2") as "Get application version" , count("stage3") as "Send Parameters To Spinnaker", count("stage4") as "Build", count("stage5") as "Unit Test & Publish Report", count("stage6") as "Jacoco" FROM "jenkins_custom_data" GROUP BY "stage8"

I'm not getting expected table, instead getting group of both values together, like

stage8      Checkout SCM    Get application version     Send Parameters To Spinnaker        Build   Unit Test & Publish Report      Jacoco
               23              27                          17                               15       12                               11

When I try to run this query in InfluxDB I'm getting error

ERR: mixing aggregate and non-aggregate queries is not supported

Please advise, how can we group multiple values and show as Rows in table

Thanks.

Why is this tagged as both influx and MySQL? One answer is for MySQL which might not work for influx. — hyprnick
@hypernick yes you're right, let me remove mysql, i didn't initially see that db type for influx is same as mysqul. but since the query is not working and 've researched, both db are separate, apologies. removed mysql tag. — Jaydeep Soni

nbk nbk · Accepted Answer · 2020-04-28T18:10:51

I am astonished, that you had any result at all, mysql workbench didn't liek your query at all.

Please consult When to use single quotes, double quotes, and backticks in MySQL to read more about single quotes and backticks

SELECT 
   count(`stage1`) as "Checkout SCM"
   , count(`stage2`) as "Get application version" 
   , count(`stage3`) as "Send Parameters To Spinnaker"
   , count(`stage4`) as "Build"
   , count(`stage5`) as "Unit Test & Publish Report"
   , count(`stage6`) as "Jacoco" 
FROM `jenkins_custom_data` 
GROUP BY `stage8`

Example https://www.db-fiddle.com/f/bVvbUy1rirBp5tD9Fq9J8X/3

Get multiple group wise data query from InfluxDB in Grafana

1 Answers