Hello,
I am trying to write an SPL to do the below but hitting a road block. Can someone please help!!
Date randomNumber
8/1/18 127
8/1/18 155
8/1/18 115
8/1/18 115
8/2/18 150
8/2/18 143
8/2/18 151
9/1/18 102
9/1/18 117
9/1/18 198
9/2/18 122
9/2/18 111
9/2/18 139
9/2/18 184
10/1/18 148
10/1/18 121
10/1/18 108
10/2/18 160
10/2/18 166
Scenario:
Considering that the current month is October (10). I am trying to compare the current count of random numbers that I have received on the 10/1 and 10/2 to the average of the counts that I have received on the 1st and 2nd of September(09) and August(08).
The results that I would get would be:
day1Actual = 3 for October 1st
day2Actual = 2 for October 2nd
day1Average = 3.5 for August and September average (7 divided by 2)
day2Average = 3.5 for August and September average (7 divided by 2)
Hi! Here is a search for you to calculate day1Average, day2Average, day3Average etc.:
... | rex field=Date "(?<Month>\d+)/(?<Day>\d+)/(?<Year>\d+)"
| stats count as Count by Year,Month,Day | sort Year,Month,Day
| eventstats last(Month) as Current_Month last(Year) as Current_Year | where Month!=Current_Month OR Year!=Current_Year
| stats avg(Count) as DayAverage values(Month) as Months by Day
This will give you:
Day DayAverage Months
1 3.5 8,9
2 3.5 8,9
To get day1Actual and day2Actual you would need to replace the where clause with:
| where Month=Current_Month AND Year=Current_Year