Splunk Search

How to filter non repeating events?

rajkumar_2
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

javiergn
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

javiergn
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

0 Karma

rajkumar_2
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

javiergn
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

rajkumar_2
New Member

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

0 Karma

rajkumar_2
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

javiergn
SplunkTrust
SplunkTrust

I have updated my answer above to see if that helps

0 Karma

aaraneta_splunk
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

richgalloway
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...