How to calculate uptime rate comparing timestamps by status

user_5533745 0
Viewed 165

I have a table where I record http calls. Here's how my table looks like:

callTimestamp httpStatus endpoint
2021-04-01 06:00:00 200 https://someserver/someapi/v1/endpoint1
2021-04-01 10:21:11 200 https://someserver/someapi/v1/endpoint1
2021-04-01 10:25:00 500 https://someserver/someapi/v1/endpoint1
2021-04-01 11:33:15 200 https://someserver/someapi/v1/endpoint1
2021-04-01 11:34:31 200 https://someserver/someapi/v1/endpoint1
2021-04-01 11:35:22 500 https://someserver/someapi/v1/endpoint1
2021-04-01 12:22:54 200 https://someserver/someapi/v1/endpoint1
2021-04-01 10:21:11 200 https://someserver/someapi/v1/endpoint2
2021-04-01 10:25:32 500 https://someserver/someapi/v1/endpoint2
2021-04-01 10:59:12 200 https://someserver/someapi/v1/endpoint2

I need to calculate the uptime rate, which is:

  1. The total uptime rate (100%) in seconds is TIME_TO_SEC(TIMEDIFF(NOW(), MIN(callTimestamp)))

  2. Need to calculate the (downtime) difference between the record with httpStatus 500 and it's subsequent with httpStatus 200 grouped by endpoint. From the example above for endpoint1, I have two ocurrences:

    2021-04-01 11:33:15 - 2021-04-01 10:25:00 = 4095 seconds

    2021-04-01 12:22:54 - 2021-04-01 11:35:22 = 2852 seconds

Example of expected result
Considering
MIN(callTimestamp) = 2021-04-01 06:00:00
NOW() = 2021-04-01 22:00:00

TIME_TO_SEC(TIMEDIFF(NOW(), MIN(callTimestamp))) = 57600 seconds (this is my 100%)

endpoint1 = 57600 - 6947 = 50563 seconds uptime
endpoint2 = 57600 - 2020 = 55580 seconds uptime

uptimeRate endpoint
87.93 https://someserver/someapi/v1/endpoint1
96.49 https://someserver/someapi/v1/endpoint2

I don't have idea of how to calc the downtime by endpoint.

Any ideas of how could I do this?

P.S: I'm using MySQL 8.0.20

mysql-8.0 mysql sql
1 Answers

You can use lead()over() to calculate next callTimestamp and min()over() window function to select minimum callTimestamp. Then with group by and aggregation you can get what you are looking for.

Schema and insert statements:

 create table mytable(callTimestamp datetime,   httpStatus int, endpoint varchar(100));
 insert into mytable values('2021-04-01 10:21:11',200,  'https://someserver/someapi/v1/endpoint1');
 insert into mytable values('2021-04-01 10:25:00',500,  'https://someserver/someapi/v1/endpoint1');
 insert into mytable values('2021-04-01 11:33:15',200,  'https://someserver/someapi/v1/endpoint1');
 insert into mytable values('2021-04-01 11:34:31',200,  'https://someserver/someapi/v1/endpoint1');
 insert into mytable values('2021-04-01 11:35:22',500,  'https://someserver/someapi/v1/endpoint1');
 insert into mytable values('2021-04-01 12:22:54',200,  'https://someserver/someapi/v1/endpoint1');
 insert into mytable values('2021-04-01 10:21:11',200,  'https://someserver/someapi/v1/endpoint2');
 insert into mytable values('2021-04-01 10:25:32',500,  'https://someserver/someapi/v1/endpoint2');
 insert into mytable values('2021-04-01 10:59:12',200,  'https://someserver/someapi/v1/endpoint2');

Query:

 select endpoint, 100*(TIME_TO_SEC(TIMEDIFF(NOW(), max(mincalltime)))-sum(TIME_TO_SEC(TIMEDIFF(nexttime, calltimestamp))))/ TIME_TO_SEC(TIMEDIFF(NOW(), max(mincalltime))) uptimeRate
 
 
 from
 (select *,lead(calltimestamp)over (partition by endpoint order by calltimestamp)nexttime,
 min(calltimestamp)over(partition by endpoint order by calltimestamp) mincalltime
 from mytable 
 )t
 where httpstatus=500
 group by endpoint

Output:

endpoint uptimeRate
https://someserver/someapi/v1/endpoint1 98.8895
https://someserver/someapi/v1/endpoint2 99.6771

db<fiddle here

user_86516010
Related