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

 

Happy Splunking!

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)
Happy Splunking!
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

 

Happy Splunking!
0 Karma

bernanda
Explorer

Wow, you save my time.

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

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 ...