Visualizing count differences

Elastic Stack Kibana
1

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.

2

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?

3

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.

4

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?

5

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

6

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.

7

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:

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:

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.

Am I looking in the wrong direction?

8

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