Handle empty result set with Stat visualisation

Grafana Stat Panel
,
1
  • Grafana version: 8.3.3
  • Visualization: Stat
  • Data source type & version: Microsoft SQL Server 2019
  • OS Grafana is installed on: Windows
  • User OS & Browser: Edge

I’m using variations of the following query to display the health status of an application. I’m also taking advantage of the time series feature that automatically maps the third parameter (metric) to the name assigned to the second parameter (healthy).

When there is data this mostly works as you can see.

mops-working.PNG
mops-working.PNG1573×757 111 KB

When there is no data both the first and the second parameter are shown.

mops-broken.PNG
mops-broken.PNG2053×722 115 KB

Ideally I’d like to display the health status of an application with no data as unhealthy.

I get the feeling that I need to implement this another way because if there’s no data Grafana can’t know the name of the metric?

SELECT $__timeEpoch(AUDIT_TIME),
       CAST(HEALTHY AS INT) AS healthy,
       APP_NAME             AS metric
FROM ima.dbo.HEALTH_CHECK_RULE
         INNER JOIN ima.dbo.HEALTH_CHECK_METRIC m ON HEALTH_CHECK_RULE.RULE_ID = m.RULE_ID
WHERE $__timeFilter(AUDIT_TIME)
  AND APP_NAME = 'mwproxy'
  AND RULE_NAME = 'All checks'
ORDER BY AUDIT_TIME ASC
2

Welcome

Please provide DDL and DML for both tables?

create table #HEALTH_CHECK_RULE --etc
create table #HEALTH_CHECK_METRIC  --etc

insert into #HEALTH_CHECK_RULE values()

insert into #HEALTH_CHECK_METRIC  values()

We don’t have access to your database so impossible to provide some guidance.

3

I’ll answer my own question :wink:

I’ve realized that I don’t actually need a series, I just need the value of the most recent metric. I get a better result when I let the database find it for me and let Grafana format the result set as a table.

SELECT CAST(HEALTHY AS INT) AS mwproxy
FROM ima.dbo.HEALTH_CHECK_METRIC a
         INNER JOIN
     (SELECT MAX(METRIC_ID) id
      FROM ima.dbo.HEALTH_CHECK_RULE
               INNER JOIN ima.dbo.HEALTH_CHECK_METRIC m ON HEALTH_CHECK_RULE.RULE_ID = m.RULE_ID
      WHERE APP_NAME = 'mwproxy'
        AND RULE_NAME = 'All checks') b ON a.METRIC_ID = b.id
1 Like