Visualize elastic cluster status, in color

Hi,

In Totango, we have lot’s of Elastic clusters and we’re using NR to monitor them. However, visualizing the cluster’s status (i.e., green, yellow or red) had always alluded me, until now!
The following is a NRQL that outputs 1 for green, 2 for yellow and 4 for red. Combined with a Billboard chart and appropriate thresholds, one can get the color of the health status reflected in the chart.

Here’s the NRQL:

from(
  from(
    from ElasticsearchClusterSample 
    select 
      filter(latest(cluster.status), where cluster.status = 'green') as 'green', 
      filter(latest(cluster.status), where cluster.status = 'yellow') as 'yellow',
      filter(latest(cluster.status), where cluster.status = 'red') as 'red'
    where 
      tags.env = 'prod' and tags.cluster = 'my-cluster')
  select count(green)*1 + count(yellow)*2 + count(red)*4 as 'status')
select 'My Cluster'
facet cases(
  where status = 1 as 'Green',
  where status = 2 as 'Yellow', 
  where status = 4 as 'Red')
since 5 minutes ago

And the thresholds:

Enjoy.
-S.