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

sangs8788 — Fri, 23 Jul 2021 08:15:54 GMT

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

topic How to search for last 6 months for event indexed every month? in Splunk Search

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