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!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! 👏 Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...