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!

#### Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

#### Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

#### Take the 2021 Splunk Career Survey for \$50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...