Kibana has created simple ways to efficiently process all your data — ask and answer questions and track the analytics flow. Many times, the answer to your question needs to be computed based on query data. Formulas allow you to create your own metrics by using mathematical operations to combine multiple aggregate fields.

In addition, moving and replaying data through time and space is an effective way to gain historical context and gain more insight into the present.

In the following sections, you’ll find 10 sample questions that you can answer using formulas, time-shifting and just-in-time browsing data, dashboard data and map visualization in Kibana. Try following your own data or using Kibana’s sample dataset. Any questions? Head over to our discussion forum.

On the list:

  1. Is the percentage of errors increasing?
  2. How does your performance compare to last week?
  3. How do these figures compare with their peers?
  4. What has had the biggest impact on my average?
  5. What is the original gain/loss compared to history?
  6. What is the percentage of gain/loss in past performance?
  7. How did the data get there?
  8. How do you interactively explore dashboards in space?
  9. What is the unit version of this metric?
  10. What are my industry-specific calculations for the previous period (e.g., “net recommended value”)?

Is the percentage of errors increasing?

Ratios are useful because they help show the proportion of the total population and answer questions that the raw count couldn’t answer: “Am I seeing an increase in this measure because I’m getting more aggregate data?” Ratios can be constructed by filtering a subset of the data and comparing it to the unfiltered total data. Try using KQL to act as this filter in a custom formula to build your ratios in real time.

The following example shows the ratio of unique users who encountered HTTP error codes (response codes greater than HTTP 200) to overall unique users. For more insight, the visualization takes the hour of the day as a row and the day of the week as a column. If these fields are missing from your data, you can always add them as run-time fields.

In this example, we need to import the kibanA_SAMPLE_datA_logs data set that comes with Kibana:

 

This imports the kibanA_SAMPLE_datA_LOGS dataset. We can use Lens to create a table visualization:

 

 

We can refer to the article “Kibana: Easily Creating Runtime Fields to Analyze Data in Lens and Discover – version 7.13” to create the required Runtime fields. Create a runtime field called day_of_week:

Above, we enter the following code:

ZonedDateTime input = doc['timestamp'].value;
String output = input.format(DateTimeFormatter.ofPattern('e')) + ' ' + input.format(DateTimeFormatter.ofPattern('E')); 

emit(output);
Copy the code

We can see a new day_of_week field added to Lens:

 

We can see the following screen:

 

Above, we enter the following script:

unique_count(clientip, kql='response.keyword > 200') / unique_count(clientip)
Copy the code

We end up with the visualization shown above.

Visual practice: Ratios

When visualizing ratios, apply the percentage value format. Use “ratio” in your metric name. If you’re visualizing something like error rates, it’s useful to show them in a table with an hour of the day and a day of the week. By ratio, averages can provide column-level insight (in this case, the average error rate for a particular day of the week).

How does your performance compare to last week?

Year-over-year comparisons over a period will give you a representation of the percentage now compared to the past, of which 100% is an exact match.

This example shows a 14-fold (1400%) increase in bandwidth compared to the previous week.

median(bytes) / median(bytes, shift='1w')
Copy the code

 

For a hands-on look at this, you can refer to my other article “Kibana: How to Use Kibana Time Offset, Advanced Formulas, and Dynamic Colors – 7.14”. In the article, there is a detailed description of how to do this.

Visualization practice: year-over-year over time

Comparison over time can be used for almost any visualization. It may be useful to look at time periods over time to see if the period change itself changes over time. This calculation outputs another percentage, so don’t forget the value format, the default option “Linear” is the least visually destructive if your data is sparse on the line chart.

How do these figures compare with their peers?

Using overall sums allows you to display any data as part of a total for easy comparison.

In the example below, we can see the “Ceph” image that generated the most data, and instead of looking at the count, we can see the values expressed as percentages.

count() / overall_sum( count() )
Copy the code

 

 

Visual practice: percentage of total

Using formulas to build percentages of the total population allows any visual representation of the ratio to the overall data, whereas previously only tree charts, pie charts, and doughnuts provided this functionality. When you want to make sure that each data point is readable, you can use horizontal bars to show the percentage of the total.

Tremaps, Donuts, and Pie automatically generate percentages of data, but readability can be missed

What has had the biggest impact on my average?

Functions such as the overall average allow you to make mathematical comparisons of the average values of all the values in the full report.

In this example, we can see which categories did better than the overall average of total sales, and both apparel categories did better than the overall average.

sum(taxless_total_price) - overall_average(sum(taxless_total_price))
Copy the code

