Splunk Search

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

chalak
Path Finder

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.

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

Exactly what i was looking for. Thank you.

0 Karma

cmerriman
Super Champion

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.

0 Karma

chalak
Path Finder

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.

0 Karma

cmerriman
Super Champion
| 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.

0 Karma

chalak
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...