How to search for last 6 months for event indexed every month?

[sangs8788]

Hi,

I have a summary index which gets indexed once in a month. I have a query which runs based on current month looks back at last 6 months and provides me a report.  Is it possible to rewrite a query to show a trend which can go over each months' event and look back 6months of data for each month and provide a report?  

Here is the query which looks back at last 6 months from current month. I would like to do the same for all months (look back from each month) and provide a trend

index=summary source=sre_slo_BE_qlatency_permodule_monthly 
| where _time>=relative_time(now(),"-6mon@mon")
| eval Month=Month + "-" + Year
| chart values(p90Latency) as P90Latency by  Month, Module useother=f limit=10000 
| eval MonthYear=Month, Year=substr(Month,5,4), Month=substr(Month,0,3) 
| fields - Year 
| table MonthYear * 
| transpose 20 header_field=MonthYear, column_name=Module
| foreach *20*
     [ eval Max=case(Max>=if(isnull('<<FIELD>>'),0,'<<FIELD>>'),Max,true(),if(isnull('<<FIELD>>'),0,'<<FIELD>>'))]
| where Max>30000
| foreach *20*
    [eval <<FIELD>>=ROUND(('<<FIELD>>')/1000,2)]
| fields - Max
| rename Module as MainModule 
| eval RequestType="Business Event"
| lookup SLOHighToleranceLookup RequestType OUTPUTNEW Module | eval Module=if(isnull(Module), "null", Module) 
| where MainModule != Module
| fields - Module, RequestType
| rename MainModule as Module
| eval ViolationCount=0, LastViolatedMonth="", LastViolatedResponse=0, TotalViolationCount=0
| foreach *-2020 or *-2021
[ | eval LastViolatedMonth = if('<<FIELD>>'>30,"<<FIELD>>", LastViolatedMonth)
       , LastViolatedMonthNumber = substr(LastViolatedMonth, 0, 2)
       , ViolationCount=if(('<<FIELD>>'>30), ViolationCount+1, ViolationCount)
       , LastViolatedResponse=if('<<FIELD>>'>30,'<<FIELD>>', LastViolatedResponse)
       , Deviation=case(LastViolatedResponse>30,round(((LastViolatedResponse-30)/30)*100,1))
       , Priority = case(
                        (Deviation >= 100 AND ViolationCount >=1), "P1"
                      , ((Deviation >= 75 AND Deviation < 100) AND ViolationCount >=3), "P1"
                      , ((Deviation >= 75 AND Deviation < 100) AND (ViolationCount >= 0 AND ViolationCount < 3)), "P2" 
                      , ((Deviation >= 50 AND Deviation < 75) AND ViolationCount >= 3), "P2"
                      , ((Deviation >= 50 AND Deviation < 75) AND (ViolationCount >= 0 AND ViolationCount < 3)), "P3"
                      , ((Deviation >= 25 AND Deviation < 50) AND ViolationCount >= 3), "P3"
                      , ((Deviation >= 25 AND Deviation < 50) AND (ViolationCount >= 1 AND ViolationCount < 3)), "P4"
                      , ((Deviation > 0 AND Deviation < 25) AND ViolationCount >= 0), "P4"
                      )]
| eval LastViolatedMonthNumber = substr(LastViolatedMonth, 0, 2)
     , LastViolatedMonthYear = substr(LastViolatedMonth, 4, 4)
| eval LastViolatedMonth = case(LastViolatedMonthNumber==01, "Jan", LastViolatedMonthNumber==02, "Feb", LastViolatedMonthNumber==3, "Mar", LastViolatedMonthNumber==4, "Apr", LastViolatedMonthNumber==5, "May", LastViolatedMonthNumber==6, "Jun", LastViolatedMonthNumber==7, "Jul", LastViolatedMonthNumber==8, "Aug", LastViolatedMonthNumber==9, "Sep", LastViolatedMonthNumber==10, "Oct", LastViolatedMonthNumber==11, "Nov", LastViolatedMonthNumber==12, "Dec")  
| eval LastViolatedMonth=LastViolatedMonth + "-" + LastViolatedMonthYear
| fields Module, LastViolatedMonth, LastViolatedResponse, ViolationCount, Deviation, Priority, LastViolatedMonthNumber, LastViolatedMonthYear
| sort - LastViolatedResponse
| rename LastViolatedMonth as "Last Violation Month", LastViolatedResponse as "Last Violation p90ResponseTime (s)", Deviation as "Deviation (%)", ViolationCount as "Missed Count"
| eval CurrentMonth = strftime(now(), "%m"), CurrentYear= strftime(now(), "%Y"), ViolationMonthDifference=if(CurrentYear>LastViolatedMonthYear, (12-LastViolatedMonthNumber)+CurrentMonth, CurrentMonth-LastViolatedMonthNumber)
| where ViolationMonthDifference<=3
| eval Priority = if(Priority=="P1" AND LastViolatedMonthNumber != CurrentMonth-1 , "P2", Priority)
| fields - LastViolatedMonthNumber, LastViolatedMonthYear, CurrentMonth, CurrentYear, ViolationMonthDifference

Thanks