2 Queries in one table for grafana

1

Hello!

I want to create a dashboard with Grafana to visualize the SLA-Problems of my Business Processes.
I know that this is not the Grafana-Forum. But I want to check my queries first :slight_smile:

I have two queries:

  • First: List all services with a sla-level < 99.7
  • Second: List all servides with a sla-error > 4 h

First:
SELECT service,"percentage" FROM (SELECT (1 - (sum("time_critical") / sum("time_total"))) * 100 AS "percentage" FROM "availability" WHERE "host" =~ /^business_processes$|^business_processes_detail$/ AND "service" =~ /^sla_/ AND "timeperiod" =~ /^24x7$/ AND time >= 1525125600000000000 and time <= 1527803999000000000 GROUP BY service) WHERE percentage < 99.7

    name: availability
    time                service   percentage
    ----                -------   -------
    1525125600000000000 sla_1     99.47125149342891
    1525125600000000000 sla_2     99.52751642771804
    1525125600000000000 sla_3     99.07911439665472
    1525125600000000000 sla_4     99.45784796893668
    1525125600000000000 sla_5     99.65415919952211
    1525125600000000000 sla_6     99.08359468339307

Second:
SELECT count(“time_critical”) as Anzahl FROM “availability” WHERE ((“host” = ‘business_processes’ OR “host” = ‘business_processes_detail’) AND “service” =~ /^sla_/ AND “timeperiod” = ‘24x7’) AND time_critical >= (4*3600) and time >= 1525125600000000000 and time <= 1527803999000000000 group by service

name: availability
tags: service=sla_4
time                Anzahl
----                ------
1525125600000000000 1

I want to combine these 2 queries in Granfana in one Table like:

time                service   percentage              Anzahl
    ----                -------   -------             -------
    1525125600000000000 sla_1     99.47125149342891   
    1525125600000000000 sla_2     99.52751642771804   
    1525125600000000000 sla_3     99.07911439665472   
    1525125600000000000 sla_4     99.45784796893668   1
    1525125600000000000 sla_5     99.65415919952211
    1525125600000000000 sla_6     99.08359468339307

Is this possible?
If I change the second query, that I get also the “service” to SELECT service,count("time_critical") as Anzahl FROM "availability" WHERE (("host" = 'business_processes' OR "host" = 'business_processes_detail') AND "service" =~ /^sla_/ AND "timeperiod" = '24x7') AND time_critical >= (1*3600) and time >= 1525125600000000000 and time <= 1527803999000000000
than I get an Error: ERR: mixing aggregate and non-aggregate queries is not supported

Do anyone know how I can change the query that I will get the table I need?

rg
hansi