Splunk Search

Calculate daily and monthly average

majeedk
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 

Can someone please help?

0 Karma
1 Solution

mayurr98
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!

View solution in original post

mayurr98
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!

garymgreen
Engager

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

0 Karma
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 ...