Splunk Search

Calculate last 3 months count average and compare the result with last month count

avni26
Explorer

I want to calculate last 3months count and take its average and need to compare with last month total count.
For example:
last Month, August = 350
July = 320
June = 347
May = 300
Need to apply condition in my base query that
Last month count < avg of last 3 month
350 < ((320+347+300/3))
So for that , need to calculate last 3 months count and last month count in same query.
Please suggest.

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your base search selecting data for last 4 months (current month+3 months before that)
| timechart span=1mon count
| eval Last3Months=if(_time<relative_time(now()-"1mon@mon"),count,0)
| eval LastMonth=if(_time=relative_time(now()-"1mon@mon"),count,0)
| stats avg(Last3Months) as Last3MonthAvg values(LastMonth) as LastMonth
| eval result=if(LastMonth<Last3MonthAvg ,"Some Message","Something else")

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your base search selecting data for last 4 months (current month+3 months before that)
| timechart span=1mon count
| eval Last3Months=if(_time<relative_time(now()-"1mon@mon"),count,0)
| eval LastMonth=if(_time=relative_time(now()-"1mon@mon"),count,0)
| stats avg(Last3Months) as Last3MonthAvg values(LastMonth) as LastMonth
| eval result=if(LastMonth<Last3MonthAvg ,"Some Message","Something else")
0 Karma

avni26
Explorer

@somesoni2
with small modification its worked. Thank you for your help 🙂

0 Karma

jpolvino
Builder

One way to accomplish this is to use appendcols.

earliest=-4mon@mon latest=-1mon@mon-1s (your search here)
| timechart span=1mon count as VolLast3
| stats avg(VolLast3) as AvgLast3
| appendcols [search earliest=-1mon@mon latest=@mon-1s (your search here)
| stats count as VolLastMonth]
| eval breach=if(VolLastMonth>AvgLast3,"Breached","OK")

The main search before appendcols gets each month's count from May, June, July and averages those into AvgLast3.

Then the appendcols uses a search to look at just the month of August, storing that count in VolLastMonth. Finally, a comparison is made.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...