Splunk Search

How to filter non repeating events?

New Member

Hi,

This an output from a summary index. From this table, we need to filter based on which exception not occurred continuously. i.e. at 9:25, ORA-17002 and Connection Unavailable not occurred. Pls help us to find out how to filter only those events as of latest time.

_time   orig_index  ip  port    exceptions
2016-12-21T09:15:00.000-0800    applog  192.168.168.146 9900    GET Request - Method failed
2016-12-21T09:15:00.000-0800    applog  192.168.168.147 9800    GET Request - Method failed
2016-12-21T09:15:00.000-0800    applog  192.168.168.147 9900    GET Request - Method failed
2016-12-21T09:15:00.000-0800    applog  192.168.168.27   9300   Connection Unavailable
2016-12-21T09:15:00.000-0800    applog  192.168.168.27   9300   ORA-17002
2016-12-21T09:20:00.000-0800    applog  192.168.168.146 9900    GET Request - Method failed
2016-12-21T09:20:00.000-0800    applog  192.168.168.147 9800    GET Request - Method failed
2016-12-21T09:20:00.000-0800    applog  192.168.168.27   9300   Connection Unavailable
2016-12-21T09:25:00.000-0800    applog  192.168.168.146 9900    GET Request - Method failed
2016-12-21T09:25:00.000-0800    applog  192.168.168.147 9800    GET Request - Method failed
2016-12-21T09:25:00.000-0800    applog  192.168.168.147 9900    GET Request - Method failed
0 Karma
1 Solution

SplunkTrust
SplunkTrust

See if the following helps:

your base search
| eval value = "+"
| xyseries _time exceptions value
| fillnull value="-"
| streamstats current=f window=1 values(*) as previous_*
| foreach previous_* [eval "<<MATCHSTR>>" = '<<FIELD>>' . '<<MATCHSTR>>']
| fields - previous_*
| untable _time exceptions value
| where value = "+-" OR value = "-+"

This is the output I'm getting based on your sample above (see picture below).
I understand you just want to highlight those exceptions not happening sequentially and therefore:

  • You DO NOT care about ORA-17002 not happening at 09:25 because it DIDN'T happen at 09:20.
  • You DO care about ORA-17002 not happening at 09:20 because it DID happen at 09:15.

alt text

EDIT to include remaining fields as requested:

YOUR BASE SEARCH HERE
| append [ 
   YOUR BASE SEARCH AGAIN HERE
   | eval value = "+"
   | xyseries _time exceptions value
   | fillnull value="-"
   | streamstats current=f window=1 values(*) as previous_*
   | foreach previous_* [eval "<<MATCHSTR>>" = '<<FIELD>>' . '<<MATCHSTR>>']
   | fields - previous_*
   | untable _time exceptions value
   | where value = "+-" OR value = "-+"
   | eval isNonRepeating = 1
   | fields - value ]
| stats values(eval(if(isNonRepeating=1, _time, null()))) as _time, values(ip) as ip, values(orig_index) as orig_index, values(port) as port by exceptions
| where isNotNull(_time)
| rename _time as nonRepeatingTime
| eval nonRepeatingTime=strptime(nonRepeatingTime, "%Y-%m-%dT%H:%M:%S.%3N%z")
| fieldformat nonRepeatingTime=strftime(nonRepeatingTime, "%Y-%m-%d %H:%M:%S")

Please note I'm using strptime and strftime in the last two lines to present time in the format I want but you can probably ignore that.
Output I'm getting when I replicate this in my lab:

alt text

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

This "exception not occurred continuously" is based on ip/port OR just overall?

0 Karma

SplunkTrust
SplunkTrust

See if the following helps:

your base search
| eval value = "+"
| xyseries _time exceptions value
| fillnull value="-"
| streamstats current=f window=1 values(*) as previous_*
| foreach previous_* [eval "<<MATCHSTR>>" = '<<FIELD>>' . '<<MATCHSTR>>']
| fields - previous_*
| untable _time exceptions value
| where value = "+-" OR value = "-+"

