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