Splunk Search

How do you compare a previous average with the current actual count?

rohitmaheshwari
Explorer

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

whrg
Motivator

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
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...