Splunk Search

How to format a website/service downtime duration calculation results

venky1544
Contributor

Hi all, I have the below dataset for a website.

Time,title, response code
01/10/2019 08:22 ABC_PORTAL 200
01/10/2019 08:24 ABC_PORTAL

01/10/2019 08:26 ABC_PORTAL

01/10/2019 08:28 ABC_PORTAL

01/10/2019 08:30 ABC_PORTAL

01/10/2019 08:32 ABC_PORTAL 503
01/10/2019 08:34 ABC_PORTAL 503
01/10/2019 08:36 ABC_PORTAL 503
01/10/2019 08:38 ABC_PORTAL 503
01/10/2019 08:40 ABC_PORTAL 200
01/10/2019 08:42 ABC_PORTAL 200
01/10/2019 08:44 ABC_PORTAL 200
01/10/2019 08:46 ABC_PORTAL 503
01/10/2019 08:48 ABC_PORTAL

01/10/2019 08:50 ABC_PORTAL

01/10/2019 08:52 ABC_PORTAL

01/10/2019 09:54 ABC_PORTAL

01/10/2019 09:56 ABC_PORTAL

01/10/2019 09:58 ABC_PORTAL 503
01/10/2019 10:00 ABC_PORTAL 503
01/10/2019 10:02 ABC_PORTAL 200
01/10/2019 10:04 ABC_PORTAL 200

In the above data the blank response code are connection timed out
I want to show the downtime duration of the website.
Below is my search:

sourcetype=| eval response_code=if(response_code="", "failed", response_code)
 | transaction title startswith="response_code=failed"  endswith="response_code=200" 
 |eval minutes=(duration/60)| stats sum(minutes) as "Total Downtime in minutes" by title,_time 

or

sourcetype=| eval response_code=if(response_code="", "failed", response_code)
 | transaction title startswith="response_code=503"  endswith="response_code=200" 
 |eval minutes=(duration/60)| stats sum(minutes) as "Total Downtime in minutes" by title,_time 

The problem is it is not calculating the correct duration I want to show the data in below manner
title , Downtime_start,Downtime_end , Duration,response_code

How can I achieve the above result or should I think of another way of representation any suggestions would be a great help!

0 Karma

to4kawa
Ultra Champion
| makeresults
| eval raw="01/10/2019 08:22 ABC_PORTAL 200
01/10/2019 08:24 ABC_PORTAL
01/10/2019 08:26 ABC_PORTAL
01/10/2019 08:28 ABC_PORTAL
01/10/2019 08:30 ABC_PORTAL
01/10/2019 08:32 ABC_PORTAL 503
01/10/2019 08:34 ABC_PORTAL 503
01/10/2019 08:36 ABC_PORTAL 503
01/10/2019 08:38 ABC_PORTAL 503
01/10/2019 08:40 ABC_PORTAL 200
01/10/2019 08:42 ABC_PORTAL 200
01/10/2019 08:44 ABC_PORTAL 200
01/10/2019 08:46 ABC_PORTAL 503
01/10/2019 08:48 ABC_PORTAL
01/10/2019 08:50 ABC_PORTAL
01/10/2019 08:52 ABC_PORTAL
01/10/2019 09:54 ABC_PORTAL
01/10/2019 09:56 ABC_PORTAL
01/10/2019 09:58 ABC_PORTAL 503
01/10/2019 10:00 ABC_PORTAL 503
01/10/2019 10:02 ABC_PORTAL 200
01/10/2019 10:04 ABC_PORTAL 200"
| makemv delim="
" raw
| mvexpand raw
| rex field=raw "(?<time>\d+/\d+/\d+ \d+:\d+) (?<title>\w+)"
| rex field=raw "(?<response_code>\d{3})$"
| eval _time=strptime(time,"%m/%d/%Y %H:%M")
| fillnull
| fields - time,- raw
| autoregress response_code as reg
| fillnull
| where reg!=response_code
| fields _time title response_code reg
| delta _time as duration
| autoregress _time as Downtime_start
| eval Downtime_end=_time
| where response_code!=200
| eval Duration = tostring(round(Downtime_end - Downtime_start),"duration")
| foreach Downtime_* 
    [eval <<FIELD>> = strftime(<<FIELD>>,"%m/%d/%Y %H:%M")]
| fields title , Downtime_start,Downtime_end , Duration,response_code
| eval response_code=if(response_code==0,"slow_connection",response_code)
| fields - _time

Hi, How about this?

0 Karma

sandeepmakkena
Contributor
 sourcetype=
 | eval startTime=if(response_code=503,_time,null())
 | eval endTime=if(response_code=200,_time,null())
 | stats min(startTime) as startTime max(endTime) as endTime by  Some_request_ID 
 | eval duration=endTime-startTime 

When you have large data transaction will take more resources and not accurate, try using an alternative.
Hope this helps, Thanks!

0 Karma

adonio
Ultra Champion

can you elaborate?
according to your data, you had downtime due to something (no value) between 8:24 to 8:30 and then you had another downtime due to 503 error code, until 8:38 how do you want that reported?
is it 14 minutes for the "something" and 8 minutes for 503? is it 6 and 8? 0 and 14?

0 Karma

venky1544
Contributor

HI Adonio
I'm still figuring out the best representation for this data the blank values are when there is a connection timed out.
expectated output:-
title , Downtime_start,Downtime_end , Duration,response_code
ABC_PORTAL ,01/10/2019,01/10/2019 08:30,slow_connection
ABC_PORTAL,01/10/2019 08:32,01/10/2019 08:40,503
ABC_PORTAL,01/10/2019 08:46 ,01/10/2019 10:02 ,503

not sure if this would be a correct representation

0 Karma
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...