# Custom query to create Kibana data table
**URL:** https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919
**Category:** Kibana
**Created:** [March 21, 2021, 9:57pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919 "2021-03-21T21:57:15Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@venkat\_swaminathan](https://discuss.elastic.co/u/venkat_swaminathan)
#### Post date: [March 21, 2021, 9:57pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/1 "2021-03-21T21:57:15Z")
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": []
> }
> }
> }
>
> ```
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [March 22, 2021, 5:41am UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/2 "2021-03-22T05:41:03Z")
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?
---
### Author:  [@venkat\_swaminathan](https://discuss.elastic.co/u/venkat_swaminathan)
#### Post date: [March 22, 2021, 9:22am UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/3 "2021-03-22T09:22:54Z")
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](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-bucket-filter-aggregation.html), [Bucket Script](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-pipeline-bucket-script-aggregation.html) aggregation and [Bucket Sort](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-pipeline-bucket-sort-aggregation.html) 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
Inputs here will be helpful
---
### Author:  [@venkat\_swaminathan](https://discuss.elastic.co/u/venkat_swaminathan)
#### Post date: [March 22, 2021, 2:09pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/4 "2021-03-22T14:09:05Z")
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
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [March 22, 2021, 2:56pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/5 "2021-03-22T14:56:58Z")
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.
---
### Author:  [@venkat\_swaminathan](https://discuss.elastic.co/u/venkat_swaminathan)
#### Post date: [March 22, 2021, 3:02pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/6 "2021-03-22T15:02:00Z")
what is the suggested approach for this? should I handle this data before pushing?
@Felix_Roessel
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [March 22, 2021, 3:08pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/7 "2021-03-22T15:08:27Z")
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.
---
### Author:  [@venkat\_swaminathan](https://discuss.elastic.co/u/venkat_swaminathan)
#### Post date: [March 22, 2021, 4:01pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/8 "2021-03-22T16:01:26Z")
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
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 19, 2021, 4:02pm UTC](https://discuss.elastic.co/t/custom-query-to-create-kibana-data-table/267919/9 "2021-04-19T16:02:15Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.