Percentage Calculation in kibana

Elastic Stack Kibana
1

Hi Team,

Our goal is to build table in the below format

Marketing Operating Count Total AF FL Total(%) AF(%) FL(%)
UA UA 9415 24 17 7 0.25 0.18 0.07
UA LH 47 6 6 0 12.77 12.77 0

We are able to create below format using data-table

Marketing Operating Count Total AF FL
UA UA 9415 24 17 7
UA LH 47 6 6 0

Percentages should be calculated w.r.t Count

We tried with scripted fields for calculating percentage, but couldn't calculate percentage and we got to know that scripted fields are calculated for each document due to which we couldn't achieve it.

Please refer below for more details - Division in Scripted field

Our current version of kibana is 7.4.2. Could you please suggest best possible way to implement this.

Also do let us know if we can achieve this by taking latest kibana version.

Thanks & Regards,
Ramadevi

2

The TSVB table visualization allows you to calculate percentages of other columns, but you can't use multiple buckets (like Marketing and Operating in your example).

I think right now this is only possible in Canvas as you can use SQL there to query your table.

3

Hi Joe,

Our goal is to implement table and send mail with table.

When we checked Canvas, we could only see download format as pdf but not datatable.

Could you please suggest.

Also we are in 7.4.2 version, could you please suggest if we have an option for sending automated mail through kibana in any specific version of kibana.

4

When we checked Canvas, we could only see download format as pdf but not datatable.

Do you mean you can't export it as a CSV file? This is indeed not possible at the moment, but you can export the whole canvas sheet as PDF. This PDF will include the same information in a more readable form (also possible to automatically schedule this to send it via mail):

Screenshot 2020-08-18 at 15.47.41
Screenshot 2020-08-18 at 15.47.41642×736 55 KB

5

Could you please suggest procedure for this - also possible to automatically schedule this to send it via mail

6

Check out https://www.elastic.co/guide/en/kibana/current/automating-report-generation.html, it describes your use case

7

Query 1: We dont have Reporting/Watcher in version we are using.Actually we just have kibana url(7.4.2).How can we get reporting/watcher.Should we get latest version.

  1. We tried to download json format for table, but after download we could notice we are getting only sql query but not tabular data
{
  "css": ".canvasPage {\n\n}",
  "id": "workpad-80dc2782-0eba-476b-9056-f6c1db412469",
  "name": "Untitled Workpad",
  "width": 1080,
  "height": 720,
  "page": 0,
  "pages": [
    {
      "id": "page-76b9e017-7a80-45c6-a92f-ba8d7823d76d",
      "style": {
        "background": "#FFF"
      },
      "transition": {
      },
      "elements": [
        {
          "id": "element-04f9513a-0e94-4ae9-b7c7-4bd4c2367e46",
          "position": {
            "left": 20,
            "top": 20,
            "width": 500,
            "height": 300,
            "angle": 0
          },
          "expression": "filters\n| essql query=\"SELECT * FROM \\\"rmp_*\\\"\"\n| table\n| render"
        }
      ],
      "groups": [
      ]
    }
  ],
  "colors": [
    "#37988d",
    "#c19628",
    "#b83c6f",
    "#3f9939",
    "#1785b0",
    "#ca5f35",
    "#45bdb0",
    "#f2bc33",
    "#e74b8b",
    "#4fbf48",
    "#1ea6dc",
    "#fd7643",
    "#72cec3",
    "#f5cc5d",
    "#ec77a8",
    "#7acf74",
    "#4cbce4",
    "#fd986f",
    "#a1ded7",
    "#f8dd91",
    "#f2a4c5",
    "#a6dfa2",
    "#86d2ed",
    "#fdba9f",
    "#000000",
    "#444444",
    "#777777",
    "#BBBBBB",
    "#FFFFFF",
    "rgba(255,255,255,0)"
  ],
  "isWriteable": true,
  "assets": {
  },
  "@timestamp": "2020-08-18T15:33:29.898Z",
  "@created": "2020-08-18T15:32:54.943Z"
}

3.Is there a possibility to invoke kibana url to download tabular data from canvas

8

No, there is no way to do this. Reporting and watcher are under the gold license, so you need a subscription for this: https://www.elastic.co/subscriptions

9

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