Splunk Search

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

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

Can anybody help me please?

Tags (2)
0 Karma
1 Solution

carasso
Splunk Employee
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.

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

View solution in original post

0 Karma

carasso
Splunk Employee
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.

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

0 Karma

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

0 Karma

jtrucks
Splunk Employee
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

the_wolverine
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!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...