Splunk Search
Highlighted

Using the 'where' clause as a Custom Alert Trigger condition?

Communicator

I am attempting to set up an Alert which will trigger when average response times for various products over the week have increased by at least double in comparison to the previous week.

However it is not working out exactly as I had in mind.

My search query for the alert is as follows;

source="transactionLog" type="report" earliest=-1d@d latest=now 
| eval Day=if(_time>=relative_time(now(),"@d"),"Today","Yesterday") 
| chart avg(responsetime) over product by Day

And then I am using a custom trigger condition as follows;

search where Today>2*Yesterday 

However the problem is, whether I add the where clause to the end of my search or not, there are still over 700,000 events returned as results - so my alert notification returns all response times for ALL products (even the ones which did not see an increase).

ie; whether I include the where clause at the end of my search or not, there is still the same number of returned events?

This means the alert notification contains a whole lot of irrelevant data - I would ideally like to see ONLY the instances in the alert notification where the average response time has doubled, not all of the data.

I assume the WHERE clause does not actually filter out results which do not match the clause?

Is there a more suitable way to approach this?

Tags (4)
0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

SplunkTrust
SplunkTrust

Hello Alexander,
It looks like, Splunk sends out the result of search if one of the result matches your condition in where clause. Ie, if one of the rows has a value Today>2*Yesterday, then it sends out the whole result of the search.

Try adding your condition ( where Today>2*Yesterday) to the main search itself and change the alert condition to if number of events is greater than 0

View solution in original post

Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

Communicator

@renjith.nair

Thank you for your response.

I am curious why anyone would choose to use the where clause if the entire result list is returned by the search query, rather than including the condition in the main search query as you suggested?

0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

Communicator

@renjith.nair

I have tested this and it still does not seem to work - as you can see, the Today and Yesterday are referencing time periods - so when I attempt to add this to the end of my search, it still yields all of the 700,000+ results;

source="transactionLog" type="report" earliest=-1d@d latest=now 
| eval Day=if(_time>=relative_time(now(),"@d"),"Today","Yesterday") 
| chart avg(responsetime) over product by Day
| search where Today>2*Yesterday 

The last line above is the new line.

Perhaps the fault lies in the logic within my search query..

0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

SplunkTrust
SplunkTrust

The last line should be | where Today>2*Yesterday . Then the main/alert search would only return the rows which are satisfying the where clause.

Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

Communicator

@somesoni2

This is how I had it set up initially as you had previously suggested on a different Answer.

The problem is that this does not work.

Whether I have that where clause on the end of the search or not, I still receive the same number of results (700,000+) including all results which do not fit the clause requirements..?

Hence why I have posted this question..

0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

SplunkTrust
SplunkTrust

When you say you're getting 700,000+ results, where are you checking this? What is the number of rows that you get in the "Statistics" tab when you run the search manually?

0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

Communicator

@somesoni2

Below the search input box:

** 697,139 events (1/1/17 12:00:00.000 AM to 1/9/17 2:19:19.000 PM)**

In the statistics tab there is just one result.

However the events tab has all of the events.

0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

Communicator

Which tab of data will trigger an alert??

The alert emails me back with all products and their response times for each day - regardless of whether they have had a 2x increase since yesterday @somesoni2

0 Karma
Highlighted

Re: Using the 'where' clause as a Custom Alert Trigger condition?

SplunkTrust
SplunkTrust

If you're running the search in the "Verbose Mode" (little dropdown below the search magnifying glass button), it shows all the events which were used to generate the summary, i.e. result of the base search. The number of records in the Statistics is the actual number of results returned by your full search.