topic Re: How to filter non repeating events? in Splunk Search

How to filter non repeating events?

rajkumar_2 — Wed, 21 Dec 2016 18:27:19 GMT

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

Re: How to filter non repeating events?

richgalloway — Wed, 21 Dec 2016 19:30:00 GMT

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

Re: How to filter non repeating events?

javiergn — Wed, 21 Dec 2016 19:37:48 GMT

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.

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:

Re: How to filter non repeating events?

somesoni2 — Wed, 21 Dec 2016 19:59:29 GMT

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

Re: How to filter non repeating events?

rajkumar_2 — Thu, 22 Dec 2016 06:34:25 GMT

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.

Re: How to filter non repeating events?

javiergn — Fri, 23 Dec 2016 12:29:08 GMT

I have updated my answer above to see if that helps

Re: How to filter non repeating events?

aaraneta_splunk — Sat, 24 Dec 2016 04:47:44 GMT

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

Re: How to filter non repeating events?

rajkumar_2 — Tue, 29 Sep 2020 12:12:05 GMT

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.

Re: How to filter non repeating events?

javiergn — Sun, 01 Jan 2017 11:59:21 GMT

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

Re: How to filter non repeating events?

rajkumar_2 — Mon, 02 Jan 2017 13:20:29 GMT

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