Splunk Search

Multiple stats for multiple key-values with a common prefix

mfietz
New Member

We have log entries with multiple key-value pairs. All of the keys I'm interested in have a common prefix and all of the values are decimal numbers. Unfortunately, not every entry contains all of the keys.
An entry could look like this:
<TIMESTAMP, LOGLEVEL etc.> prefix-<SUFFIX1>=<VALUE1>, prefix-<SUFFIX3>=<VALUE2>, ...

What I want is a table where the first column contains the union of all of the keys with the common prefix, the other columns should contain statistics like median, different percentiles, standard deviation.

What I got so far:
host=... index=... | rex max_match=100 "(?<field>prefix-\w+?)=(?<val>\d+)" | stats median(val) as "Median", perc90(val) as "90th percentile", perc99(val) as "99th percentile", min(val) as "min", max(val) as "max", stdev(val) as "Standard deviation", count(val) as "count" by field

The problem: Because not all log entries contain all keys, somewhere along the pipes the value 0 seems to be assumed for those missing keys. As a result, the statistical measures get totally skewed.

I would be thankful for any help.

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

How about this

your base search | table prefix-* | eval temp=1 | untable temp field value | stats median(val) as "Median", perc90(val) as "90th percentile", perc99(val) as "99th percentile", min(val) as "min", max(val) as "max", stdev(val) as "Standard deviation", count(val) as "count" by field

View solution in original post

0 Karma

somesoni2
Revered Legend

How about this

your base search | table prefix-* | eval temp=1 | untable temp field value | stats median(val) as "Median", perc90(val) as "90th percentile", perc99(val) as "99th percentile", min(val) as "min", max(val) as "max", stdev(val) as "Standard deviation", count(val) as "count" by field
0 Karma

mfietz
New Member

I'm impressed, that solved my issue!
Two small corrections: table prefix_* (Splunk changes hyphens to underscores when a field is recognized) and untable temp field val.
But apart from that: Brilliant! Thank you very much!

0 Karma

mfietz
New Member

fieldsummary seems to mostly do what I want, but it is missing percentiles and median

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...