How to format a website/service downtime duration calculation results

[venky1544]

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!

[to4kawa]

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

[sandeepmakkena]

 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!

[adonio]

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?

[venky1544]

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