# Kibana: Range based on count

**URL:** <https://discuss.elastic.co/t/kibana-range-based-on-count/199431>\
**Category:** Kibana\
**Created:** [September 13, 2019, 1:03pm UTC](https://discuss.elastic.co/t/kibana-range-based-on-count/199431 "2019-09-13T13:03:52Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![megakoresh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/megakoresh/32/54166_2.png) [@megakoresh](https://discuss.elastic.co/u/megakoresh)\
**Post date:** [September 13, 2019, 1:03pm UTC](https://discuss.elastic.co/t/kibana-range-based-on-count/199431/1 "2019-09-13T13:03:52Z")

</div>

Hey. We have some host vulnerability data that we'd like to visualize and I am not sure how to do that. The data is in following format:

```
{
    "id": "5e7473a8-1eda-4a4e-a640-650db8f3c129",
    "timestamp": "2019-09-13T12:05:17+03:00",
    "host_id": 3,
    "hostname": "dank.memes",
    "os": "Unknown",
    "erratum_id": "RHSA-2019:5123",
    "synopsis": "Important: kernel security and bug fix update",
    "severity": "Important",
    "issue_date": "2019-09-11T00:00:00Z",
    "source": "satellite1.dank.memes",
    "report_id": "d770748e-5b7d-479e-97a3-52fa3a0c2369"
},
{
    "id": "c21ea41a-9cca-43c7-bc95-db478a705fcf",
    "timestamp": "2019-09-13T12:05:17+03:00",
    "host_id": 3,
    "hostname": "dank-memes.com",
    "os": "Unknown",
    "erratum_id": "RHSA-2019:2736",
    "synopsis": "Important: kernel security and bug fix update",
    "severity": "Important",
    "issue_date": "2019-09-11T00:00:00Z",
    "source": "satellite2.dank",
    "report_id": "d770748e-5b7d-479e-97a3-52fa3a0c2369"
},
{
    "id": "d112b3d8-65e9-4aae-8053-1650bd5a6467",
    "timestamp": "2019-09-13T12:05:17+03:00",
    "host_id": 4,
    "hostname": "jepsjops.com",
    "os": "Unknown",
    "erratum_id": "RHSA-2019:2736",
    "synopsis": "Important: kernel security and bug fix update",
    "severity": "Important",
    "issue_date": "2019-09-11T00:00:00Z",
    "source": "satellite1.dank.memes",
    "report_id": "d770748e-5b7d-479e-97a3-52fa3a0c2369"
}
...+~86K of these 

```

Every item is a combination of some patch and a host to which it applies. The question I would like answered is

"How many unique hosts have 0-10, 10-100, 100-1000+ patches applied?"

Conceptually it's simple - I need a Vertical Bar visualization where Y is Unique Count of hosts (by hostname or host\_id) and X is a range where every interval represents how many times the document with the same field value has been encountered. E.g. if doc['hosntame'] === '[swiggity.swag.com](http://swiggity.swag.com)' is found 6 times, it would end up in the first range (0-10), if it's found 60 times, it would end up in 10-100.

So essentially, in data terms - "How many times are repeated values encountered in data set?", bucketed into ranges.

Not too complicated and I can make this sort of visualization rather easily in excel. However with Kibana I struggle. Any suggestions?

---

<div class="post-metadata">

**Author:** ![Marius\_Dragomir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marius_dragomir/32/42087_2.png) [@Marius\_Dragomir](https://discuss.elastic.co/u/Marius_Dragomir)\
**Post date:** [September 13, 2019, 5:01pm UTC](https://discuss.elastic.co/t/kibana-range-based-on-count/199431/2 "2019-09-13T17:01:54Z")

</div>

As far as I know there is no way to do this in Kibana. It might be achievable in Canvas, i'll ping someone that knows more about it to get their input.

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [September 13, 2019, 8:05pm UTC](https://discuss.elastic.co/t/kibana-range-based-on-count/199431/3 "2019-09-13T20:05:40Z")

</div>

@megakoresh I believe this is doable in Canvas.

I've come up with an example Canvas expression using the Kibana logs sample data set, which I think matches your use case, that plots unique hosts vs range of patches.

 ![06%20PM](https://us1.discourse-cdn.com/elastic/original/3X/f/8/f814eabdb410ddeb0dd102ff06e9101f9a1bcdd1.png)

```auto
filters
| essql 
  query="SELECT COUNT(*) as patches_applied, host FROM \"kibana_sample_data_logs\"
GROUP BY host"
| mapColumn "range" fn={
    getCell patches_applied 
    | switch case={case if={all {gte 0} {lt 2000}} then="0-2000"}
      case={case if={all {gte 2000} {lt 4000}} then="2000-4000"}
      default="4000+"
  }
| sort by="range"
| pointseries x="range" y="unique(host)"
| plot defaultStyle={seriesStyle bars=0.4} yaxis={axisConfig tickSize=1}
| render

```

This element queries ES using ES SQL and grabs the total count per unique `host`. Then we use [`mapColumn`](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#mapColumn_fn) to create a new `range` column that maps each total to a range using a [`switch`](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#switch_fn) function. Then this pipes into a [`pointseries`](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#pointseries_fn) function which sets your x-axis to the `range` field and the y-axis to `unique(host)` which grabs the number of unique values from the `host` field and renders as a vertical bar chart using the [`plot`](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#plot_fn) function.

---

<div class="post-metadata">

**Author:** ![megakoresh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/megakoresh/32/54166_2.png) [@megakoresh](https://discuss.elastic.co/u/megakoresh)\
**Post date:** [September 16, 2019, 8:27am UTC](https://discuss.elastic.co/t/kibana-range-based-on-count/199431/4 "2019-09-16T08:27:04Z")

</div>

You are our hero, thanks! This works, but I can communicate this as feedback to Kibana team, that things like that are fairly common and should not be so complicated to create. Most of the people interested in graphs like this are not elasticsearch specialists like Catherine, and it's kind of strange that they need to export the data to csv first to make these graphs in excel because doing this in Kibana is so complicated.

---

<div class="post-metadata">

**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, 8:27am UTC](https://discuss.elastic.co/t/kibana-range-based-on-count/199431/5 "2019-10-14T08:27:14Z")

</div>

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