http_errors.sql

----------------------------------- ANALYZA CHYBOVOSTI VZHLEDEM KE SLUŽBĚ ---------------------------------------
--> extrakce zasazene sluzby (`serviceName`) podle jeji domeny (`requestDomain`)

-- DATA_CENTER:
-- * EU (eu1.concursolutions.com): cetnost statuscodes a errorrate kodu 500 dle service_name v REQUEST v EU domene pro vsechny entity za celou dobu
-- * US (www.concursolutions.com): cetnost statuscodes a errorrate kodu 500 dle service_name v REQUEST v US domene pro vsechny entity za celou dobu

SELECT  UPPER((SPLIT_PART(REQUESTPATH, '/', 3))) AS SERVICE_NAME
        , COUNT(*) AS COUNT_ALL
        , COUNT(CASE WHEN STATUSCODE = 200 THEN 1 ELSE NULL END) AS COUNT_200
        , COUNT(CASE WHEN STATUSCODE = 400 THEN 1 ELSE NULL END) AS COUNT_400
        , COUNT(CASE WHEN STATUSCODE = 403 THEN 1 ELSE NULL END) AS COUNT_403
        , COUNT(CASE WHEN STATUSCODE = 500 THEN 1 ELSE NULL END) AS COUNT_500
        , COUNT(CASE WHEN STATUSCODE = 502 THEN 1 ELSE NULL END) AS COUNT_502
        , COUNT(CASE WHEN STATUSCODE = 503 THEN 1 ELSE NULL END) AS COUNT_503
        , COUNT(CASE WHEN STATUSCODE = 504 THEN 1 ELSE NULL END) AS COUNT_504
        , ROUND(COUNT_500 + COUNT_502 + COUNT_503 + COUNT_504)  / COUNT_ALL * 100, 2) AS ERROR_RATE_5xx
FROM REQUEST
WHERE REQUESTDOMAIN IN ("$DATA_CENTER")
GROUP BY SERVICE_NAME
ORDER BY COUNT_500 DESC
;
--> u EU data centra je kod 500 vykazovan prakticky vyhradne u sluzby EXPENSE (celkem 2067)
--> kod 502 neni za cely casovy usek vykazan vubec
--> u US data centra je kod 500 vykazovan nejen u sluzby EXPENSE (celkem 1493), ale ve vyssim procentu i u cca 10ti dalsich sluzeb 
--> navic je ve vetsim poctu vykazan kod 502

----------------------------------------------------------------------------------------------------------------------
----------------------- ANALYZA PRVNIHO CASOVEHO USEKU S VYSOKYM VYSKYTEM KODU 500 -----------------------------------
--> tj. BETWEEN '2021-10-15 00:00:00.000' AND '2021-10-18 23:59:59.000'
----------------------------------------------------------------------------------------------------------------------

-- DATA_CENTER:
-- * EU (eu1.concursolutions.com): cetnost statuscodes a errorrate kodu 500 dle service_name v REQUEST v EU domene v postizenem casovem useku dle sluzby
-- * US (www.concursolutions.com): cetnost statuscodes a errorrate kodu 500 dle service_name v v REQUEST v US domene za celou dobu v postizenem casovem useku dle sluzby

SELECT  UPPER((SPLIT_PART(REQUESTPATH, '/', 3))) AS SERVICE_NAME
        , COUNT(*) AS COUNT_ALL
        , COUNT(CASE WHEN STATUSCODE = 200 THEN 1 ELSE NULL END) AS COUNT_200
        , COUNT(CASE WHEN STATUSCODE = 400 THEN 1 ELSE NULL END) AS COUNT_400
        , COUNT(CASE WHEN STATUSCODE = 403 THEN 1 ELSE NULL END) AS COUNT_403
        , COUNT(CASE WHEN STATUSCODE = 500 THEN 1 ELSE NULL END) AS COUNT_500
        , COUNT(CASE WHEN STATUSCODE = 502 THEN 1 ELSE NULL END) AS COUNT_502
        , COUNT(CASE WHEN STATUSCODE = 503 THEN 1 ELSE NULL END) AS COUNT_503
        , COUNT(CASE WHEN STATUSCODE = 504 THEN 1 ELSE NULL END) AS COUNT_504
        , ROUND(COUNT_500 + COUNT_502 + COUNT_503 + COUNT_504)  / COUNT_ALL * 100, 2) AS ERROR_RATE_5xx
FROM REQUEST
WHERE REQUESTDOMAIN IN ("$DATA_CENTER") AND TIMESTAMP_NEW BETWEEN '2021-10-15 00:00:00.000' AND '2021-10-18 23:59:59.000'
GROUP BY SERVICE_NAME
ORDER BY COUNT_500 DESC
;
--> u EU data centra bylo v postizenem casovem useku vykazano pro sluzbu EXPENSE 1966 kodu 500 (za celou dobu jich bylo vykazano 2067)
--> u US data centra bylo v postizenem casovem useku vykazano pro sluzbu EXPENSE 1249 kodu 500 (za celou dobu jich bylo vykazano 1493)
--> pro dalsi sluzby a kod 500 v tomto obdobi nebyly vykazany vyznamne vyssi pocty 
--> pro sluzbu EXPENSE bylo dale vykazano 260 kodu 502 (z celkovych 262)