This is the output I'm getting based on your sample above (see picture below).
I understand you just want to highlight those exceptions not happening sequentially and therefore:

  • You DO NOT care about ORA-17002 not happening at 09:25 because it DIDN'T happen at 09:20.
  • You DO care about ORA-17002 not happening at 09:20 because it DID happen at 09:15.

alt text

EDIT to include remaining fields as requested:

YOUR BASE SEARCH HERE
| append [ 
   YOUR BASE SEARCH AGAIN HERE
   | eval value = "+"
   | xyseries _time exceptions value
   | fillnull value="-"
   | streamstats current=f window=1 values(*) as previous_*
   | foreach previous_* [eval "<<MATCHSTR>>" = '<<FIELD>>' . '<<MATCHSTR>>']
   | fields - previous_*
   | untable _time exceptions value
   | where value = "+-" OR value = "-+"
   | eval isNonRepeating = 1
   | fields - value ]
| stats values(eval(if(isNonRepeating=1, _time, null()))) as _time, values(ip) as ip, values(orig_index) as orig_index, values(port) as port by exceptions
| where isNotNull(_time)
| rename _time as nonRepeatingTime
| eval nonRepeatingTime=strptime(nonRepeatingTime, "%Y-%m-%dT%H:%M:%S.%3N%z")
| fieldformat nonRepeatingTime=strftime(nonRepeatingTime, "%Y-%m-%d %H:%M:%S")

Please note I'm using strptime and strftime in the last two lines to present time in the format I want but you can probably ignore that.
Output I'm getting when I replicate this in my lab:

alt text

View solution in original post

0 Karma

New Member

Hi,

Thank you for your reply.

The stats is not working as I'm working on summary index. the summary index "summary_exceptions" is populated using,
"...| sistats count by index, host, cobrand_port, exceptions, threshold".

Now to send alert, we use:
index="summary_exceptions" | eval time=_time | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(time) | stats count by time, orig_index, orig_host, cobrand_port, exceptions, threshold | where count > threshold | lookup exceptions exceptions OUTPUT severity | rename cobrand_port as port orig_host as ip| eval severity=if(severity=1, "Critical", "Warning") | where severity="Critical"

The output is:
time orig_index ip port exceptions threshold count severity
2016-12-27 04:05:00 fe_server 172.17.22.107 9743 ORA-00028 1 2 Critical
2016-12-27 04:05:00 fe_server 172.17.22.146 9943 ALERT:Price GET Request - Method failed 1 8 Critical
2016-12-27 04:05:00 fe_server 172.17.22.147 9843 ALERT:Price GET Request - Method failed 1 10 Critical
2016-12-27 04:05:00 fe_server 172.17.22.147 9943 ALERT:Price GET Request - Method failed 1 4 Critical
2016-12-27 04:10:00 fe_server 172.17.22.146 9943 ALERT:Price GET Request - Method failed 1 8 Critical
2016-12-27 04:10:00 fe_server 172.17.22.147 9843 ALERT:Price GET Request - Method failed 1 4 Critical
2016-12-27 04:10:00 fe_server 172.17.22.147 9943 ALERT:Price GET Request - Method failed 1 4 Critical

We need to send recovery alert now for ORA-00028 since it din't occur at 4:10 with all other info like ip, port etc. Can you please update the query and let me know.

0 Karma

SplunkTrust
SplunkTrust

Hi, what's the exact query you are running? Can you post it here?

I just tried to replicate your sample above by using the following CSV:

