# Visualizing count differences **URL:** \ **Category:** Kibana\ **Created:** [September 12, 2019, 10:04pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331 "2019-09-12T22:04:58Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![wfaulk-bw](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wfaulk-bw/32/54114_2.png) [@wfaulk-bw](https://discuss.elastic.co/u/wfaulk-bw)\ **Post date:** [September 12, 2019, 10:04pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/1 "2019-09-12T22:04:58Z")
I have a visualization that is showing me the top 25 clients for a particular DNS query. I'm interested in clients that are preferring one of our DNS servers over the other, so right now, I just have a Split Series, splitting on the term beat.hostname. But what I'd really like is to get a graph of the count difference, something like graphing "count[beat.hostname:dnsserver1] - count[beat.hostname:dnsserver2]". I'm happy to provide the dnsserver names manually, but I have no idea how to do math on counts, or if it's even possible. ---
**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\ **Post date:** [September 12, 2019, 10:29pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/2 "2019-09-12T22:29:32Z")
Unfortunately it sounds like what you're trying to accomplish is only possible using the scripted metrics aggregation: [https://github.com/elastic/kibana/issues/2646](https://github.com/elastic/kibana/issues/2646) You might also be able to accomplish this using Canvas. Do you have access to Canvas in your Kibana installation? ---
**Author:** ![wfaulk-bw](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wfaulk-bw/32/54114_2.png) [@wfaulk-bw](https://discuss.elastic.co/u/wfaulk-bw)\ **Post date:** [September 13, 2019, 3:43pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/3 "2019-09-13T15:43:32Z")
I do have Canvas, but I've never used it. Can you give me a quick idea of how to do this? Every time I try to set up a data source in Canvas, I get: > [essql] \> Unexpected error from Elasticsearch: [illegal\_argument\_exception] Illegal Capacity: -50 I'm currently trying to use Elasticsearch SQL as the data source, and this is my query: ```auto SELECT COUNT(*) AS count,beat.hostname,client_ip,dns.question.type FROM "packetbeat-*" WHERE beat.hostname = "ns01" GROUP BY client_ip ORDER BY count DESC LIMIT 25 ``` Which is not the final query I'd want, but I was trying to build up to it. ---
**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\ **Post date:** [September 13, 2019, 4:21pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/4 "2019-09-13T16:21:04Z")
Hmm looks like it might be due to differences in the mappings for your indices. It might be fixed in a newer version (see [https://github.com/elastic/elasticsearch/issues/43876](https://github.com/elastic/elasticsearch/issues/43876)). What version of Kibana are you using? ---
**Author:** ![wfaulk-bw](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wfaulk-bw/32/54114_2.png) [@wfaulk-bw](https://discuss.elastic.co/u/wfaulk-bw)\ **Post date:** [September 13, 2019, 5:16pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/5 "2019-09-13T17:16:06Z")
I'm on 7.2.0, which looks like it's prior to that fix. I'll look into upgrading. ---
**Author:** ![anelson-edge](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anelson-edge/32/52865_2.png) [@anelson-edge](https://discuss.elastic.co/u/anelson-edge)\ **Post date:** [September 16, 2019, 2:19pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/6 "2019-09-16T14:19:59Z")
I'd love to see your scripted metric if you select that route and get it working. If you stick with the Canvas solution, I'd also love to hear how you accomplished that. ---
**Author:** ![wfaulk-bw](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wfaulk-bw/32/54114_2.png) [@wfaulk-bw](https://discuss.elastic.co/u/wfaulk-bw)\ **Post date:** [September 26, 2019, 3:17pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/7 "2019-09-26T15:17:34Z")
I haven't been able to upgrade yet, but I did find that I was able to query specific indexes instead of index patterns, which would be fine for what I'm doing right now. However, I'm still unable to figure out how to do what I want. RIght now I have: ```sql SELECT client_ip,beat.hostname,COUNT(*) AS num FROM "packetbeat-6.5.1-2019.09.25" WHERE "direction" = 'in' AND "dns.question.type" = 'NAPTR' GROUP BY "client_ip","beat.hostname" ORDER BY num DESC ``` but that doesn't get me any further than a normal visualization. WIth another DB engine, I could do something like: ```sql SELECT client_ip, MAX(num) - MIN(num) FROM ( SELECT client_ip,beat.hostname,COUNT(*) AS num FROM "packetbeat-6.5.1-2019.09.25" WHERE "direction" = 'in' AND "dns.question.type" = 'NAPTR' GROUP BY "client_ip","beat.hostname" ORDER BY num DESC ) GROUP BY client_ip ``` But [Elastic doesn't allow subselects with `GROUP BY` clauses](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html). Am I looking in the wrong direction? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 24, 2019, 3:17pm UTC](https://discuss.elastic.co/t/visualizing-count-differences/199331/8 "2019-10-24T15:17:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.