Create Pie Chart in Kibana based on query DSL

[Vladpov]

Hello everyone,

I have this query which mi find all message numbers that have two suffix's

   GET /my_index3/_search
    {
      "size": 0,
      "aggs": {
        "num1": {
          "terms": {
            "field": "num1.keyword",
            "order": {
              "_count": "desc"
            }
          },
          "aggs": {
            "count_of_distinct_suffix": {
              "cardinality": {
                "field": "suffix.keyword"
              }
            },
            "my_filter": {
              "bucket_selector": {
                "buckets_path": {
                  "count_of_distinct_suffix": "count_of_distinct_suffix"
                },
                "script": "params.count_of_distinct_suffix == 2"
              }
            }
          }
        }
      }
    }

Output

  "aggregations" : {
"num1" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : "1563866656876839",
      "doc_count" : 106,
      "count_of_suffix" : {
        "value" : 2
      }
    },
    {
      "key" : "1563867854324841",
      "doc_count" : 50,
      "count_of_suffix" : {
        "value" : 2
      }
    },
    {
      "key" : "1563866656878888",
      "doc_count" : 42,
      "count_of_suffix" : {
        "value" : 2
      }
    },
    {
      "key" : "1563866656871111",
      "doc_count" : 40,
      "count_of_suffix" : {
        "value" : 2
      }

The question is if there is any way to create a Pie Chart in Kibana which can show me these messages (which have two suffix) in one colour and rest of log messages in different colour like following picture:

Any idea would be really appreciated!!

[markov00 (Marco Vettorello)]

Hey @Vladpov thanks for your request.
It's not already possible in kibana/visualize but I think you can maybe try to use Canvas for that.

[Vladpov]

Thank you for your reply!

Do you have any suggestions how could I manage it with Canvas? I've never worked with Canvas so I'm asking

Thanks for any ideas

[markov00 (Marco Vettorello)]

I think it's better to start looking into the Canvas tutorial first: https://www.elastic.co/blog/getting-started-with-canvas-in-kibana
Than you could achieve the results playing a bit with the expression language in Canvas and its functions https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html

[Vladpov]

Thanks for quick reply!

I have data frame where are log messages in following format:

@timestamp.max:Jul 23, 2019 @ 11:24:18.000 num1.keyword:1563866656876839 suffix.keyword:dn _id:MWQeN8mrYpzvKYEH3qfIKBJhAAAAAAAA _type:_doc _index:dataframelast _score:1

I'm interested in num1.keyword and suffix.keyword. I created Canvas where I chose data frame index and I'm trying this query:

SELECT num1.keyword from dataframelast WHERE suffix.keyword IN ('mt', 'dn') GROUP BY num1.keyword HAVING COUNT suffix.keyword = 2;

But nothing happens

I'd like to see Pie chart where would be a percentage of log messages with two suffix...

[markov00 (Marco Vettorello)]

I think you have to rewrite your query as:
HAVING COUNT(suffix.keyword) = 2
in any case could you share the error message if any?

[Vladpov]

I'm trying this:

SELECT num1.keyword
 from dataframelast
 where suffix IN ('mt', 'dn')
 group by num1.keyword
 having count (distinct suffix.keyword) = 2;

but it throws me an exception:

Unable to parse expression: Expected "|" or end of input but "(" found.

[Vladpov]

It was caused by my mistake because I was writing query into the expression editor where I erased everything before I started writing the query...

But still, now it doesn't show the error but empty screen. I don't know where could be a problem. Maybe it can't show the query in pie chart or so ...

[markov00 (Marco Vettorello)]

Maybe you have also erased the rendering option for the pie.
To start easily try first to get the right data on a table with an similar expression:

filters
| essql query="SELECT geo.dest, COUNT(distinct geo.src) FROM kibana_sample_data_logs WHERE geo.dest IN ('AU','CA') GROUP BY geo.dest HAVING COUNT(distinct geo.src) > 20"
| table
| render

Than if you are getting the right data table out of your logs, create a new canvas pie chart, change the essql function with the correct one and than the last thing you have to do is to create a link the columns with the piechart variables on the piechart style panel

[Vladpov]

Thank you for your help but when I create a new canvas and add element either Pie Chart or Table and try |essql query it gives me an exception with exclamation mark and message:

Expression failed with the message:

    [essql] > Can not cast 'datatable' to any of 'filter'

[markov00 (Marco Vettorello)]

can you share the full expression?

[Vladpov]

So I chose my data frame and tried easy query for select * from dataframelast and it gave me that...

[markov00 (Marco Vettorello)]

Seems that you are mixing a bit the things up. Each function output filters,esdocs,pointseries is piped to the input of the next one and you are mixing few things in a wrong way.
First of all you should rewrite it as

filters
| essql query="SELECT * FROM dataframelast"
| table
| render

than fix your SQL to get the right data out on the table
and then add the pie function instead of the table one with the pie one: https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#pie_fn

[Vladpov]

Ouu thank you very much! Now I see a little bit better how it works

filters
| essql 
  query="select num1.keyword from dataframelast where suffix.keyword in ('mt','dn') group by num1.keyword having count (distinct suffix.keyword) = 2"
| pie 
| render

With this query I can see id numbers of messages that have both suffix and if I apply it on Pie chart I see one coloured chart with correct result.

Do you have any idea how can I incorporate there the other messages with different colour in the same Pie as I mentioned in my first post?