Splunk Search

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

alexandermunce
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
1 Solution

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

woodcock
Esteemed Legend

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)
0 Karma

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

alexandermunce
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

alexandermunce
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

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

alexandermunce
Communicator

@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 
0 Karma

renjith_nair
Legend

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
---
What goes around comes around. If it helps, hit it with Karma 🙂

alexandermunce
Communicator

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

0 Karma

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

alexandermunce
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

somesoni2
Revered Legend

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
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
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...