topic Re: How to show all current day transactions whose amount is higher than the previous month average in Splunk Search

How to show all current day transactions whose amount is higher than the previous month average

dorismustovic — Thu, 12 Dec 2019 21:49:58 GMT

Hi all,

I have a bank transaction XML log with DATE, CC, AMOUNT. I need to show all transactions of the current day whose amount is higher than the average transaction amount for this customer for the previous month.
Here is the log example:

I found one similar topic and tried this so far, but it doesn't work:

  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)+"******" | eval cardmask1=substr(CC, 11,12) | eval mask=cardmask+cardmask1| stats sum(AMOUNT) as TodaySum by mask | appendcols [ search sourcetype="..." |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)+"******" | eval cardmask1=substr(CC, 11,12) | eval mask=cardmask+cardmask1| stats avg(AMOUNT) as LastMonthAvg by mask ]  eval alert=if(TodaySum  > LastMonthAvg, "OK","NOK")

Please, I need help, got no more ideas.

Thank you 🙂

Re: How to show all current day transactions whose amount is higher than the previous month average

to4kawa — Sat, 14 Dec 2019 04:47:56 GMT

| makeresults 
| eval _raw="<DATE>11122019</DATE>
<TIME>000031</TIME>
<CC>2615710116889328</CC>
<AMOUNT>14972.19</AMOUNT>"
| appendpipe
    [| eval _raw="<DATE>10122019</DATE>
<TIME>000031</TIME>
<CC>2615710116889328</CC>
<AMOUNT>14972.19</AMOUNT>"]
| appendpipe
    [| eval _raw="<DATE>10112019</DATE>
<TIME>000031</TIME>
<CC>2615710116889328</CC>
<AMOUNT>14972.19</AMOUNT>"]
| appendpipe
    [| eval _raw="<DATE>09112019</DATE>
<TIME>000031</TIME>
<CC>2615710116889328</CC>
<AMOUNT>14972.19</AMOUNT>"]
`comment("the logic is blow")`
| xmlkv 
| eval epochtime=strptime(DATE, "%d%m%Y")
| eval Month_date=tonumber(strftime(epochtime,"%m"))
| eval cardmask=substr(CC, 0,4)+"******" 
| eval cardmask1=substr(CC, 11,12) 
| eval mask=cardmask+cardmask1 
| eventstats sum(AMOUNT) as TodaySum by mask epochtime
| eventstats avg(TodaySum) as Month_avg by mask Month_date
| table epochtime mask TodaySum Month_avg Month_date
| rename epochtime as _time
| eventstats values(eval(if(tonumber(strftime(now(),"%m")) -1 == Month_date,Month_avg,NULL))) as prev_Mon_avg by mask

It wasn't actual data, so I could only do this.
please try with earliest=-1month@month and your search.

Re: How to show all current day transactions whose amount is higher than the previous month average

woodcock — Sat, 14 Dec 2019 18:13:39 GMT

First of all, FIX YOUR TIMESTAMP SETTINGS so that _time is correct, then do this:

index=Myindex sourcetype="Mysourcetype" earliest=@d latest=now
|eval cardmask=substr(CC, 0,4) . "**" 
| eval cardmask1=substr(CC, 11,12) 
| eval maskCC=cardmask+cardmask1
| eval AVG = [search index=Myindex sourcetype="Mysourcetype" earliest=-1mon@mon latest=@mon | stats avg(AMOUNT) as avg | return $avg ]
| where AMOUNT > AVG
| table *

Re: How to show all current day transactions whose amount is higher than the previous month average

dorismustovic — Sun, 15 Dec 2019 12:38:28 GMT

@woodcock I tried to do that, but it also doesn't work: Error in 'where' command: The expression is malformed. A comparison term is missing.
I have 600 000 events/transactions (and at least 100 000 different customers) and I have to search for all who satisfy the condition (all transactions of the current day whose amount is higher than the average transaction amount for this customer for the previous month), not one by one (NOT specifying exact customer and then searching only for him).

Re: How to show all current day transactions whose amount is higher than the previous month average

dorismustovic — Sun, 15 Dec 2019 14:01:43 GMT

Actually it worked with this one:

It works perfectly, but can You please tell me how to show average in my table also (now it shows maskCC and current day AMOUNT, but I would like to show last month average also)?

Thank You in advance 🙂

Re: How to show all current day transactions whose amount is higher than the previous month average

woodcock — Sun, 15 Dec 2019 15:22:01 GMT

See updated answer above.