Calculate daily and monthly average

[majeedk]

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?

[mayurr98 · Accepted solution]

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]

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