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!

#### .conf23 Registration is Now Open!

Time to toss the .conf-etti &#x1f389; —  .conf23 registration is open!   Join us in Las Vegas July 17-20 for ...

#### Don't wait! Accept the Mission Possible: Splunk Adoption Challenge Now and Win ...

Attention everyone! We have exciting news to share! We are recruiting new members for the Mission Possible: ...

#### Unify Your SecOps with Splunk Mission Control

In today’s post, I'm excited to share some recent Splunk Mission Control innovations. With Splunk Mission ...