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 |
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
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)
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 |
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
Wow, you save my time.
Thank you very very much, i am proud of your help.
Really nice