Fraud detection - how to compare last weeks average count with todays count and send Alert if to far apart

[michartmann]

Basically I need to construct a search that compare last weeks average count for "successful authorizations" with today count and shows that in a chart.
I also need to measure the gap between these to values and if the gap is larger than a set threshold then an Alert should be sent.

Can anybody help me please?

[carasso · Accepted solution]

Comparing week-over-week results used to a pain in Splunk, with complex date calculations. No more. Now there is a better way.

I wrote a convenient search command called "timewrap" that does it all, for arbitrary time periods.

... | timechart count span=1h | timewrap w 

That's it!
You can then average whatever columns you want.

http://apps.splunk.com/app/1645/

[lguinn2]

Try this:

"successful authorization" earliest=-8d@d
| eval todayStartTime = relative_time(now(),"@d")
| bucket _time span=1d
| stats sum(prevcounter) as dailyCount by _time
| eval TodaysCount = if (_time>=todayStartTime,dailyCount,null())
| eval dailyCount =  if (_time>=todayStartTime,null(),dailyCount)
| stats sum(TodaysCount) as Today avg(dailyCount) as LastWeeksAverage

You can set an alert based on Today > LastWeeksAverage and it will work. But it probably isn't what you want.

First, the count will start low early in the day and grow all day. Assuming a normal distribution for the daily counts, you will be alerted approximately every other day, some time in the evening most likely.

Here is a search that looks at the count for the prior hour and the 80th percentile for that hour over the prior 7 days.

"successful authorization" earliest=-8d@h latest=@h
| eval LastHour = relative_time(now(),"-1h@h")
| where strftime(_time,"%H")=strftime(LastHour,"%H")
| bucket _time span=1h
| stats sum(prevcounter) as hrCount by _time
| eval TodaysCount = if (_time>=LastHour,hrCount,null())
| eval hrCount =  if (_time>=LastHour,null(),hrCount)
| stats sum(TodayCount) as This_Hour p80(hrCount) as Hour_80th_Percentile by LastHour
| fieldformat LastHour = LastHour("%x %X")

You can alert based on This_Hour > Hour_80th_Percentile

AS jtrucks points out, summary indexing can make this more efficient. But this solution does avoid the limitations of using a subsearch.

[jtrucks]

For a non-commercial solution, see my short video presentation from SplunkLive this past May at https://vimeo.com/66779015 and the slides (linked in the comments on the video as well) at http://jtrucks.info/splk/SL2013DC-JesseTrucks.pdf

If your searches are fast enough to skip the summary table, you can simply do an appendcols with a search against your live data instead of using the summary index data as indicated in my talk and slides.

Essentially, do the search for last week, then inside appendcols do the search for this week. Shove the two results into different field names and then compare the two field names at the end.

[the_wolverine]

You should check out Prelert for something out-of-the-box that does this very easily: http://splunk-base.splunk.com/apps/68765/prelert-anomaly-detective