Create Pie Chart in Kibana based on query DSL

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:

pie_chart

Any idea would be really appreciated!!

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.

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 :smiley:

Thanks for any ideas :slight_smile:

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

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 :frowning:

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

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?

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.

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... :smiley:

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 ...

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

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'

can you share the full expression?

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

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

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

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?

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