Logarithmic operation in Kibana

[tshayan (Shayan Talaiyan)]

Hello,

I have a data set which includes a field for sound level in units of dBm. I am required to calculate the average of all dBm's and display the result on a map based on the locations they were recorded in.

The proper way to calculate the average dBm would be as follows:

1- Convert dBm to mW for each document: 10^(dBm/10)
2- Take the average of mW's in all documents: AVG(step 1 result)
3- Convert avg mW to avg dBm: Log10(step 2 result)*10

Step 1 I can do with pre-processing during data ingest. Step 2 I can easily do in Kibana after pre-processing. Step 3 is where I am stuck... Any way to take the result of step 2 and do a logarithmic operation on it? I wasn't able to find a way to do it in any of the visualizations. I looked into using scripted fields but that only works on each document and cannot be used for aggregations on your whole data set.

Any tips would be appreciated. I am using Kibana 7.5.1.

[flash1293 (Joe Reuter)]

You can't do this in regular visualizations, but it's possible in canvas due to the flexibility of the expression language. After fetching the dBm data using an Elasticsearch SQL query, you can pipe it through a math expression built using a second Elasticsearch SQL query fetching the global average.

Same approach as in this answer Calculating purcentage in a metric

[tshayan (Shayan Talaiyan)]

Thanks for your reply... I am new to Kibana and haven't played with Canvas much... Assuming I do this successfully in Canvas would it then be possible to bring in the Canvas visualization into the main Kibana dashboard?

[flash1293 (Joe Reuter)]

It's planned to embed canvas workpads into dashboards, but it's not possible at the moment.