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

[chalak]

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.

[somesoni2 · Accepted solution]

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

[chalak]

Exactly what i was looking for. Thank you.

[cmerriman]

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.

[chalak]

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.

[cmerriman]

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

[chalak]

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?