How to schedule an alert every hour that pulls data from the last 24 hours to calculate a ratio, but only alert based on events in the last hour?

[markwymer]

Hi,

Sorry folks, I've really tried to find the answer but none of the various question/solutions or any examples that I've seen in docs and books seem to cover my requirement - or, of course, it's probably just that I don't understand alerting properly (which is more then likely 🙂 !! )

I have a report (that works fine) that does a subsearch that returns any IP address where the ratio of successful/failed logons falls within 0.1 <> 5 percent. It then reports any account that was successfully logged on to from that IP address. The timeframe for the report is 'Last 24 Hours'.

I would, now, like to convert that into an alert which should run every hour and alert me to any accounts that have been logged into (with the above search criteria) in the last hour but the success/fail ration should still be over the last 24 hours..

Unfortunately, when I set this up as a 'scheduled' alert for every hour, the search itself get restricted to all events in the last hour which doesn't report the search as intended as the success/fail ration is also only based on the last hour. If I set the alert to 'Real Time' I don't see any option to change the default of 'All Time(Real Time)'. All Time searches on this particular index can take a long time to execute (we index about 100,000,000 events per day).

How can I create an alert that scans the last 24 hours for events, but only alert on any that happened in the last hour?

Just in case it helps, the search for both the alert and the report is;-

index=ba_com_logs  Logon_Status="for" [ search index=ba_com_logs  logon | stats count(eval(Logon_Status="for")) as Successful, count(eval(Logon_Status="error")) as Failed by Logon_Source_IP | where ((Successful/Failed) * 100 > 0.000001) AND ((Successful/Failed) * 100 < 5) | fields Logon_Source_IP ] | eval Time=strftime(_time,"%Y/%m/%d - %H:%M:%S") | iplocation Logon_Source_IP|table Logon_Source_IP, Domain_Name, Logon_Username, Member_Tier_Status, City, Country, Time

Many thanks for any help,
Mark.

[hogan24]

I dont fully understand your specific search but I have a suggestion in regards to what your asking for....

In your base search and sub-search, hard-code the time range (earliest=-24h latest=now) to guarantee you get the time range you're looking for. (The cron schedule controls when the search is performed. The earliest/latest fields control the range of data pulled within the search.)

Get the specific hour of each transaction (including day/month/year in integer format so you can select the max to get the most recent hour)
| eval hour=strftime(_time,"%Y%m%d%H")

Get the previous hour as well...
(this assumes the alerts is run hourly on the hour and therefore looks at the previous hour. ie: Running the search at 10am will look at the 9am hour of data.)
| eval previousHour=tonumber(strftime(now(),"%Y%m%d%H"))-1

Then add both of these to your stats so you have a row for each hour...
| stats ........ by hour previousHour

Use eventstats to summarize/total everything if needed for the grand total over the previous 24 hours.

Then filter down to get only 1 hours worth of data in your final results and alert on that...
| where hour = previousHour

Hope that's helpful. Thanks.

[markwymer]

Hi, thanks for all your help, I tried your suggestions and through the search UI it works perfectly, unfortunately, when I schedule the same as an alert hourly, it still doesn't return any results.

I'm beginning to think that either I'm looking at the problem in the wrong way (more then likely) or that there is a bug in the scheduling engine of Splunk that over rules the search parameters.

I appreciate all your help - I'm, now, going to rethink what it is that we are trying to achieve.

Again, many thanks,
Mark

[markwymer]

Hi hogan24,

I tried your suggestion which is great when run manually but still produces no output when run from a scheduled alert. This still makes me think that the schedule is overruling any timeframes put into the search string.

My suspicion is that where I'm doing a count of successful and failed logins in the subsearch, it is, also, only searching for the last hour.

To try and explain my search, I'm using a subsearch to do a count by IP address, over the last 24 hours, of the successful and failed logons. This, then, returns a list of IP addresses that has a success/fail ratio of <= 5%.
The main search will then go through these IP addresses and return any successful logons but ONLY for the last hour. It will then alert on any account.

[somesoni2]

Try something like this (you can override the timerange for a search from the search query itself)

 index=ba_com_logs  Logon_Status="for" earliest=-1h@h [ search index=ba_com_logs  logon earliest=-24h@h | stats count(eval(Logon_Status="for")) as Successful, count(eval(Logon_Status="error")) as Failed by Logon_Source_IP | where ((Successful/Failed) * 100 > 0.000001) AND ((Successful/Failed) * 100 < 5) | fields Logon_Source_IP ] | eval Time=strftime(_time,"%Y/%m/%d - %H:%M:%S") | iplocation Logon_Source_IP|table Logon_Source_IP, Domain_Name, Logon_Username, Member_Tier_Status, City, Country, Time

[markwymer]

Hi Somesoni2,

Thanks for getting back to me but it appears that the cron schedule still overrides any timeframe settings within the search.

The email alert that I get still has results for the last 24 hours not the last hour.

[somesoni2]

The cron schedule just takes care of when the job is to run. There will text fields 'Start time' and 'End time' which will define the time range for which search will run when no time range is provided. The outer/base search did not have explicit time range (now it is added in my updated answer) so was taking from saved search's Start time and End time.

Try the updated answer with explicit time range within the query OR change the search's Start time and End time to last 1 hour.