How to create a new line on a kibana canvas table

[francieliton_araujo (francieliton araujo lima)]

Hello, I'm new here, and in kibana.
I would like to know if it is possible and how it would be, to put a new line on a table.
I made an essql query:

essql:SELECT
sum(segrelatorio) as value,severidade
FROM 
"databases*"
 WHERE
GROUP BY severidade

however it only returns 3 values for the field, which are: warning, high, disaster.
what i would like to do, would be to put a new line as the name Normalized which would be a calculation of the 3 first.

[wylie (Wylie Conlon)]

You might not be able to do this in ES SQL, but I think you can use another expression function to do this. Take a look at the canvas function reference to see the full list.

[francieliton_araujo (francieliton araujo lima)]

I understood and even read the documentation. I found a lot of interesting things, but I don't know how to do it, if you can do an example.
If I managed to do 2 essql and the result was a union it would already solve, but I don't know.

[wylie (Wylie Conlon)]

It would help if you could provide a sample input and sample output.

[francieliton_araujo (francieliton araujo lima)]

filters
| demodata
| pointseries color="state" size="max(price)"
| pie
| render

This would be the example that I can do, however I would like to include a new slice, I wanted the result for example the total sum / quantity of slices. below follows how I would like it to look

Ready this would be more or less what I wanted, this new slice would be a calculation in relation to the other, type 5000 - sum (start) + sum (running) + sum (done).

[wylie (Wylie Conlon)]

There is no expression function that can insert rows into an existing table. You can insert columns, for example here is a fairly complex table I've made that fetches some number of rows and then calculates the percentage of each row as it relates to the overall total.

It's using the var_set and var features available in 7.7 and higher:

filters
| var_set name="results" value={essql query='SELECT DestCityName, COUNT(*) as count from kibana_sample_data_flights group by DestCityName'}
| var name="results"
| var_set name="sum" value={ply expression={math "sum(count)"} | getCell "value"}
| var name="results"
| staticColumn name="sum" value={var name="sum"}
| mapColumn name="percent_of_sum" expression={math "count / sum" | formatnumber "0.0%"}
| sort by="count" reverse=true

The alternative to var_set is copy+pasting queries, but it's not as fast.

[francieliton_araujo (francieliton araujo lima)]

@wylie

math "count / sum"

Okay, thank you very much. it is an alternative solution.
I have 3 questions that are:
1st => I can have two
`value = {essql query = 'SELECT DestCityName, COUNT (*) as count from kibana_sample_data_flights group by DestCityName'} | var name = "results" `
And what will happen, we will have twice as many columns or double rows.

2nd => this expression {math "sum (count)"} can be changed to {math "1000 - sum (count)"}

3rd => could you give me an example if possible of how to put a condition in mapColumn name = "percent_of_sum" type if DestCityName = Zurich then math "count / sum" else math "1000 - count / sum

[wylie (Wylie Conlon)]

1. You can have multiple variables using var_set. For example

| var_set name="a" value={essql query="..."}
| var_set name="b" value={essql query="..."}

And then reference those as | var name="a" later.

In the most recent version you can execute these in parallel using var_set name="" value="" name="" value="".

2. Yes

3. Conditional logic can be put into the lazily evaluated function for mapColumn. Something like:

| var name="a"
| mapColumn name="new_column" expression={if {getCell "DestCityName" | eq "Zurich"} then={math "count / sum"} else={math "1000 - count / sum"}}

[francieliton_araujo (francieliton araujo lima)]

@wylie

filters
| var_set name="results" value={essql query='SELECT DestCityName, COUNT(*) as count from kibana_sample_data_flights group by DestCityName'}
| var name="results"
| var_set name="sum" value={ply expression={math "sum(count)"} | getCell "value"}
| var name="results"
| staticColumn name="sum" value={var name="sum"}
| mapColumn name="percent_of_sum" expression={math "count / sum" | formatnumber "0.0%"}
| sort by="count" reverse=true

thank you very much, I really believe that this will work, but I have already encountered a problem: when I try to make a query comes the following error

poderia me ajudar novamente

[wylie (Wylie Conlon)]

You need to escape special characters with double quotes in SQL. This usually looks like this: essql query="select \"geo.src\" from kibana_sample_data_logs".

[francieliton_araujo (francieliton araujo lima)]

@wylie

| mapColumn name="new_column" expression={if {getCell "DestCityName" | eq "Zurich"} then={math "count / sum"} else={math "1000 - count / sum"}}

Thank you very much solved my problem using an alternative exit, sorry for the delay in responding, but I need to test and apply because I would not leave you alone, because I knew you were close. Thank you very much