I want to calculate last 3months count and take its average and need to compare with last month total count.
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.
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.
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")