I have a rather complicated query that go like this:
index=* source=* earliest=-4mon@mon latest=@mon RESPONSE_CODE="0"
| bin _time span=1mon
| stats count AS MonthTotal1 SUM(AMOUNT) AS MonthTotal BY MERCHANT_CODE, SUBMERCHANT_CODE, _time
| eval lastMonthStart = relative_time(now(),"-mon@mon")
| stats sum(eval(if(_time>=lastMonthStart,MonthTotal,0))) AS 1M_Total sum(eval(if(_time>=lastMonthStart,0, MonthTotal))) AS 3M_Total values(eval(if(_time>=lastMonthStart,MonthTotal1,null()))) AS Transaction sum(eval(if(_time<lastMonthStart,MonthTotal1,null()))) AS THREE_MONTHS BY SUBMERCHANT_CODE, MERCHANT_CODE
| eval 3M_Total_avg = round(3M_Total/3,2)
| eval RATE_Total = round((1M_Total/3M_Total_avg)*100,2)
| search RATE_Total>=200 OR RATE_Total=0
| join MERCHANT_CODE
[search index = *
| dedup MERCHANT_CODE
| table MERCHANT_CODE, BANK]
| table MERCHANT_CODE SUBMERCHANT_CODE, BANK, 1M_Total, RATE_Total
It seem complicated but the gist is I have to compare the lastest month total value of transaction to the average of 3 months before it for each sub-merchant, if the rate is >200%, show it in a table. The typical event go like this (I'll omit some unnecessary parts):
2021-10-25 13:52:33 TRANSACTION_ID="144479283"AMOUNT="10000", MERCHANT_TRANSACTION_CODE="17797161285", RESPONSE_CODE="0",MERCHANT_CODE="MOMOCE", SUBMERCHANT_CODE="22312"
Something to note:
- Each MERCHANT can have several SUBMERCHANT, or don't have one at all, so the field SUBMERCHANT is not always exist in events.
- Each MERCHANT have a BANK associate to it, but in another table.
I have a query just for SUBMERCHANT as a baseline to compare results, but somehow the query above, and even if I use (eventstats) instead of (stats), all show all different results than the baseline.
Does anyone have anyideal to untangle this mess, I'll really appreciate!
Not digging too deeply into the search - wouldn't timechart with timewrap be sufficient here? Maybe with an added foreach to sum three previous months?
I'm not very familiar with the use of timewarp or foreach, I only use the method I know of. Will timewarp and foreach solve my problems and how do I incorporate it in my search?
Timewrap, as the name suggests wraps the results of timechart around a specified timerange. So if you have some results for - let's say - half a year and you timewrap it aound 1 month, you'll get 6 values in each result row - one from first month, another from second month and so on.
Foreach lets you iterate over fields (in case of timechart|timewrap|foreach would let you to iteratw over your warp-span spaced results.