# Create Pie Chart in Kibana based on query DSL **URL:** \ **Category:** Kibana\ **Created:** [July 31, 2019, 3:34pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162 "2019-07-31T15:34:32Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [July 31, 2019, 3:34pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/1 "2019-07-31T15:34:32Z")
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](https://us1.discourse-cdn.com/elastic/original/3X/4/1/4131f3d65c0eb01ea811765089d1e9f138d8afb2.png) Any idea would be really appreciated!! ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [August 25, 2019, 5:05pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/2 "2019-08-25T17:05:11Z")
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. ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 6:46am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/3 "2019-09-16T06:46:01Z")
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 🙂 ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [September 16, 2019, 7:30am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/4 "2019-09-16T07:30:56Z")
I think it's better to start looking into the Canvas tutorial first: [https://www.elastic.co/blog/getting-started-with-canvas-in-kibana](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](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html) ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 7:43am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/5 "2019-09-16T07:43:39Z")
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... ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [September 16, 2019, 8:02am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/6 "2019-09-16T08:02:00Z")
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? ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 8:05am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/7 "2019-09-16T08:05:36Z")
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. ``` ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 8:53am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/8 "2019-09-16T08:53:32Z")
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 ... ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [September 16, 2019, 9:40am UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/9 "2019-09-16T09:40:13Z")
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: ```auto 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 ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 1:56pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/10 "2019-09-16T13:56:54Z")
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' ``` ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [September 16, 2019, 2:10pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/11 "2019-09-16T14:10:53Z")
can you share the full expression? ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 4:00pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/12 "2019-09-16T16:00:40Z")
So I chose my data frame and tried easy query for select \* from dataframelast and it gave me that... ![Sn%C3%ADmek%20obrazovky%20(69)](https://us1.discourse-cdn.com/elastic/original/3X/5/f/5f919765af3d7a18b37444a35d769d1320b20f13.png) ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [September 16, 2019, 4:15pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/13 "2019-09-16T16:15:09Z")
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 ```auto 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](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#pie_fn) ---
**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\ **Post date:** [September 16, 2019, 7:43pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/14 "2019-09-16T19:43:35Z")
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? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 14, 2019, 7:43pm UTC](https://discuss.elastic.co/t/create-pie-chart-in-kibana-based-on-query-dsl/193162/15 "2019-10-14T19:43:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.