Splunk Search

How to divide a field by its average?

brayps
Explorer

I have a time chart of count by field

 

 

| timechart count by field_name limit=0

 

 

I would like to divide each value in the statistics table by the mean of that field. 

Current Output:

Time A B
1

1

4

2 2 5
3 3 6

 

Desired Output:

Time A B
1 0.5 0.8
2 1 1
3 1.5 1.2

 

I can use a `foreach` to perform an operation on every column but I am having trouble configuring a subquery within that to calculate the mean and divide by it.

Labels (2)
0 Karma
1 Solution

johnhuang
Motivator

You can simplify the calculation by using stats. The caveat is that you need to set the span for stats to match the timechart interval, e.g. 1h in the example below:

 

<base_search>
| bucket _time span=1h
| stats count AS event_ct by _time field_name 
| eventstats avg(event_ct) AS avg_event_ct BY field_name
| eval ratio_of_avg=event_ct/avg_event_ct
| timechart span=1h max(ratio_of_avg) AS ratio_of_avg by field_name

 

 

View solution in original post

johnhuang
Motivator

You can simplify the calculation by using stats. The caveat is that you need to set the span for stats to match the timechart interval, e.g. 1h in the example below:

 

<base_search>
| bucket _time span=1h
| stats count AS event_ct by _time field_name 
| eventstats avg(event_ct) AS avg_event_ct BY field_name
| eval ratio_of_avg=event_ct/avg_event_ct
| timechart span=1h max(ratio_of_avg) AS ratio_of_avg by field_name

 

 

somesoni2
Revered Legend

Give this a try

| timechart count by field_name limit=0
|  rename * as orig_*
|  eventstats avg(orig_*) as avg_* 
|  foreach orig_* 
    [|  eval "<<FIELD>>"=round('<<FIELD>>'/'avg_<<MATCHSTR>>')] 
|  fields - avg_* 
|  rename orig_* as *
0 Karma

brayps
Explorer

Thank you for your response! I couldn't get this method to work as it was setting the _time field to the default 1970-01-01

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

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

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...