How to perform divide math operation in elasticsearch SQL

Elastic Stack Kibana
1

I have logged a few transactions in Elasticsearch and now trying to log a success % metric in Canvas using the elasticsearch SQL

So the logic is this. Out of all the transactions that have ended i need to get the metric of how much % have passed. I log everything to the same index

The following SQL gives me the count of all the transactions that have ended. I mean the total number of transactions

SELECT count(*) as TotalTransactions FROM "rpa-preprod*" WHERE transactionState ='Ended'

The next SQL gives the count of all the transactions that have passed

SELECT count(*) as PassedTransactions FROM "rpa-preprod*" WHERE transactionStatus='Successful'

Now i need to find how much percentage of transactions have been successful. Basically divide the second count by first count and multiply by 100.

How can i do this in elasticsearch SQL ?

I tried the following SQL and it did not work

SELECT ((SELECT count(*) as PassedTransactions FROM "rpa-preprod*" WHERE transactionStatus='Successful') * 100 /(SELECT count(*) as TotalTransactions FROM "rpa-preprod*" WHERE transactionState ='Ended')) AS SuccessPercentage FROM DUAL

2

Hi @kiran_kumar_sukumar

You can find a similar request here and in essence, the trick is to split your SQL into two essql queries and divide them. Using Canvas from Kibana 7.6 and the sample Flights dataset to get the percentage of canceled trips:

filters
| essql 
  query="SELECT count(1) as cnt FROM \"kibana_sample_data_flights\" WHERE Cancelled = true"
| math {string "cnt/" {filters | essql query="SELECT count(1) as cnt FROM \"kibana_sample_data_flights\"" | math "cnt" }}
| formatnumber "0%"
| render

Cheers

1 Like
3

Sorry, forgot to mention that you have more details about how to present percentages in other Kibana contexts on this blog post.

4

Thanks @jsanz. Its working and the numbers are so great that you wouldnt believe it :slight_smile:

5

Hi @jsanz, Is it possible to specify a default value if the count turns out to be 0

6

I'm not a Canvas expert sorry :sweat: , but maybe you can try to wrap the expression with a conditional statement to avoid that zero division.

If you can't make it work I'd suggest to create a new post nailing down to that issue in particular.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.