Kibana: Range based on count

[megakoresh (Stanislav Simovski)]

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' 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?

[Marius_Dragomir]

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.

[Catherine_Liu]

@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.

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 to create a new range column that maps each total to a range using a switch function. Then this pipes into a pointseries 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 function.

[megakoresh (Stanislav Simovski)]

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.