Pipeline aggregations in kibana visualization

[misomij (Miso Mijatovic)]

Hi, I have documents like the following in the same index pattern:

{
    "timestamp": ...
    "user_id": "LUCA",
    "dealer": 123456789,
    "method": "search",
    "ip": 1.1.1.1,
    "search": "number='123456789'"
}
{
    "timestamp": ...
    "user_id": "LUCA",
    "dealer": 123456789,
    "method": "orderEntry",
    "ip": 1.1.1.1
}

I need to calculate these 2 values:

ratio = number of searches per dealer / number of orderEntry per dealer
gap = number of searches per dealer - number of orderEntry per dealer

I can do this in dev tools with this query:

{
  "size":0,
  "aggs":{
    "datehistogram":{
      "date_histogram":{
        "field":"timestamp",
        "interval":"day"
      },
      "aggs":{
        "User":{
          "terms":{
            "field":"user_id.keyword",
            "size":10
          },
          "aggs":{
            "search":{
              "value_count":{
                "field":"search.keyword"
              }
            },
            "termorder":{
              "filter":{
                "term":{
                  "method.keyword":"orderEntry"
                }
              }
            },
            "ratio":{
              "bucket_script":{
                "buckets_path":{
                  "searchcount":"search.value",
                  "termordercount":"termorder._count"
                },
                "script":"params.searchcount/params.termordercount"
              }
            },
            "gap":{
              "bucket_script":{
                "buckets_path":{
                  "searchcount":"search.value",
                  "termordercount":"termorder._count"
                },
                "script":"params.searchcount-params.termordercount"
              }
            }
          }
        }
      }
    }
  }
}

I can't find a way to display this in kibana, I tryed to play with the data table with no success
Can anyone help me with this?

Thanks in advance

[Brandon_Kobel]

Hey @misomij you mentioned that you played around with the DataTable to do so, ideally, what format would you like the data reported in?

The Time Series Visual Builder allows you to do calculations like these on time series data, but they don't support the table based output, similar to the data table.

[misomij (Miso Mijatovic)]

Thank you for responding. I would like to see something like this, in a table:

user_id     gap    ratio
LUCA        ...    ...
BRANDON     ...    ...

with the gap and ratio values calculated over the time span selected for the dashboard.
I would like to see fast the number, result of the operation, in a time series visualization I can't

[rubenvillar (Rubén Villar)]

Hi,
I really miss Pipeline Bucket Script Aggregrations in Kibana.

I would like to show % over total in a Data Table.

{
"query": {
"bool": {
"must": [
{
"query_string": {
"analyze_wildcard": true,
"query": "*"
}
},
{
"range": {
"DATE_REGISTRATION": {
"gte": 1352967660235,
"lte": 1510734060235,
"format": "epoch_millis"
}
}
}
],
"must_not":
}
},
"size": 0,
"_source": {
"excludes":
},
"aggs": {
"6": {
"terms": {
"field": "GROUP.keyword",
"size": 5,
"order": {
"_count": "desc"
}
},
"aggs": {
"5": {
"date_histogram": {
"field": "DATE_REGISTRATION",
"interval": "1y",
"time_zone": "Europe/Berlin",
"min_doc_count": 0
},
"aggs": {
"7": {
"cumulative_sum": {
"buckets_path": "_count"
}
},
"percentage_new_users": {
"bucket_script": {
"buckets_path": {
"my_var1": "_count",
"my_var2": "7"
},
"script": "params.my_var1 / params.my_var2"
}
}
}
}
}
}
}
}