Visualize a value calculated from the per-bucket sum of one field divided by the per-bucket sum of another?

[GrahamHannington]

I have data in JSON Lines format that contains statistics about the tasks performed by a system over an interval of time. (The specific definition of "task" here is not important.)

Each line of JSON Lines contains, among other fields:

• A time stamp representing the start of the time interval
• The total number of tasks in that interval
• The total response time of those tasks

For example, here is a snippet of one of the lines:

{
    "Start Interval": "2019-08-31T15:45:00.000Z",
    "#Tasks": 6,
    "Response Time Total": 2.2305
}

In Kibana (I'm using 7.9.2), I want to chart average response time per task, over time.

Specifically, I want to create a bar chart where the buckets use the Date Histogram aggregation with an auto interval, and the charted value for each bucket is based on the following formula:

Sum("Response Time Total") / Sum("#Tasks")

The Y-axis label would be "Average response time (per task)"; the X-axis label, "@timestamp per [interval]". How do I do this?

I've read some related Elastic documentation topics in this area (for example, "Pipeline aggregations"), but the solution is still not clear to me.

I think the solution involves defining, in the visualization metrics:

• Y-axis Sum of #Tasks
• Y-axis Sum of Response Time Total
• Y-axis... here, I'm not sure... some metric that specifies, under Advanced > JSON input, some JSON that refers to the above two metrics in a "script" formula

(I've already done this, using the same JSON Lines data, in a different analytics platform. Rhymes with "slam dunk" . Now I need to reproduce that visualization in Kibana.)

[GrahamHannington]

Ah. I've just read Kibana issue #4707, "Support Bucket Script Aggregation".

Seems I'm not the only one who wants this. Guess I'll go look at "Calculate" in TSVB. But there are also a bunch of similar per-bucket calculations that I want to visualize, that aren't time-based.

[GrahamHannington]

The Bucket Script aggregration in TSVB (was: Time Series Visual Builder) does what I want for this specific time-based use case (described in the topic title):

[GrahamHannington]

I've just tried Vega(-Lite) in Kibana for the first time.

Here, I'm charting the results of a bucket script, but the buckets aren't time-based (the following code is shown in HJSON, rather than JSON):

{
  $schema: https://vega.github.io/schema/vega-lite/v2.json
  title: Average response time by transaction ID
  width: container
  height: container
  data: {
    url: {
      %context%: true
      %timefield%: @timestamp
      index: cicspa-transum-*
      body: {
        aggs: {
          applid_buckets: {
            terms: {
              field: Tran
            }
            aggs: {
              sum_response: {
                sum: {
                  field: Response Time Total
                }
              }
              sum_tasks: {
                sum: {
                  field: "#Tasks"
                }
              }
              average_response: {
                bucket_script: {
                  buckets_path: {
                    response: sum_response
                    tasks: sum_tasks
                  }
                  script: params.response/params.tasks
                }
              }
            }
          }
        }
      }
      size: 0
    }
    format: {
      property: aggregations.applid_buckets.buckets
    }
  }
  mark: bar
  encoding: {
    x: {
      field: key
      type: nominal
      axis: {
        title: Transaction ID
      }
    }
    y: {
      field: average_response.value
      type: quantitative
      axis: {
        title: Seconds
      }
    }
  }
}

The resulting chart:

My first impression, especially after my first attempts at debugging this (I don't want to admit how long it took me to figure out that I needed to specify key as the X-axis field), is that creating these types of charts in Vega-Lite (with per-bucket calculations) is going to be more difficult, more time-consuming, than creating similar charts on other analytics platforms. I've got a basic chart, but now I need to figure out how to add the dynamic behavior that I take for granted with other ("standard") Kibana visualizations.

Note: In Kibana 7.9.2, I found it necessary to explicitly specify width: container and height: container. Otherwise, the visualization did not expand to fit the container. The default behavior contradicts the docs.

I'm also mindful of this qualification in the Kibana docs about Vega(-Lite):

This functionality is experimental and may be changed or removed completely in a future release.