time, orig_index, ip, port, exceptions, threshold, count, severity
2016-12-27 04:05:00, fe_server, 172.17.22.107, 9743, ORA-00028, 1, 2, Critical
2016-12-27 04:05:00, fe_server, 172.17.22.146, 9943, ALERT:Price GET Request - Method failed, 1, 8, Critical
2016-12-27 04:05:00, fe_server, 172.17.22.147, 9843, ALERT:Price GET Request - Method failed, 1, 10, Critical
2016-12-27 04:05:00, fe_server, 172.17.22.147, 9943, ALERT:Price GET Request - Method failed, 1, 4, Critical
2016-12-27 04:10:00, fe_server, 172.17.22.146, 9943, ALERT:Price GET Request - Method failed, 1, 8, Critical
2016-12-27 04:10:00, fe_server, 172.17.22.147, 9843, ALERT:Price GET Request - Method failed, 1, 4, Critical
2016-12-27 04:10:00, fe_server, 172.17.22.147, 9943, ALERT:Price GET Request - Method failed, 1, 4, Critical

And the same query I posted before but renaming time to _time:

| inputcsv mycsv.csv | rename time AS _time
| append [ 
   | inputcsv mycsv.csv | rename time AS _time
   | eval value = "+"
   | xyseries _time exceptions value
   | fillnull value="-"
   | streamstats current=f window=1 values(*) as previous_*
   | foreach previous_* [eval "<<MATCHSTR>>" = '<<FIELD>>' . '<<MATCHSTR>>']
   | fields - previous_*
   | untable _time exceptions value
   | where value = "+-" OR value = "-+"
   | eval isNonRepeating = 1
   | fields - value ]
| stats values(eval(if(isNonRepeating=1, _time, null()))) as _time, values(ip) as ip, values(orig_index) as orig_index, values(port) as port by exceptions
| where isNotNull(_time)
| rename _time as nonRepeatingTime

And it seems to be working fine. Output:

exceptions  nonRepeatingTime    ip  orig_index  port
ORA-00028   2016-12-27 04:10:00     172.17.22.107   fe_server   9743 

So not sure why you are having any problems.

By looking at your alert query, I came up with the following search (I'm sure this can be improved but I don't have access to your raw data):

index="summary_exceptions" 
| bucket span=1s _time
| stats count by _time, orig_index, orig_host, cobrand_port, exceptions, threshold 
| where count > threshold 
| lookup exceptions exceptions OUTPUT severity 
| rename cobrand_port as port orig_host as ip
| where severity=1
| eval severity="Critical"
| append [ 
   | search index="summary_exceptions" 
   | bucket span=1s _time
   | stats count by _time, orig_index, orig_host, cobrand_port, exceptions, threshold 
   | where count > threshold 
   | lookup exceptions exceptions OUTPUT severity 
   | rename cobrand_port as port orig_host as ip
   | where severity=1
   | eval severity="Critical"
   | eval value = "+"
   | xyseries _time exceptions value
   | fillnull value="-"
   | streamstats current=f window=1 values(*) as previous_*
   | foreach previous_* [eval "<<MATCHSTR>>" = '<<FIELD>>' . '<<MATCHSTR>>']
   | fields - previous_*
   | untable _time exceptions value
   | where value = "+-" OR value = "-+"
   | eval isNonRepeating = 1
   | fields - value ]
| stats values(eval(if(isNonRepeating=1, _time, null()))) as _time, values(ip) as ip, values(orig_index) as orig_index, values(port) as port by exceptions
| where isNotNull(_time)
| rename _time as nonRepeatingTime
0 Karma

New Member

Thank you. It works fine and this would be enough now to start with. Appreciate your help on this.

0 Karma

New Member

Thank you. appreciate your help. this is the query we need. Can you please let me know how to add the corresponding orig_index, ip and port too?. This is used for sending recovery alert(since the exception not happened again), we need to provide details like for which ip, port and index, the exception recovered.

0 Karma

SplunkTrust
SplunkTrust

I have updated my answer above to see if that helps

0 Karma

Splunk Employee
Splunk Employee

@rajkumar_2 - If javiergn's answer helped solve your question, please don't forget to click "Accept" below the answer. Thank you.

0 Karma

SplunkTrust
SplunkTrust

Try this.

... | stats count(exceptions) as count list(_time) as time list(orig_index) as orig_index list(ip) as ip list(exceptions) as exceptions | where count = 1 | ...
---
If this reply helps you, an upvote would be appreciated.
0 Karma