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?
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!
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!
Awesome solution that clearly demonstrates how to break up a time range and create counts in buckets of differing durations. Thank you!