I have a bank transaction XML log with date, card number, and amount. I need print all transactions of the current day in an amount exceeding the average of the last month. Here is my log:
21052016
102212
5123451234564591
13337.12
I did this so far:
sourcetype="xml_test" |xmlkv | eval epochtime=strptime(Date, "%d%m%y") | eval DATE=strftime(epochtime, "%d-%m-%Y") |eval cardmask=substr(CC, 0,4)+"XXXXXX" | eval cardmask1=substr(CC, 11,12) | eval mask=cardmask+cardmask1| stats sum(Amount) as SUM by mask |eval alert=if(SUM > Amount, "OK","NOK")....
So, I am almost finished..but need a little help.
Thanks
Nikola
Your current search will return results, if today's amt is greater than TOTAL amount for all dates in the xml file. If you are looking for today greater than average of all amounts in the xml file, the change the sum(Amount) to avg(Amount) in the stats command. If you would like to compare with results of previous month's average, then you should try something like this
sourcetype="xml_test" |xmlkv | eval epochtime=strptime(Date, "%d%m%y") | where epochtime=relative_time(epochtime, "-1mon@mon")<=epochtime| eval DATE=strftime(epochtime, "%d-%m-%Y") |eval cardmask=substr(CC, 0,4)+"XXXXXX" | eval cardmask1=substr(CC, 11,12) | eval mask=cardmask+cardmask1| stats sum(Amount) as TodaySum by mask | appendcols [ search sourcetype="xml_test" |xmlkv | eval epochtime=strptime(Date, "%d%m%y") | where epochtime=relative_time(epochtime, "@d")<=epochtime AND relative_time(epochtime, "-1mon@mon")<=epochtime | eval DATE=strftime(epochtime, "%d-%m-%Y") |eval cardmask=substr(CC, 0,4)+"XXXXXX" | eval cardmask1=substr(CC, 11,12) | eval mask=cardmask+cardmask1| stats avg(Amount) as LastMonthAvg by mask ] eval alert=if(TodaySum > LastMonthAvg, "OK","NOK")