Visualizing count differences

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.

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

You might also be able to accomplish this using Canvas. Do you have access to Canvas in your Kibana installation?

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:

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.

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). What version of Kibana are you using?

I'm on 7.2.0, which looks like it's prior to that fix. I'll look into upgrading.