Splunk Search
Highlighted

How to edit my search to display all events of the current day with a sum exceeding the average of the last month?

Explorer

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

0 Karma
Highlighted

Re: How to edit my search to display all events of the current day with a sum exceeding the average of the last month?

Legend

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")
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.