Hello, I’m using Grafana 8.2.6 with an Elasticsearch 7.10.2 datasource, and am new to Grafana so apologies if this is a simple solution that I have somehow missed.
I need to create a table panel where my data is transposed so that each row represents one of my fields, I am currently able to do this by using the raw data metric with a reduce transformation with series to rows. In order to use this data I also need to use the Distinct Count and All Nulls calculations so that I can filter down my data.
In order for a user to understand the data, I also need to display the unique values in each field, the closest I have gotten to this is by using the All Values calculation on the reduce transformation, but this lists every value in list form, as opposed to every distinct value, which is not ideal with a large number of rows and few distinct values.
So far I have looked through each transformation option, looked at Repeat by Variable options with my fields being passed into a dashboard variable, and Value mappings, however hit a dead end each time. I’ve also considered passing my list of values into a html panel with a JavaScript function that could reduce the list to unique values, however I do not know where to start here. I’m also aware of term aggregation in Elasticsearch but I’m not sure how I would go about using this in Grafana.
An example of the data I have is:
timestamp | field1 | field2 | field3 | field4
a1 | b1 | c1 | d1 | null
a2 | b2 | c2 | d1 | null
a3 | b1 | c3 | null | null
a4 | b3 | c4 | d2 | null
a5 | b2 | c5 | d1 | null
With the table panel I currently have looking like:
Field Name | All Values | Distinct Count | All Nulls
field1 | [b1,b2,b1,b3,b2] | 3 | false
field2 | [c1,c2,c3,c4,c5] | 5 | false
field3 | [d1,d1,null,d2,d1] | 3 | false
field4 | [null, null, null, null, null] | 1 | true
And I want my data to instead look like:
Field Name | Distinct Values | Distinct Count | All Nulls
field1 | [b1,b2,b3] | 3 | false
field2 | [c1,c2,c3,c4,c5] | 5 | false
field3 | [null,d1,d2] | 3 | false
field4 | [null] | 1 | true