Derived Column Example Formulas

You can use these example formulas for custom fields, otherwise known as Derived Columns, as inspiration when creating your own custom fields. The specific attributes in each example may or may not exist in your data and environment.

Each example contains:

Most of the example queries use VISUALIZE, WHERE, and GROUP BY clauses, which are located at the top of the Query Builder display.

  • VISUALIZE: Performs a calculation and displays a corresponding graph over time. Most VISUALIZE queries return a line graph while the HEATMAP visualization shows the distribution of data over time
  • WHERE: Filters based on attribute parameter(s)
  • GROUP BY: Groups fields by attribute parameter(s)

Determine the percentage of successful requests 

Determine the percentage of successful requests by using a Derived Column.

Create a Derived Column and enter the following function in the Derived Column Editor:

# Count a successful result as 1 and an error as 0. 
# Then multiply by 100 to get a percentage

MUL(
  IF(EQUALS($http.status_code, 200), 1, 0),
  100
)

To get the success rate, use the Derived Column name in a query, such as success-rate-derived-column:

VISUALIZE
AVG(success-rate-derived-column)

Find failures in sequential events 

Monitor the health of a pipeline-style process by using a Derived Column that tells the number of requests that failed to complete.

Create a Derived Column and enter the following function in the Derived Column Editor:

# Assign pipeline-step-1 events a value of 1 and the pipeline-step-2 events a value of -1.

IF(
  EQUALS($name, "pipeline-step-1"), 1,
  EQUALS($name, "pipeline-step-2"), -1,
  0
)

To determine the volume difference between events from the two pipeline steps, use the Derived Column name in a query, such as failed-pipeline-step-derived-column:

VISUALIZE
SUM(failed-pipeline-step-derived-column)

If there are an equal number of pipeline-step-1 events as pipeline-step-2 events, then the SUM will be zero. When there are fewer pipeline-step-2 events than pipeline-step-1 events, then the SUM will be a positive integer.

Find traces with missing root spans 

Find traces with missing root spans by creating a Derived Column and entering the following function in the Derived Column Editor:

# If trace.parent_id exists, then assign a value of 0, else assign a value of 1

IF(
  EXISTS($trace.parent_id), 0,
 1
)

To find traces with a missing root span, use the Derived Column name in a query, such as check-trace-parent-derived-column:

VISUALIZE GROUP BY HAVING
SUM(check-trace-parent-derived-column) trace.trace_id SUM(check-trace-parent-derived-column) = 0

The sum will return at least 1 if you have a root span and 0 if you have no root span.

Use regular expressions to visualize Kubernetes deployment metrics 

To visualize Kubernetes deployment metrics, create a Derived Column and enter the following function in the Derived Column Editor:

REG_VALUE($k8s.pod.name, `(.*)-.*-.*`)

To visualize Kubernetes deployment metrics, use the Derived Column name in a query, such as k8s.deploy.name.derived.column:

VISUALIZE WHERE GROUP BY
MAX(metrics.memory.usage) k8s.namespace.name = your-namespace-name k8s.deploy.name.derived.column
Note
Any other metric operations can be used instead of VISUALIZE MAX() in this query example.

Compare window sizes to screen sizes 

For front end developers, compare window sizes to screen sizes using a Derived Column.

Create a Derived Column and enter the following function in the Derived Column Editor:

DIV(
  MUL(
    $window_height,
    $window_width
  ),
  MUL(
    $screen_height,
    $screen_width
 )
)

To compare window sizes to screen sizes, use the Derived Column name in a query, such as screen-used-derived-column:

VISUALIZE WHERE
HEATMAP(screen-used-derived-column) screen-used-derived-column < 1

After running the query, if most of the data in the heatmap clusters in a band closer to the top of the display, then most users are already using the majority of their screens.

Group incoming data by content size 

Group, or bucket, incoming data by content size using a Derived Column. It is helpful to know that the IF Operator works very similar to a switch or case statement in other languages, allowing you to provide multiple outputs based on many separate conditions.

Create a Derived Column and enter the following function in the Derived Column Editor:

IF(
  LT($headers.Content-Length, 1000), "0:bytes",
  LT($headers.Content-Length, 1000000), "1:kbytes",
  LT($headers.Content-Length, 1000000000), "2:mbytes",
  "3:huge"
)

