Splunk Search

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

Engager

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.

Tags (2)
1 Solution
Splunk Employee

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.

Splunk Employee

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.

Legend

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.

Splunk Employee

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.

--
Jesse Trucks
Minister of Magic
Champion

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

Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...