Splunk Search

## How to calculate the average number of events with errors by field name?

Explorer

I am trying to calculate the average number of errors by calculating events(with error)/total events.
Here is my query

``````...| stats count(_raw) as Total| appendcols[search .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count as errors by FIELDNAME ]|eval average = errors/Total|sort -errors
``````

Result:

``````FIELDNAME  | errors |   Total|  average
================================
abc           10
def            2
ghi            2        30        0.0666
jkl            1
mno            1
``````

Expected Result

``````FIELDNAME   errors    Total  average
================================
abc            10       30      3.3
def             2       30      0.66
ghi             2       30      0.0666
jkl             1       30      0.33
mno             1       30      0.33
``````

my question is why total is not calculated for all the events? what logic I am missing here.

Thank you so much.

Tags (5)
1 Solution
Builder

Try this:

``````<your search> .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count AS errors by FIELDNAME | join [  ...| stats count(_raw) as Total ] | eval average = errors/Total|sort -errors
``````

Note: joins are expensive.

Builder

Try this:

``````<your search> .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count AS errors by FIELDNAME | join [  ...| stats count(_raw) as Total ] | eval average = errors/Total|sort -errors
``````

Note: joins are expensive.

Builder

I just went by your requirement.

Just read up the documentation about appendcols and join. Specifically, appendcols synopsis states that it "Appends the fields of the subsearch results to current results, first results to first result, second to second, etc." In your case, the subsearch returns just one event (the total stats count), and that is why it was getting appended to only one event from your main search. In case of join, all events are combined based on common field (if none specified)...

Hope this helps.

Explorer

Thank you.

Explorer

Can you point out the mistake in my query and the thought process that went when writing yours.

Get Updates on the Splunk Community!

#### Splunk Indexers — ext4 vs XFS filesystem performance

Summary While I did not initially set out to benchmark filesystem performance on our Linux-based Splunk ...

#### Digital Resilience Made Easier With GenAI

TECH TALKS Digital Resilience Made Easier With GenAI Thursday, September 5, 2024  |  11AM PST / 2PM ...

#### Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...