Aggregation inside Visualization

[kamfart]

Hello there,

I try to aggregate data with Kibana visualization but I've got some troubles.

First, some information :
My source file look like that :
ID | name | char | number1 | number2 | number3 | number4 | time

I've parsed that log to extract the information in dedicated field.
Then, I try to sum the "number column" and to group them by ID

I used the DevTools to try, here my Query in SQL (yes I didnt master the DSL yet...) :
POST _sql?format=txt
{
"query":"SELECT id, (nbDigit + nbSpec + nbUpper + nbLower) as length FROM "test-report-audit*" WHERE note!='A' AND note!='D'"
}

The output look like :
id | length
---------------+---------------
559980310 |6
404312591 |9
941595003 |10
157634821 |9
977421435 |10

Now, I tried to use this result inside Kibana Verticale Bar to count the number of occurence of each number from the field "length". But I'm stuck.

On this forum, I found (in an another thread) that is not possible to use DSL Query inside vizu.
So I guess I have to left my SQL Query to figure it out with split slices/chart only ?

But, I don't know how to "convert" this result in a Kibana Visualization and moreover I don't know if Kibana can do this kind of operation ?

Thanks for your help,
Kamfart

[wylie (Wylie Conlon)]

Your options here are:

• Create a scripted field in your index pattern which can be used generally in Kibana, especially in Visualize. I think this is your best option.

https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-guide.html

• Use the SQL statement you described while building a Canvas dashboard and visualization
• Use the Vega visualization, which supports querying elasticsearch directly via DSL or SQL

[kamfart]

Hi Wylie, thanks for your help !

I've tried the 2nd solution, with Canvas, and it work perfectly !

Now, I'm trying to understand how Painless work.
But I've got a little problem.
Because is only about Painless langage, do I need to open another thread on Elasticsearch forum ?

Thanks,
Kamfart

[wylie (Wylie Conlon)]

Yes, I think you will get the best help there. I'm definitely not as familiar with that language!

[kamfart]

Thanks for all the solution Wylie, I'm going to close this topic.

Kamfart