How to get Start and End time from failed condition

[bernanda]

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

datetime	Name	app_version	status_code	StartError	EndError	sumcall
2021-01-25T11:22:34.848Z	AAAA	1.0.0	403 Forbidden	2021-01-25T11:22:34.848Z	2021-01-25T12:01:45.478Z	3
2021-01-25T11:24:23.242Z	AAAA	1.0.0	403 Forbidden
2021-01-25T12:01:45.478Z	AAAA	1.0.0	403 Forbidden
2021-01-25T10:07:25.753Z	AAAA	1.0.0	200 OK	-
2021-01-26T07:55:51.835Z	BBBB	1.0.0	401 Unauthorized	2021-01-26T07:55:51.835Z	2021-01-26T07:55:51.835Z	1
2021-01-26T08:00:14.970Z	BBBB	1.0.0	200 OK	-	-
2021-01-25T13:48:21.898Z	CCCC	1.0.0	403 Forbidden	2021-01-25T13:48:21.898Z	2021-01-25T13:48:40.131Z	5
2021-01-25T13:48:23.851Z	CCCC	1.0.0	403 Forbidden
2021-01-25T13:48:25.338Z	CCCC	1.0.0	403 Forbidden
2021-01-25T13:48:38.672Z	CCCC	1.0.0	403 Forbidden
2021-01-25T13:48:40.131Z	CCCC	1.0.0	403 Forbidden

[renjith_nair]

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)

[bernanda]

Hi renjith_nair , 

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

datetime	Name	app_version	status_code	StartError	EndError	SumCall
2021-01-27T08:51:53.559Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T08:52:19.417Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T08:53:32.198Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T08:53:51.421Z	AAAA	1.3.1	500 URL Open error	2021-01-27T08:53:51.421Z	2021-01-27T09:09:21.353Z	2
2021-01-27T09:09:21.353Z	AAAA	1.3.1	500 URL Open error	2021-01-27T08:53:51.421Z	2021-01-27T09:09:21.353Z	2
2021-01-27T09:09:35.818Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T09:10:10.618Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T09:10:22.274Z	AAAA	1.3.1	500 URL Open error	2021-01-27T09:10:22.274Z	2021-01-27T09:10:22.274Z	1
2021-01-27T09:52:15.312Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T09:52:26.469Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T09:53:48.253Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T09:54:00.223Z	AAAA	1.3.1	500 URL Open error	2021-01-27T08:53:51.421Z	2021-01-27T08:53:51.421Z	3
2021-01-27T09:57:41.246Z	AAAA	1.3.1	500 URL Open error	2021-01-27T08:53:51.421Z	2021-01-27T09:57:45.564Z	3
2021-01-27T09:57:45.564Z	AAAA	1.3.1	500 URL Open error	2021-01-27T08:53:51.421Z	2021-01-27T09:57:45.564Z	3
2021-01-27T09:58:19.772Z	AAAA	1.3.1	200 OK	-	-	-
2021-01-27T09:58:36.630Z	AAAA	1.3.1	500 URL Open error	2021-01-27T09:58:36.630Z	2021-01-27T09:58:36.630Z	1

[renjith_nair · Accepted solution]

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

[bernanda]

Wow, you save my time.

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