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
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:
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:
This "exception not occurred continuously" is based on ip/port OR just overall?
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:
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:
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.
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
Thank you. It works fine and this would be enough now to start with. Appreciate your help on this.
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.
I have updated my answer above to see if that helps
@rajkumar_2 - If javiergn's answer helped solve your question, please don't forget to click "Accept" below the answer. Thank you.
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 | ...