To group incoming data by content size, use the Derived Column name in a query, such as content-length-bucket-derived-column:

VISUALIZE GROUP BY
COUNT content-length-bucket-derived-column

This query can help answer questions about whether larger file uploads fail more often, and give a general idea of the distribution of file sizes across uploads.

Derive browser version from the user-agent header 

Derive the browser version from the user-agent header by using a Derived Column.

Create a Derived Column and enter the following function in the Derived Column Editor:

IF(
  REG_MATCH($http.user_agent, `Firefox/[0-9\.]+`), REG_VALUE($http.user_agent, `Firefox/[0-9\.]+`),
  REG_MATCH($http.user_agent, `Chrome/[0-9\.]+`), REG_VALUE($http.user_agent, `Chrome/[0-9\.]+`),
  REG_MATCH($http.user_agent, `Safari/[0-9\.]+`), REG_VALUE($http.user_agent, `Safari/[0-9\.]+`),
  REG_MATCH($http.user_agent, `Edg/[0-9\.]+`), REG_VALUE($http.user_agent, `Edg/[0-9\.]+`),
  REG_MATCH($http.user_agent, `Trident\/4.0`), "IE 8",
  REG_MATCH($http.user_agent, `Trident\/5.0`), "IE 9",
  REG_MATCH($http.user_agent, `Trident\/6.0`), "IE 10",
  REG_MATCH($http.user_agent, `Trident\/7.0`), "IE 11",
  "Unknown"
)

To derive the browser versions in use, use the Derived Column name in a query, such as browser-version-derived-column:

VISUALIZE GROUP BY
COUNT browser-version-derived-column

This query can help you answer questions about whether requests from particular browser types are failing more frequently.

Derive browser name from the user-agent header 

Derive the browser name from the user-agent header by using a Derived Column.

Create a Derived Column and enter the following function in the Derived Column Editor:

IF(
  REG_MATCH($http.user_agent, "Gecko/"), "Firefox",
  OR(
    REG_MATCH($http.user_agent, "Chrome/"),
    REG_MATCH($http.user_agent, "Chromium/")
  ), "Chrome",
  REG_MATCH($http.user_agent, "AppleWebKit/"), "Webkit",
  REG_MATCH($http.user_agent, "Trident/"), "IE",
  REG_MATCH($http.user_agent, "Edge/"), "Edge",
  "Unknown"
)

To derive the browser names in use, use the Derived Column name in a query, such as browser-name-derived-column:

VISUALIZE GROUP BY
COUNT browser-name-derived-column

This query can help you answer questions about whether requests from particular browser types are failing more frequently.

Derive browser operating system from the user-agent header 

Derive the browser operating system (OS) from the user-agent header by using a Derived Column.

Create a Derived Column and enter the following function in the Derived Column Editor:

IF(
  CONTAINS($http.user_agent, "Macintosh"), "Macintosh",
  CONTAINS($http.user_agent, "Windows"), "Windows",
  CONTAINS($http.user_agent, "Android"), "Android",
  CONTAINS($http.user_agent, "CrOS"), "ChromeOS",
  REG_MATCH($http.user_agent, "iPhone|iPad"), "iOS",
  CONTAINS($http.user_agent, "Linux"),"Linux",
  "Unknown"
)

To derive the browser operating systems in use, use the Derived Column name in a query, such as browser-os-derived-column:

VISUALIZE GROUP BY
COUNT browser-os-derived-column

This query can help you answer questions about whether requests from particular browser types are failing more frequently.

Derive browser architecture from user-agent header 

Derive browser architecture from the user-agent header by using a Derived Column.

Create a Derived Column and enter the following function in the Derived Column Editor:

IF(
  REG_MATCH($http.user_agent, "x86|Intel"), "Intel",
  REG_MATCH($http.user_agent, "aarch64|armv|Android"), "ARM",
  "Unknown"
)
Note
This Derived Column example assumes that Android has Arm architecture.

To derive the browser architecture in use, use the Derived Column name in a query, such as browser-architecture-derived-column:

VISUALIZE GROUP BY
COUNT browser-architecture-derived-column

This query can help you answer questions about whether requests from particular browser types are failing more frequently.