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

[alexandermunce]

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?

[woodcock]

This analogous search work for me:

index=_* earliest=-1d@d latest=now 
| eval Day=if(_time>=relative_time(now(),"@d"),"Today","Yesterday") 
| chart count over sourcetype by Day
| where Today<(2*Yesterday)

[renjith_nair · Accepted solution]

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

[alexandermunce]

@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?

[alexandermunce]

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

[renjith_nair]

Please have a look at the following link for better understanding : https://docs.splunk.com/Documentation/Splunk/6.5.1/Alert/AlertTriggerConditions#How_searches_and_tri...

So the condition can be used when you want all the results but only if some condition matches.

And in your original search , just add where Today>2*Yesterday without search

[alexandermunce]

@renjith.nair
@somesoni2

My mistake - I made a typo in the previous comment - my actual search query 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
| search Today>2*Yesterday 

[renjith_nair]

If Today and Yesterday are numbers, then just try

 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
 | where Today>2*Yesterday

[alexandermunce]

@renjith.nair
Today and Yesterday are not numbers - they are time related as you can see from the above search?

@somesoni2 advised that they are however related to the responsetime due to the chart function.

[renjith_nair]

As per your search , they are just avg of responsetime splitted across today and yesterday. So the values under Today and Yesterday should be some avgs. can you confirm that or just paste some sample values ? If you are doing an arithmatic like 2*Yesterday, then it should be some numbers

[somesoni2]

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.

[alexandermunce]

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

[somesoni2]

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?

[alexandermunce]

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

[somesoni2]

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.

[alexandermunce]

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