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

[dorismustovic]

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 🙂

[woodcock · Accepted solution]

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 *

[dorismustovic]

@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).

[dorismustovic]

Actually it worked with this one:

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

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 🙂

[woodcock]

See updated answer above.

[to4kawa]

| 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.