Deviation between product category and average sales.

Visual practice: deviation

The use of horizontal bars is particularly useful when visualizing zero-centered metrics. Don’t forget to give a descriptive measure name.

What is the original gain/loss compared to history?

The time shift difference can be compared with the same index after the time shift. It’s useful to subtract the past from the present, which gives you a zero-centered visualization — positive numbers are past additions.

Here we have an example of average CPU usage on Kubernetes nodes compared to 6 hours ago. This example uses division to convert the NUMBER of CPU nanocores to the number of cores (note that run-time fields are a good way to add them as additional metric transformations that other users look up in the field list without having to do this in the formula).

(
   average(kubernetes.node.cpu.usage.nanocores)
   - average(kubernetes.node.cpu.usage.nanocores, shift='6h')
) / 1
Copy the code

 

The CPU core usage of the node is different from that of 6 hours ago.

Visual practice: time-shifting differences and changing units

Moving the axis to the top of the visualization helps when the most “relevant” number is the large number at the top of the visualization.

What is the percentage of gain/loss in past performance?

A slightly different view of the time-shift difference (% change) allows you to see the percentage increase in past values rather than the original difference. This is very popular when talking about growth.

Example percentage change calculation.

The following example shows the percentage change in Kubernetes CPU usage.

(
   (
   average(kubernetes.node.cpu.usage.nanocores)
   - average(kubernetes.node.cpu.usage.nanocores, shift='3d')
   ) /
   (
   average(kubernetes.node.cpu.usage.nanocores, shift='3d')
   )
)
Copy the code

Percentage change in node CPU usage compared with 3 days ago

Visual practice: percentage change

If you use the “percentage” value format, you do not need to multiply the value by 100. If there is room, value tags can help complement visualizations.

 

How did the data get there?

Maps are the best way to understand the geography of your data. But a map is just a snapshot — it could represent the last 15 minutes or the last year. In fact, if you look at a map minute by minute or day by day, your map may tell a different story. Using the time slider, you can play back the metrics and see how each incremental time unit changes over time. You may find patterns that have not been seen before or anomalies that require further investigation. The time slider shows how your data arrived at its location today.

Time slider control in Elastic Maps

You can refer to my article “Kibana: How to Use Kibana Time Offset, Advanced Formulas, and Dynamic Colors – 7.14” for practical instructions on this.

How do you interactively explore dashboards in space?

Sometimes, the answer to your question depends on where you are. If you’re creating a dashboard for someone else, consider enabling the map to be used as a filter for the entire dashboard. Try visualizing key metrics on the dashboard next to the map. This allows anyone to visualize non-spatial metrics while spatial filtering, and can be an effective way to make comparisons and find anomalies.

The map acts as a filter for the dashboard

 

What is the unit version of this metric?

Custom formulas can be used to reduce summary data to capture metrics per unit. It all depends on how your data is entered, what the metrics are, and what the “units” in your data are.

The following example shows cloud metric telemetry at regular intervals, such as every 30 seconds. The total number of metrics (e.g., CPU usage) and the number of events (requests) that the total represents, through a formula, we can determine the average CPU usage per request and do things like trends in new metrics over time to understand efficiency as load increases. In the final visualization, the Valley display system becomes more efficient under heavy load.

average(kubernetes.pod.cpu.usage.node.pct) //average metric
counter_rate(max(nginx.stubstatus.requests)) //per request
Copy the code

Average CPU per request over time

Visualization practice: per unit

If you are using per-unit metrics, be sure to include the aggregation and “per unit” of the metrics in use in the metric name. It can be useful to visualize units in a formula in a separate dashboard panel or series.

What are my industry-specific calculations for the previous period (e.g., “net recommended value”)?

Suppose your organization adopts a particular metric that is calculated in a particular way. For example, if your data contains survey questions, Some organizations like to calculate the “net referrer score” (scoring the lowest 2 options on the scale question) by calculating the percentage of the number of “referees” (scoring the highest 2 options on the scale question) minus the percentage of “critics” to get a score between -100% and 100%. This business metric is as simple as a formula, and because the formula is calculated at report time, you can easily redefine it as the business evolves its metrics and definitions.

Calculate industry-specific metrics, such as net recommendations

Have a data problem of your own?

Try these examples for yourself by signing up for a free trial of Elastic Cloud or downloading a free self-managed version of Elastic Stack. If you have additional questions about getting started, please visit the Kibana forum or check out the Kibana documentation guide.

Reference:

【 1 】 www.elastic.co/blog/kibana…