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

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

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...