topic Re: Alert on any value in any column which exceeds a threshold? in Splunk Search

Alert on any value in any column which exceeds a threshold?

chalak — Thu, 22 Feb 2018 12:53:05 GMT

I have list of IPs and a number of requests summarized in statistics tab with a following query:

| datamodel X Y search
    | timechart  span=1h count(request) as num by ip usenull=f useother=f limit=0

the output is in the following format :

Time   |    192.168.0.1 | 192.168.0.2  | 192.168.0.3 | ...
1:00   |       20       |     30       |     50      | ...
2:00   |       30       |     50       |    101      | ...
3:00   |       10       |     25       |     30      | ...

I would like to alert if any value in the entire table exceeds a given threshold? (eg. threshold > 100). How can I achieve this?

I have tried several ways, inlcuding: | search num > 100; however, that is not working.

Could you please help me out here.

Re: Alert on any value in any column which exceeds a threshold?

cmerriman — Thu, 22 Feb 2018 14:03:20 GMT

I suggest the foreach command, since this is a dynamic table.
http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Foreach

It will be something like:

| datamodel X Y search
| timechart  span=1h count(request) as num by ip usenull=f useother=f limit=0
| foreach *.* [|eval <<FIELD>>=if('<<FIELD>>'>100,'<<FIELD>>',null())]

a little fine tuning might need to be made depending on the end goal of the query.

Re: Alert on any value in any column which exceeds a threshold?

chalak — Mon, 26 Feb 2018 14:17:40 GMT

Thank you for the suggestion. It will leave only values that are higher than the threshold. However it keeps all the columns with all IPs that are not higher as well, with null values. Could you please suggest how I would be able to remove all the null values? (so I would be left only with table : 2:00am | 101 )

I have tried to use CASE with one expression instead of IF command, however my idea does not have the wanted effect as it also displays all columns including the null ones.

Re: Alert on any value in any column which exceeds a threshold?

cmerriman — Mon, 26 Feb 2018 14:49:33 GMT

| datamodel X Y search
 | timechart  span=1h count(request) as num by ip usenull=f useother=f limit=0
 | foreach *.* [|eval <<FIELD>>=if('<<FIELD>>'>100,'<<FIELD>>',null())]
 |transpose header_field=time|stats max(*) as *
 |transpose header_field=column column_name=time

you could try the above. it's going to have an ip in the column name, but that's not necessarily the ip that hit the thresholds. and i'm just grabbing the max threshold hit at each hour.

Re: Alert on any value in any column which exceeds a threshold?

chalak — Mon, 26 Feb 2018 15:18:34 GMT

I see your point, however I am trying to achieve a table with the information: IPs (which exceeded the threshold) + number of requests.

Time   |  192.168.0.3 | ...
 2:00   |       101      | ...

Would you be able to help me to achieve this kind of output?

Re: Alert on any value in any column which exceeds a threshold?

somesoni2 — Mon, 26 Feb 2018 17:29:07 GMT

Try like this. You can set your alert to fire when number of result from below search is greater than 0.

| datamodel X Y search
| bucket span=1h _time 
| stats  count(request) as num by _time ip 
| where num>100
| timechart span=1h values(num) by ip

Re: Alert on any value in any column which exceeds a threshold?

chalak — Wed, 28 Feb 2018 10:27:17 GMT

Exactly what i was looking for. Thank you.