Splunk Search

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

vspreethi17
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.

1 Solution

sk314
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.

View solution in original post

sk314
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.

sk314
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.

vspreethi17
Explorer

Thank you.

0 Karma

vspreethi17
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Observability | Use Synthetic Monitoring for Website Metadata Verification

If you are on Splunk Observability Cloud, you may already have Synthetic Monitoringin your observability ...

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...

.conf24 | Personalize your .conf experience with Learning Paths!

Personalize your .conf24 Experience Learning paths allow you to level up your skill sets and dive deeper ...