Splunk Search

How to get Start and End time from failed condition

bernanda
Explorer

Hi Splunk,

We have data like this: ( how to get the result like on the table StartError EndError and SumCall ?) I have tried with command ' eventstats first(datetime) as StartError last(datetime) as EndError by status_code' but the result is not like we expected.

Thank you

datetimeNameapp_versionstatus_codeStartErrorEndErrorsumcall
2021-01-25T11:22:34.848ZAAAA1.0.0403 Forbidden2021-01-25T11:22:34.848Z2021-01-25T12:01:45.478Z3
2021-01-25T11:24:23.242ZAAAA1.0.0403 Forbidden
2021-01-25T12:01:45.478ZAAAA1.0.0403 Forbidden
2021-01-25T10:07:25.753ZAAAA1.0.0200 OK-  
2021-01-26T07:55:51.835ZBBBB1.0.0401 Unauthorized2021-01-26T07:55:51.835Z2021-01-26T07:55:51.835Z1
2021-01-26T08:00:14.970ZBBBB1.0.0200 OK-- 
2021-01-25T13:48:21.898ZCCCC1.0.0403 Forbidden2021-01-25T13:48:21.898Z2021-01-25T13:48:40.131Z

5

2021-01-25T13:48:23.851ZCCCC1.0.0403 Forbidden
2021-01-25T13:48:25.338ZCCCC1.0.0403 Forbidden
2021-01-25T13:48:38.672ZCCCC1.0.0403 Forbidden
2021-01-25T13:48:40.131ZCCCC1.0.0403 Forbidden
Labels (3)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

Alright, If I understand that correctly, you need first time and last time of the records after the last successful request (200). If that's the case in the latest sample data , StartError of the 4th 500 should be 2021-01-27T09:54:00.223Z and EndError should be 2021-01-27T09:57:45.564Z

If the above assumption is correct , try using below search with your existing search

| eval flag=if(status_code="200 OK",1,0)|accum flag as group
| eventstats first(eval(if(status_code!="200 OK",datetime,null()))) as StartError ,last(eval(if(status_code!="200 OK",datetime,null()))) as EndError,count(eval(status_code!="200 OK")) as sumcall by Name,group
| fields - flag,group
| eval StartError=if(status_code=="200 OK","-",StartError) 
| eval EndError=if(status_code=="200 OK","-",EndError) 
| eval sumcall=if(status_code=="200 OK","-",sumcall)

Sample Result

renjith_nair_0-1611816061478.png

 

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Try adding this to your search and test

"Your search"
| eventstats first(eval(if(status_code!="200 OK",datetime,null()))) as StartError ,last(eval(if(status_code!="200 OK",datetime,null()))) as EndError,count(eval(status_code!="200 OK")) as sumcall by Name 
| eval StartError=if(status_code=="200 OK","-",StartError) 
| eval EndError=if(status_code=="200 OK","-",EndError) 
| eval sumcall=if(status_code=="200 OK","-",sumcall)
0 Karma

bernanda
Explorer

Hi  , 

Thank you very much for your reply. The command it works, but the result "sumcall" count all status != "200 OK"  and collect the datetime from first and last not from the first error and last error before status_code="200 OK"

I expected the "StartError" get datetime from the first of !="200 OK" and "EndError" get datetime from the last of ="200 OK" and "SumCall" only sum how many the Error before 200 OK and not all of !="200 OK"

Like this table: (For the 200 OK, there is no StartError, EndError, and SumCall

datetimeNameapp_versionstatus_codeStartErrorEndErrorSumCall
2021-01-27T08:51:53.559ZAAAA1.3.1200 OK---
2021-01-27T08:52:19.417ZAAAA1.3.1200 OK---
2021-01-27T08:53:32.198ZAAAA1.3.1200 OK---
2021-01-27T08:53:51.421ZAAAA1.3.1500 URL Open error2021-01-27T08:53:51.421Z2021-01-27T09:09:21.353Z2
2021-01-27T09:09:21.353ZAAAA1.3.1500 URL Open error2021-01-27T08:53:51.421Z2021-01-27T09:09:21.353Z2
2021-01-27T09:09:35.818ZAAAA1.3.1200 OK---
2021-01-27T09:10:10.618ZAAAA1.3.1200 OK---
2021-01-27T09:10:22.274ZAAAA1.3.1500 URL Open error2021-01-27T09:10:22.274Z2021-01-27T09:10:22.274Z1
2021-01-27T09:52:15.312ZAAAA1.3.1200 OK---
2021-01-27T09:52:26.469ZAAAA1.3.1200 OK---
2021-01-27T09:53:48.253ZAAAA1.3.1200 OK---
2021-01-27T09:54:00.223ZAAAA1.3.1500 URL Open error2021-01-27T08:53:51.421Z2021-01-27T08:53:51.421Z3
2021-01-27T09:57:41.246ZAAAA1.3.1500 URL Open error2021-01-27T08:53:51.421Z2021-01-27T09:57:45.564Z3
2021-01-27T09:57:45.564ZAAAA1.3.1500 URL Open error2021-01-27T08:53:51.421Z2021-01-27T09:57:45.564Z3
2021-01-27T09:58:19.772ZAAAA1.3.1200 OK---
2021-01-27T09:58:36.630ZAAAA1.3.1500 URL Open error2021-01-27T09:58:36.630Z2021-01-27T09:58:36.630Z1



0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Alright, If I understand that correctly, you need first time and last time of the records after the last successful request (200). If that's the case in the latest sample data , StartError of the 4th 500 should be 2021-01-27T09:54:00.223Z and EndError should be 2021-01-27T09:57:45.564Z

If the above assumption is correct , try using below search with your existing search

| eval flag=if(status_code="200 OK",1,0)|accum flag as group
| eventstats first(eval(if(status_code!="200 OK",datetime,null()))) as StartError ,last(eval(if(status_code!="200 OK",datetime,null()))) as EndError,count(eval(status_code!="200 OK")) as sumcall by Name,group
| fields - flag,group
| eval StartError=if(status_code=="200 OK","-",StartError) 
| eval EndError=if(status_code=="200 OK","-",EndError) 
| eval sumcall=if(status_code=="200 OK","-",sumcall)

Sample Result

renjith_nair_0-1611816061478.png

 

View solution in original post

0 Karma

bernanda
Explorer

Wow, you save my time.

Thank you very very much, i am proud of your help.
Really nice

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!