Splunk Search

## Calculate daily and monthly average

Engager

Hi

Consider following data .

``````Date Country IP_Prefix
01/01/2018 UK 123.123
01/01/2018 UK 123.123
01/01/2018 UK 123.123
01/01/2018 UK 124.125
02/01/2018 UK 124.125
02/01/2018 France 99.200
02/01/2018 Fance 99.200
03/01/2018 Fance 99.200
04/01/2018 Fance 99.200
``````

What I want to get is 3 columns:
c1= average number of events per day from a IP_Prefix in last 7 days
c2 = average number of events per 7 days in previous 28 days
c3= c1 / c3

So the output would like below table (new column values are for example only):

``````IP_Prefix c1 c2 c3
123.123 2 1 2
123.125 1 2 0.5
99.200 1 1 1
``````

Tags (4)
1 Solution
Super Champion

hey you can try something like this

``````index=<your_index> IP_Prefix=* earliest=-28d latest=now |eval last7days= relative_time(now(),"-7d") |   eval marker = case(_time>= last7days, "seven") | stats count as last28days count(marker) as last7days by IP_Prefix | eval c2=round(last28days/4,2),c1=round(last7days/7,2),c3=round(c1/c2,2) |fields IP_Prefix c1 c2 c3
``````

LOGIC:

step1: c1=(total events in last 7 days by IP_Prefix)/7= average no of events per day

step2: c2=(total events in last 28 days by IP_Prefix)/4= average no of events per 7 days (NOTE: divide by 4 because need average per 7 days)

step3: c3=c1/c2

let me know if this helps!

Super Champion

hey you can try something like this

``````index=<your_index> IP_Prefix=* earliest=-28d latest=now |eval last7days= relative_time(now(),"-7d") |   eval marker = case(_time>= last7days, "seven") | stats count as last28days count(marker) as last7days by IP_Prefix | eval c2=round(last28days/4,2),c1=round(last7days/7,2),c3=round(c1/c2,2) |fields IP_Prefix c1 c2 c3
``````

LOGIC:

step1: c1=(total events in last 7 days by IP_Prefix)/7= average no of events per day

step2: c2=(total events in last 28 days by IP_Prefix)/4= average no of events per 7 days (NOTE: divide by 4 because need average per 7 days)

step3: c3=c1/c2

let me know if this helps!

Engager

Awesome solution that clearly demonstrates how to break up a time range and create counts in buckets of differing durations. Thank you!

Get Updates on the Splunk Community!

#### Set Up More Secure Configurations in Splunk Enterprise With Config Assist

This blog post is part 3 of 4 of a series on Splunk Assist. Click the links below to see the other ...

#### Observability Highlights | November 2022 Newsletter

November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

#### Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...