Splunk Search

Declaring a timeframe for event/stats and using the result in another search

ReufRamon
New Member

Hello everyone,

I have indexed a number of events which all have an "Amount" field. I have to search for events from the past week whose Amount value is greater than the average Amount value of the past month. The thing I have issues with is differentiating the timeframe in which the average Amount is calculated from the timeframe of the events that need to be found. I have tried using subsearches like this:

earliest=-1w@d [search earliest=-m@d | eventstats avg(Amount) as avgAmount | where Amount > avgAmount]
earliest=-1m@d | eventstats avg(Amount) as avgAmount | where Amount > avgAmount [search earliest=-1w@d]

but neither attempt worked. I am new to Splunk (if it isn't already obvious) and all suggestions are appreciated.

Tags (2)
0 Karma

ReufRamon
New Member

I managed to do it using join:

earliest=-1m@d | eventstats avg(Amount) as avgAmount | where Amount > avgAmount | join type=inner _time [search earliest=-1w@d]
0 Karma

jkat54
SplunkTrust
SplunkTrust

If you just want to compare the average of all the events last week versus last month it looks like this:

index=someIndex earliest=-1w@w latest=@w | stats avg(Amount) as lastWeekAvg | appendcols [ search index=someIndex earliest=-2m@m latest=-1m@m | stats avg(Amount) as lastMonthAvg]

But you want to do per event which means you need some by clause on your stats command. Say you're doing avg(Amount) by serverName...

index=someIndex earliest=-1w@w latest=@w | stats avg(Amount) as lastWeekAvg by serverName| appendcols [ search index=someIndex earliest=-2m@m latest=-1m@m | stats avg(Amount) as lastMonthAvg by serverName] | where lastWeekAvg > lastMonthAvg
0 Karma
Get Updates on the Splunk Community!

Index This | What did the zero say to the eight?

June 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

Splunk Observability Cloud's AI Assistant in Action Series: Onboarding New Hires & ...

This is the fifth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...