Custom query to create Kibana data table

Elastic Stack Kibana
1

Team,

How to create a Kibana custom graph (*in my case data table), the guidance here will be helpful.

Query::

  "aggs": {
        "group_by_name": {
          "terms": {
            "field": "Dimensions.keyword",
            "size": 50
          },
          "aggs": {
              "count_list": {
                  "filter": {
                        "term": {"MetricName.keyword": "Count"}
                     },
                  "aggs": {
                    "sum_agg": {
                      "sum": {
                        "field": "Statistics.Sum"
                      }
                    }
                  }
                },
              "5xx_list": {
                  "filter": {
                        "term": {"MetricName.keyword": "5XX"}
                     },
                  "aggs": {
                    "sum_agg": {
                      "sum": {
                        "field": "Statistics.Sum"
                      }
                    }
                  }
                },
                "diff_req": {
                    "bucket_script": {
                        "buckets_path": {
                          "totalCount": "count_list>sum_agg",
                          "total5xx": "5xx_list>sum_agg"
                        },
                        "script": "params.totalCount - params.total5xx"
                    }
                }
          }
        }
  },
  "size": 0,
  "fields": [
    {
      "field": "Date",
      "format": "date_time"
    }
  ],
  "script_fields": {},
  "stored_fields": [
    "*"
  ],
  "_source": {
    "excludes": []
  },
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "match_all": {}
        },
        {
          "range": {
            "Date": {
              "gte": "2021-03-05T00:00:00.000Z",
              "lte": "2021-03-05T23:59:00.000Z",
              "format": "strict_date_optional_time"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}
2

Which version of Kibana are you using?

Usualy to create a data table in Kibana you go to visualize and create a new data table visualization.
There you configure your inputs, aggregation and table layout.
After saving the object you can use it e.g. on a dashboard.
Could you explain a little more where you stuck?

3

Thanks for the response, for the below data dataset I was trying to create a data table that can represent total request("Aggregation of Count"), Total failed ("Aggregation of 5XX"), (total request - totally failed). Since COUNT & 5XX are in a different document,I am not able to do any calculation with them. for which I was trying to use Filter, Bucket Script aggregation and Bucket Sort to sort the result. the script is able to perform the calculation and give me the expected result in the console but I am not sure how the same should be applied from Kibana.

Sample Data

{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:33:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:34:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:35:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:36:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:37:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 8.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:38:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 15.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:39:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "Count", "Dimensions": "requests", "Date": "2021-03-05T10:40:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "5XX", "Dimensions": "requests", "Date": "2021-03-05T10:36:00+00:00", "Timestamp": 1614940560000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "5XX", "Dimensions": "requests", "Date": "2021-03-05T10:37:00+00:00", "Timestamp": 1614940620000, "Unit": "Count", "Statistics": {"Sum": 5.0}}
{"Namespace": "AWS/ApiGateway", "MetricName": "5XX", "Dimensions": "requests", "Date": "2021-03-05T10:38:00+00:00", "Timestamp": 1614940680000, "Unit": "Count", "Statistics": {"Sum": 6.0}}

final data table expectation

image

Inputs here will be helpful

4

image

I want to subtract metric aggregation of (Total_Request - 5xx_count), I am not able to find a method for that in Kibana.

@Felix_Roessel your inputs will be hepful

5

This is only possible within vega or maybe TSVB at the moment.
Watch out for the next releases. We may add some features that help you doing that.

6

what is the suggested approach for this? should I handle this data before pushing?
@Felix_Roessel

7

I think I would use a transform and store the aggregated data in an separate index.
Then you can use runtime fields to calculate your metrics on top of the aggrgates.
Finally use that data in a data table.

8

I read about transform a little earlier but could visualize how that will help in my case. ideally "MetricName": "5XX"'s "Statistics": {"Sum": 6.0} should be transformed as value for Count metric fro same Timestamp. Will be able to give a basic gist on this?.

@Felix_Roessel

9

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