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!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

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

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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