Group by presence of a datetime field with Elasticsearch datasource

Grafana Dashboards
,
1
  • What Grafana version and what operating system are you using?

Grafana 9.4.7.

  • What are you trying to achieve?

I have an Elasticsearch datasource with a field called aborted_at. It’s an ISO-8601 timestamp, and it is only present for some records.

I want to create a bar chart, stacked to 100%, that shows the proportion of finished and aborted records over time.

  • How are you trying to achieve it?

My query is a simple filter:

type.keyword = "$measurement_type"

and I’ve set my grouping to terms and date histogram.

  • What happened?

I can only group by the actual values of aborted_at:

image
image1920×1818 154 KB

  • What did you expect to happen?

I would like to be able to group by empty(aborted_at) or whatever possible solution to turn the field into a binary one.

I can get this relatively easily within Kibana’s Lens visualization:

image
image2818×1344 279 KB

2

The solution was provided on Stack Overflow: Group Grafana chart by presence of a datetime field with Elasticsearch datasource - Stack Overflow

Essentially, you need to use the _exists_ function.

Create two queries:

  • type.keyword: "$measurement_type" AND (_exists_:"aborted_at") with alias Aborted,
  • type.keyword: "$measurement_type" AND !(_exists_:"aborted_at") with alias finished.

Both with Metric(1) Count. Group by Date Histogram; aborted_at for the first one, field containing finish date for second one.

Then choose a stacked Bar chart.