Canvas Multiple Math Functions ESSQL

Elastic Stack Kibana
1

Hi,
I have a typical scenario where we need to get a percentage of number of OK devices. Something like
Scenario 1

  • Query Defective Devices count
  • Divide ( Defective Devices / total)

There are 2 ESSQL queries which get me the Defective device & Total

Here is my attempt where i am successfully able to get the percentage of Defective devices.

filters "live"
        | essql 
          query="SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')"
        | math 
          {string "scount/" {filters | essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math "pcount"}}
        | formatnumber "0%" format="0,0.[0]%"
        | metric "Defective Devices" 
          metricFont={font family="'Tw Cen MT', Helvetica, Arial, sans-serif" size=72 align="center" color="red" weight="bold" underline=false italic=false} 
          labelFont={font family="'Dubai Light', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0,0.[000]%"
        | render

Now I wish to use the same logic to get the % of OK Devices. Should be straight away subtracting (100 - result ) but for some reason it does not work.

Scenario 2

  • Subtract (total - defective) = Ok Devices
  • Divide ( OK devices/ total)

Here is my unsuccessful attempt which only gives me the count of OK Devices

filters "live"
| essql 
  query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')"
| math 
  {string "pcount -" {filters | essql query="SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')" | math {string "scount -" {filters | essql query="SELECT COUNT(DISTINCT b4) as dcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math "count(dcount)"}}}}
| formatnumber "0%" format="0,0.[000]%"
| metric "Ok Devices" 
  metricFont={font family="'Tw Cen MT', Helvetica, Arial, sans-serif" size=72 align="center" color="#4fbf48" weight="bold" underline=false italic=false} 
  labelFont={font family="'Dubai Light', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0,0.[000]"
| render

Is there any way i can add another math string expression to achieve the final result that is OK Device percentage ?

Referred This link & This one

2

I think there is probably a simple answer answer a more-complex answer. The simple answer is that you appear to be doing | math "count(dcount)", which is going to always return one because there is one cell. You might want to do | getCell "dcount" which will return a numeric value instead of a table.

There is a more complex answer, which is that starting in Kibana 7.7 you can use variables to assign and fetch values from context. Instead of making 3 SQL queries as you're showing here, you can make two. Here's how:

| var_set name="pcount" value={
  essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')"
}
| var name="pcount"
| math

I know that I haven't provided an exact answer yet, and it's because I think you are writing the wrong SQL queries. If you are still seeing the wrong results, I can only help if you break down:

Which query represents "total"? Which one represents "defective"?

3

Hi @wylie

Thanks for your reply,
Well we are running v7.5.2 Cluster for ES & Kibana and would not be upgrading the same till end of this year.
On the SQL queries here they are.

Defective Devices
"SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')"

Total Devices
SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')"

An update on the last Post - While working on the issue (SCENARIO 1), i did play around the math & string function to get a number (not in percentage %).

For example: Defective Device % = 3.6%
Number i get for OK Devices = 96.4

filters "live"
| essql 
  query="SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')"
| math 
  {string "100 - scount/" {filters | essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math "pcount/100"}}
| formatnumber "0%" format="0,0.[0]%"
| metric " OK Devices" 
  metricFont={font family="'Tw Cen MT', Helvetica, Arial, sans-serif" size=72 align="center" color="#4fbf48" weight="bold" underline=false italic=false} 
  labelFont={font family="'Dubai Light', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0.0a"
| render
4

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