rs_healcheck-beta.sql

-- RedShift HealthCheck ToolKit
-- Version 1.0
-- Developed by Bhuvanesh (@bhuvithedataguy)
-- Updates: https://thedataguy.in/

-- Drop the temp table for storing the checklist results
DROP TABLE IF EXISTS rstk_metric_result;

-- Create temp table for storing the checklist results
create temp table rstk_metric_result 
  ( 
     priority int,
     category varchar(50),
     finding  varchar(300), 
     details  varchar(65000),
     url      varchar(300), 
     value    varchar(1000),
     checkid  int
  ); 

-- Insert information row
INSERT INTO rstk_metric_result 
VALUES      (0, 
             current_timestamp, 
             'RedShift HealthCheck ToolKit', 
			 'To get help or add you own contribution join us at https://github.com/BhuviTheDataGuy/RedShift-ToolKit', 
			 'https://thedataguy.in', 
			 NULL, 
			 NULL); 

-- Tables without sort keys
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 1, 
       'Design', 
       'Tables without sort key', 
       'https://thedataguy.in/rskit/sortkeys', 
       Count(*) 
FROM   pg_catalog.svv_table_info 
WHERE  sortkey1 IS NULL
	   AND "schema" not like 'pg_temp%'; 

-- Sort key column compressed
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 2, 
       'Design', 
       'Sort key column compressed', 
       'https://thedataguy.in/rskit/sortkeycompress', 
       Count(*) 
FROM   pg_catalog.svv_table_info 
WHERE  sortkey1 IS NOT NULL 
       AND sortkey1_enc <> 'none'
       AND "schema" not like 'pg_temp%'; 

-- Sort key skew > 4
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 3, 
       'Design', 
       'Sort key skew', 
       'https://thedataguy.in/rskit/sortkeyskew', 
       Count(*) 
FROM   svv_table_info 
WHERE  sortkey1 IS NOT NULL 
       AND skew_sortkey1 > 4
       AND "schema" not like 'pg_temp%'; 

-- Tables with high Skew
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 4, 
       'Design', 
       'Tables with high Skew', 
       'https://thedataguy.in/rskit/tableskew', 
       Count(*) 
FROM   svv_table_info 
WHERE  diststyle LIKE 'KEY%' 
       AND skew_rows > 3
       AND "schema" not like 'pg_temp%';  

-- Tables without compression
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 5, 
       'Design', 
       'Tables without compression', 
       'https://thedataguy.in/rskit/tablecompress', 
       Count(*) 
FROM   pg_catalog.svv_table_info 
WHERE  encoded <> 'Y'
	   AND "schema" not like 'pg_temp%'; 

-- WLM queue wait time > 1min
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 6, 
       'WLM', 
       'WLM queue wait time', 
       'https://thedataguy.in/rskit/queuewait', 
       Count(*) 
FROM   stl_wlm_query w 
WHERE  w.total_queue_time / 1000000 > 60; 

-- WLM max connection hit
-- Credit: This query is taken from AWS RedShit Utilities with some changes to boost up performance on single node cluster.
-- Script name: wlm_apex_hourly
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
WITH generate_dt_series AS 
( 
       SELECT SYSDATE - (n * interval '1 minute') AS dt 
       FROM   ( 
                     SELECT row_number() over () AS n 
                     FROM   stl_scan limit 10080)), apex AS 
( 
         SELECT   iq.dt, 
                  iq.service_class, 
                  iq.num_query_tasks, 
                  count(iq.slot_count) AS service_class_queries, 
                  SUM(iq.slot_count)   AS service_class_slots 
         FROM     ( 
                         SELECT gds.dt, 
                                wq.service_class, 
                                wscc.num_query_tasks, 
                                wq.slot_count 
                         FROM   stl_wlm_query wq 
                         join   stv_wlm_service_class_config wscc 
                         ON     ( 
                                       wscc.service_class = wq.service_class 
                                AND    wscc.service_class > 4) 
                         join   generate_dt_series gds 
                         ON     ( 
                                       wq.service_class_start_time <= gds.dt 
                                AND    wq.service_class_end_time > gds.dt) 
                         WHERE  wq.userid > 1 
                         AND    wq.service_class > 4) iq 
         GROUP BY iq.dt, 
                  iq.service_class, 
                  iq.num_query_tasks), maxes AS 
( 
         SELECT   apex.service_class, 
                  trunc(apex.dt)           AS d, 
                  date_part(h,apex.dt)     AS dt_h, 
                  max(service_class_slots)    max_service_class_slots 
         FROM     apex 
         GROUP BY apex.service_class, 
                  apex.dt, 
                  date_part(h,apex.dt)) , final_result AS 
( 
         SELECT   apex.service_class, 
                  apex.num_query_tasks AS max_wlm_concurrency, 
                  maxes.d              AS day, 
                  maxes.dt_h 
                           || ':00 - ' 
                           || maxes.dt_h 
                           || ':59'             AS hour, 
                  max(apex.service_class_slots) AS max_service_class_slots 
         FROM     apex 
         join     maxes 
         ON       ( 
                           apex.service_class = maxes.service_class 
                  AND      apex.service_class_slots = maxes.max_service_class_slots) 
         GROUP BY apex.service_class, 
                  apex.num_query_tasks, 
                  maxes.d, 
                  maxes.dt_h 
         ORDER BY apex.service_class, 
                  maxes.d, 
                  maxes.dt_h) 
SELECT 7, 
       'WLM', 
       'WLM max connection hit', 
       'https://thedataguy.in/rskit/highconnection', 
       max(max_service_class_slots) AS maxv 
FROM   final_result 
WHERE  service_class >=6;

-- Number of WLM queue
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 8, 
       'WLM', 
       'Number of WLM queue', 
       'https://thedataguy.in/rskit/queue', 
       Count(*) 
FROM   stv_wlm_service_class_config 
WHERE  service_class BETWEEN 6 and 13; 

-- Auto WLM enabled
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 9, 
       'WLM', 
       'Auto WLM enabled', 
       'https://thedataguy.in/rskit/autowlm', 
       Count(*) 
FROM   stv_wlm_service_class_config 
WHERE  service_class >= 100; 

-- Max concurrency for a slot
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 10, 
       'WLM', 
       'Max concurrency for a slot', 
       'https://thedataguy.in/rskit/concurrencyslot', 
       Max(num_query_tasks) 
FROM   stv_wlm_service_class_config 
WHERE  service_class BETWEEN 6 AND 13; 

-- WLM commit queue wait 
INSERT INTO rstk_metric_result 
            ( 
                        checkid, 
                        category, 
                        finding, 
                        url, 
                        value 
            ) 
SELECT 11, 
       'WLM', 
       'WLM commit queue wait', 
       'https://thedataguy.in/rskit/commitqueue', 
       Max(queue_time) 
FROM   (SELECT Datediff(seconds, startqueue, startwork) AS queue_time 
        FROM   stl_commit_stats 
        WHERE  startqueue >= Dateadd(day, -7, current_date) 
        ORDER  BY queue_time DESC); 

-- Ghost rows
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
WITH raw_gh AS 
( 
         SELECT   query, 
                  tbl, 
                  perm_table_name , 
                  segment, 
                  SUM(a.rows_pre_filter-a.rows_pre_user_filter)AS ghrows 
         FROM     stl_scan a 
         WHERE    a.starttime > current_timestamp - interval '5 days' 
         and      perm_table_name NOT            IN ('Internal Worktable', 
                                                     'S3') 
         AND      is_rlf_scan = 'f' 
         AND      ( 
                           a.rows_pre_filter <> 0 
                  AND      a.rows_pre_user_filter <> 0 ) 
         GROUP BY SEGMENT, 
                  query, 
                  tbl, 
                  perm_table_name ), ran AS 
( 
         SELECT   *, 
                  dense_rank() over (PARTITION BY tbl ORDER BY query DESC, SEGMENT DESC) AS rnk
         FROM     raw_gh ), final_cte AS 
( 
         SELECT   max(query), 
                  SUM(ghrows)AS ghrows 
         FROM     ran 
         WHERE    rnk = 1 
         GROUP BY tbl) 
SELECT 12, 
       'Vacuum', 
       'Ghost rows', 
       'https://thedataguy.in/rskit/ghostrows', 
       SUM(ghrows) 
FROM   final_cte; 

-- Tables never performed vacuum (based on STL_Vacuum)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 13, 
       'Vacuum', 
       'Tables never performed vacuum', 
       'https://thedataguy.in/rskit/vacuum', 
       count(*) 
        FROM   pg_catalog.svv_table_info 
        WHERE  table_id NOT IN (SELECT table_id 
                                FROM   stl_vacuum); 

-- Table vacuum older than 5 days
INSERT INTO rstk_metric_result 
            ( 
                        checkid, 
                        category, 
                        finding, 
                        url, 
                        value 
            ) 
            WITH cte AS 
            ( 
                     SELECT   table_id, 
                              Max(eventtime)AS eventtime1 
                     FROM     stl_vacuum 
                     WHERE    status LIKE '%Finished%' 
                     GROUP BY table_id 
            ) 
   SELECT 14, 
          'Vacuum', 
          'Table vacuum older than 5 days', 
          'https://thedataguy.in/rskit/vacuum', 
          Count(*) 
   FROM   pg_catalog.svv_table_info 
   join   cte 
   ON     svv_table_info.table_id= cte.table_id 
   WHERE  cte.eventtime1>= current_date - interval '5 day';

-- Tables with tombstone blocks
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
WITH cte 
     AS (SELECT Trim(name) AS tablename, 
                Count(CASE 
                        WHEN tombstone > 0 THEN 1 
                        ELSE NULL 
                      END) AS tombstones 
         FROM   svv_diskusage 
         GROUP  BY 1 
         HAVING Count(CASE 
                        WHEN tombstone > 0 THEN 1 
                        ELSE NULL 
                      END) > 0 
         ORDER  BY 2 DESC) 
SELECT 15, 
       'Vacuum', 
       'Tables with tombstone blocks', 
       'https://thedataguy.in/rskit/tombstone', 
       Count(tablename) 
FROM   cte;  

-- Tables with missing stats
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 16, 
       'Vacuum', 
       'Tables with missing stats', 
       'https://thedataguy.in/rskit/stats', 
       Count(*) 
FROM   (SELECT plannode 
        FROM   stl_explain 
        WHERE  plannode LIKE '%missing statistics%' 
               AND plannode NOT LIKE '%redshift_auto_health_check_%' 
        GROUP  BY plannode); 

-- Tables with stale stats (> 5 percent)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 17, 
       'Vacuum', 
       'Tables with stale stats', 
       'https://thedataguy.in/rskit/stats', 
       Count(*) 
FROM   svv_table_info 
WHERE  stats_off > 5; 


-- Top sized tables 
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 18, 
       'Table', 
       'Top sized tables', 
       'https://thedataguy.in/rskit/specturm', 
       Count(*) 
FROM   pg_catalog.svv_table_info 
WHERE  pct_used >= 40; 

-- Table with high number of alerts (>3 alerts)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 19, 
       'Table', 
       'Tables with high number of alerts', 
       'https://thedataguy.in/rskit/alert', 
       Count("table") 
FROM   (SELECT "table", 
               Count(*) AS count 
        FROM   (SELECT Trim(s.perm_table_name)           AS table, 
                       Trim(Split_part(l.event, ':', 1)) AS event 
                FROM   stl_alert_event_log AS l 
                       left join stl_scan AS s 
                              ON s.query = l.query 
                                 AND s.slice = l.slice 
                                 AND s.segment = l.segment 
                WHERE  l.userid > 1 
                       AND l.event_time >= Dateadd(day, -7, Getdate()) 
                       AND s.perm_table_name NOT LIKE 'volt_tt%' 
                       AND s.perm_table_name NOT LIKE 'Internal Worktable' 
                GROUP  BY 1, 
                          2) 
        GROUP  BY 1) 
WHERE  count >= 3; 

-- Non scaned Tables (based on STL Scan)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 20, 
       'Table', 
       'Non scaned Tables', 
       'https://thedataguy.in/rskit/unusedtable', 
       Count(*) 
FROM   pg_catalog.svv_table_info 
WHERE  "table" NOT IN (SELECT DISTINCT ( perm_table_name ) 
                       FROM   pg_catalog.stl_scan); 

-- Tables without backup
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 21, 
       'Table', 
       'Tables without backup', 
       'https://thedataguy.in/rskit/nobackup', 
       Count(*) 
FROM   stv_tbl_perm 
WHERE  BACKUP = 0 and temp=0; 

-- Tables with fragmentation
-- Credit: This query is taken from AWS RedShit Utilities
-- Script name: v_fragmentation_info.sql
INSERT INTO rstk_metric_result 
            ( 
                        checkid, 
                        category, 
                        finding, 
                        url, 
                        value 
            ) 
SELECT 22, 
       'Table', 
       'Tables with fragmentation', 
       'https://thedataguy.in/rskit/fragmentation', 
       Count(tablename) 
FROM   ( 
              SELECT tbl,
               tablename, 
               dbname, 
               SUM(t_excess_blks) est_space_gain 
        FROM   (SELECT tbl, 
                       col, 
                       node, 
                       tablename, 
                       Trim(datname)                  AS dbname, 
                       SUM(excess_blks) * ( col + 1 ) AS t_excess_blks 
                FROM   (SELECT tbl, 
                               slice, 
                               col, 
                               Count(*) total_blks 
                        FROM   stv_blocklist 
                        WHERE  num_values > 0 
                        GROUP  BY 1, 
                                  2, 
                                  3) a 
                       join (SELECT tbl, 
                                    slice, 
                                    Max(col) AS col 
                             FROM   stv_blocklist 
                             GROUP  BY 1, 
                                       2) b USING (tbl, slice, col) 
                       join (SELECT tbl, 
                                    slice, 
                                    col, 
                                    Count(*) - Ceil(SUM(num_values) / 130994.0) 
                                    AS 
                                    excess_blks 
                             FROM   stv_blocklist 
                             WHERE  num_values > 0 
                                    AND num_values < 130994 
                             GROUP  BY 1, 
                                       2, 
                                       3) c USING (tbl, slice, col) 
                       join stv_slices d USING (slice) 
                       join (SELECT id, 
                                    Trim("name") AS tablename, 
                                    db_id 
                             FROM   stv_tbl_perm 
                             WHERE  slice = 0) f 
                         ON b.tbl = f.id 
                       join pg_database g 
                         ON f.db_id = g.oid 
                WHERE  excess_blks > 1 
                GROUP  BY 1, 
                          2, 
                          3, 
                          4, 
                          5) 
        WHERE  tbl > 1 
               AND t_excess_blks > (SELECT CASE 
                                             WHEN SUM(capacity) > 200000 THEN 
                                             1024 
                                             ELSE 102.4 
                                           END 
                                    FROM   stv_partitions 
                                    WHERE  host = owner 
                                           AND host = 0 
                                    GROUP  BY host) 
        GROUP  BY 1, 
                  2, 
                  3 
        ORDER  BY 4 DESC);

-- Disk based queries
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 23, 
       'Performance', 
       'Disk based queries', 
       'https://thedataguy.in/rskit/diskquery', 
       Count(*) 
FROM   (SELECT query 
        FROM   svl_query_summary 
        WHERE  is_diskbased = 't' 
        GROUP  BY query); 

-- COPY not optimized 
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 24, 
       'Performance', 
       'COPY not optimized', 
       'https://thedataguy.in/rskit/copy', 
       Count(*) 
FROM   (SELECT Count(*) AS n_files 
        FROM   stl_s3client 
        WHERE  http_method = 'GET' 
               AND query > 0 
               AND transfer_time > 0 
        GROUP  BY query) 
WHERE  n_files % (SELECT Count(slice) 
                  FROM   stv_slices) != 0; 

-- High CPU queries (>80 Percent)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 25, 
       'Performance', 
       'High CPU queries', 
       'https://thedataguy.in/rskit/highcpu', 
       Count(*) 
FROM   (SELECT query, 
       Max(query_cpu_usage_percent) cpu 
FROM   svl_query_metrics 
WHERE  query_execution_time > 60 
       AND query_cpu_usage_percent IS NOT NULL 
       AND (service_class BETWEEN 6 AND 13 or service_class >100) 
       AND query_cpu_usage_percent > 80 
       AND query NOT IN(SELECT query 
                        FROM   stl_query 
                        WHERE  querytxt NOT LIKE 'Vacuum' 
                                OR querytxt NOT LIKE 'vacuum') 
GROUP  BY query ); 

-- Most frequent Alert (> 500 times)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 26, 
       'Performance', 
       'Most frequent Alert', 
       'https://thedataguy.in/rskit/alert', 
       Listagg(event, ' ,') 
FROM   (SELECT Trim(Split_part(event, ':', 1)) AS event, 
               Count(*) 
        FROM   stl_alert_event_log 
        GROUP  BY 1) 
WHERE  count > 500; 

-- Long running queries (> 30mins)
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 27, 
       'Performance', 
       'Long running queries', 
       'https://thedataguy.in/rskit/slowquery', 
       Count(*) 
FROM   svl_query_metrics 
WHERE  query_execution_time >= 1800; 

-- Max temp space used by queries
INSERT INTO rstk_metric_result 
            (checkid, 
             category, 
             finding, 
             url, 
             value) 
SELECT 28, 
       'Performance', 
       'Max temp space used by queries', 
       'https://thedataguy.in/rskit/diskquery', 
       Max(gigabytes) 
FROM   (SELECT SUM(( bytes ) / 1024 / 1024 / 1024) AS GigaBytes 
        FROM   stl_query q 
               join svl_query_summary qs 
                 ON qs.query = q.query 
        WHERE  qs.is_diskbased = 't' 
               AND q.starttime BETWEEN SYSDATE - 7 AND SYSDATE 
        GROUP  BY q.query); 
-- -------------------------------------
-- Adding the description and priority
-- -------------------------------------
-- Tables without sort keys
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 0 THEN 'There are ' 
                                                  || value 
                                                  || 
' tables are does not have sort keys. This will endup with scaning all the blocks' 
  ELSE 'Awesome, all of your tables having sort keys!!!' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 10 THEN 2 
  WHEN Cast(value AS INT) =0 then 3
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 1) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Sort key column compressed
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 0 THEN 'There are ' 
                                                  || value 
                                                  || 
' tables where sort key columns are compressed, this may lead to a slight performance degradation'
  ELSE 'Awesome, none of your sort key columns are compressed' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 10 THEN 2 
  WHEN Cast(value AS INT) =0 then 3
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 2) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Sort key skew > 4
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 0 THEN value 
                                                  || 
' tables sort keys are skewed which indicates that you have to uncompress the sort key column or these columns may not be the right candidate for the sort keys. '
  ELSE 'Your sort keys are does not have any skew.' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 10 THEN 2 
  WHEN Cast(value AS INT) =0 then 3
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 3) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables with high Skew
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 0 THEN value 
                                                  || 
' tables are having skew in termns of distribution. This will lead to put more load on few nodes and pile the soon on few nodes. Consider changing the dist key if required'
  ELSE 'Seems the table distribution looks good' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 10 THEN 2 
  WHEN Cast(value AS INT) = 0 then 3
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 4) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables without compression
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 0 THEN value 
                                                  || 
               ' tables are not compressed yet, this is not at all a good practice, please compress all the column except sort key' 
                 ELSE 'Good Job!!! All the tables are compressed' 
               END AS details, 
               CASE 
                 WHEN Cast(value AS INT) > 10 THEN 1 
                 WHEN Cast(value AS INT) BETWEEN 1 AND 10 THEN 2 
                 WHEN Cast(value AS INT) =0 then 3
               END AS priority 
        FROM   rstk_metric_result 
        WHERE  checkid = 5) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- WLM queue wait time > 1min
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 180 THEN ' Sometimes your cluster is spending more time on wait on the queue. The max wait time from last 7 days is ' 
                                                    || value 
                                                    || 
               ' seconds. You have to tune your WLM to reduce the wait time.' 
                 WHEN Cast(value AS INT) < 180 THEN 
'We found that the wait time is less than 180  second ('||value||') which is Good. But still consider to tune WLM if need.'
ELSE 'Unknown -  - Your system tables may not have enough data'
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 900 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 180 AND 900 THEN 2 
  WHEN Cast(value AS INT) < 180 then 3
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 6) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- WLM max connection hit
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) BETWEEN 100 AND 400 THEN 
                 ' This cluster hits ' 
                 || value 
                 || 
' as the high number of connections recently. RedShift support 500 as the max connection, so please tune your WLM setting to reduce the wait time and boost the queries.'
  WHEN Cast(value AS INT) < 100 THEN 
'Great!!! This cluster never reach 100 connections (only '||value||') at any point of time, But to speed up concurrent queries in a queue, you may tune the WLM settings.'
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 300 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 100 AND 300 THEN 2 
  WHEN Cast(value AS INT) < 100 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 7) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Number of WLM queue
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) = 1 THEN 
' You are using Default queue only, its a Red Flag. At least keep minimum 2 Queue to balance the workload'
  WHEN Cast(value AS INT)BETWEEN 2 AND 3 THEN 'OK! you have ' 
                                              || value 
                                              || 
' queues, not bad. Still if you see any waiting process on the queue then tune WLM' 
  WHEN Cast(value AS INT)BETWEEN 3 AND 7 THEN 'OK! you have ' 
                                              || value 
                                              || 
' queues, not bad. But make sure it should to eat much resources. For a generic workload 2 to 3 queues will work fine.'
  WHEN Cast(value AS INT) =0 then 'You are using Auto WLM, so this check is not applicable'
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT)= 1 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 3 AND 7 THEN 2 
  WHEN Cast(value AS INT) BETWEEN 2 AND 3 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 8) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Auto WLM enabled
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) = 0 THEN 
' You are using manual WLM, if you are aware about your workload and the manual WLM has more than 2 or 3 queues then may be it will fit. Or just give a try with Auto WLM and let RedShift decide to allocate the resource'
  WHEN Cast(value AS INT) > 0 THEN 
'You have Auto WLM enbled, Good!!! still you are not convinced then switch to Manual WLM and tune the queues properly.'
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) = 0 THEN 2 
  WHEN Cast(value AS INT) > 0 THEN 2 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 9) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Max concurrency for a slot
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 20 THEN ' You have allocated' 
                                                   || value 
                                                   || 
' to your queue, generally more concurrey reduce the capacity. We recommed to use the slots between 15 to 20'
  WHEN Cast(value AS INT) BETWEEN 10 AND 20 THEN 'You have ' 
                                                 || value 
                                                 || 
' concurrent slots, Good!!! ' 
  WHEN Cast(value AS INT) < 10 THEN 'You have ' 
                                    || value 
                                    || 
' which is very less, it may allocate more resource per slot, but many sessions will go to waiting state iin the queue. Optimal value is 15 to 20'
  WHEN value IS NULL then 'You are using Auto WLM, so this check is not applicable'
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 20 THEN 1 
  WHEN Cast(value AS INT)BETWEEN 10 AND 20 THEN 2 
  WHEN Cast(value AS INT) < 10 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 10) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- WLM commit queue wait 
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 120 THEN 
                 'We found recently the commit was in the waiting queue for ' 
                 || value 
                 || 
' seconds, many reasons for this, but an optimized WLM will make this process much better' 
  WHEN Cast(value AS INT) BETWEEN 60 AND 120 THEN 
  'We found recently the commit was in the waiting queue for ' 
  || value 
  || 
'  seconds, no flags in that. If you feel its a high numnber, then an optimized WLM will make this process much better '
  WHEN Cast(value AS INT) < 60 THEN 'Great!!! the commit wait was ' 
                                    || value 
                                    || ' seconds, this looks good' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 120 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 60 AND 120 THEN 2 
  WHEN Cast(value AS INT) < 60 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 11) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Ghost rows
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 100000 THEN 
' You have more than 100K Ghost rows totally, it is a Red flag, please run vacuum on these tables.'
  WHEN Cast(value AS INT) BETWEEN 1000 AND 100000 THEN 'You have ' 
                                                       || value 
                                                       || 
' Ghost rows(marked for delete), please run vacuum to remove them' 
  WHEN Cast(value AS INT) < 1000 THEN 
'Awesome!!! You have less than 1K Ghost rows(only '||value||'), but frequently running vacuum will help you to clean up the Ghost rows.'
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 100000 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1000 AND 100000 THEN 2 
  WHEN Cast(value AS INT) < 1000 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 12) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables never performed vacuum (based on STL_Vacuum)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN 
' This cluster has '||value||' tables never vacuumed(from STL_Vacuum result), It is a RedFlag. Frequently running vacuum will make this count 0 and improve the overall process'
  WHEN Cast(value AS INT)BETWEEN 1 AND 5 THEN 
' This cluster has '||value||' tables where vacuum never run(from STL_Vacuum result), Frequently running vacuum will make this count 0 and improve the overall process'
  WHEN Cast(value AS INT) = 0 THEN 'Good Job!!! all the tables are vacummed' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 13) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Table vacuum older than 5 days
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 10 THEN 
' More than 10 tables did not perform vacuum from past 5 days, we found '||value||' tables. If the does not have any Ghost rows, then ignore this else run the vacuum'
  WHEN Cast(value AS INT)BETWEEN 5 AND 10 THEN 
'You have less than 10 tables where vacuum did not perform from last 5 days, we found '||value||' tables, this is OK, but still frequently running vacuum will make this count 0 and improve the overall process'
  WHEN Cast(value AS INT) = 0 THEN 'Good Job!!! all the tables are vacummed' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 5 AND 10 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 14) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables with tombstone blocks
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN 
' There are '||value||' tables where Tombstone blocks are there. It will spill the disk quickly and it is a Red Flag. Frequently running vacuum will make this count 0 and improve the overall process'
  WHEN Cast(value AS INT)BETWEEN 1 AND 5 THEN 
' There are '||value||' tables where Tombstone blocks are there, Frequently running vacuum will make this count 0 and improve the overall process'
  WHEN Cast(value AS INT) = 0 THEN 'Good Job!!! No Tombstone blocks found' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 15) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables with missing stats
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN ' We found ' 
                                                  || value 
                                                  || 
' tables with missing statistics, It is a main performance bottleneck. Please run Analyze to update the statistics'
  WHEN Cast(value AS INT)BETWEEN 1 AND 5 THEN 
'We found '||value||' tables with missing statistics, it is a less numer. But please run Analyze to update the statistics'
  WHEN Cast(value AS INT) = 0 THEN 
  'Awesome!!! All the tables statistics are upto date' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 16) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables with stale stats (> 5 percent)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN ' We found ' 
                                                  || value 
                                                  || 
' tables with stale statistics (greater than 5 percent), It is a main performance bottleneck. Please run Analyze to update the statistics'
  WHEN Cast(value AS INT)BETWEEN 1 AND 5 THEN 
'You have '||value||'  tables with stale statistics (less than 5 percent), Please run Analyze to update the statistics'
  WHEN Cast(value AS INT) = 0 THEN 
  'Awesome!!! All the tables statistics are upto date' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 17) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Top sized tables
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 2 THEN 
' You have '||value||' tables where the total size of those tables are greter than 40% of the total cluster size. Please consider to use RedShift Spectrum is possible.'
  WHEN Cast(value AS INT)BETWEEN 1 AND 2 THEN 
' You have '||value||' tables where the total size of those tables are greter than 40% of the total cluster size. Please consider to use RedShift Spectrum is possible'
  WHEN Cast(value AS INT) = 0 THEN 
'Nice!!! All the tables are having less than 40% of the usgae from the total size' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 2 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 2 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 18) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Table with high number of alerts (>3 alerts)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN ' We found that there are ' 
                                                  || value 
                                                  || 
' tables that has more than 3 alerts. Please visit stl_alert_event_log table to find those alerts'
  WHEN Cast(value AS INT) BETWEEN 0 AND 5 THEN 
  'Nice!!! Your tables does not have much alerts.' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 19) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Non scaned Tables (based on STL Scan)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 0 THEN ' We found that there are ' 
                                                  || value 
                                                  || 
' tables never scanned(from STL_SCAN results), Please take a look at these tables, if not required then remove them or move to Specturm'
  WHEN Cast(value AS INT) = 0 THEN 'Nice!!! All the tables are active' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 20) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables without backup
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN ' There are ' 
                                                  || value 
                                                  || 
' tables that mentioned as NOBACKUP in DDL, these tables are never backup via snapshot. Please take a look at these tables, if required then remove NO BACKUP flag'
  WHEN Cast(value AS INT) = 0 THEN 'You have ' 
                                   || value 
                                   || 
' tables with NoBackup flag, This looks fine' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 0 THEN 1 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 21) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Tables with fragmentation
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN 'This cluster has ' 
                                                  || value 
                                                  || 
' tables with Fragmentation, Please run vacuum to defrag the tables' 
  WHEN Cast(value AS INT) = 0 THEN 
  'Great!!! We did not find any tables with fragmentation' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
FROM   rstk_metric_result 
WHERE  checkid = 22) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Disk based queries
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 500 THEN 'You have ' 
                                                    || value 
                                                    || 
' disk based queries, this can spill your disk too quickly and gives bad performance. WLM setting can also help you to fix this'
  WHEN Cast(value AS INT) BETWEEN 300 AND 500 THEN 'You have ' 
                                                   || value 
                                                   || 
' disk based queries, Its a less count, but Disk based queries can spill your disk too quickly and gives bad performance. WLM setting can also help you to fix this'
  WHEN Cast(value AS INT) between 1 and 300 THEN 'Not much disk based quries. You have '||value||' quries only' 
  WHEN Cast(value AS INT) = 0 then 'Awesome!!! No disk based quries'
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 500 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 300 AND 500 THEN 2 
  WHEN Cast(value AS INT) < 300 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 23) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- COPY not optimized 
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 5 THEN 'You did the COPY operation ' 
                                                  || value 
                                                  || 
' times without having the correct number for files, Generally  the number of files is a multiple of the number of slices in your cluster'
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 'You did the COPY operation ' 
                                               || value 
                                               || 
' times without having the correct number for files. Its a less number. Generally  the number of files is a multiple of the number of slices in your cluster'
  WHEN Cast(value AS INT) = 0 THEN 'Awesome!!! You files are properlly sized' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 5 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 1 AND 5 THEN 2 
  WHEN Cast(value AS INT) = 0 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 24) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- High CPU queries (>80 Percent)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 10 THEN 'We found that ' 
                                                   || value 
                                                   || 
' queries consumed more than 80% of the CPU. Please take a look at these queries and tune them.'
  WHEN Cast(value AS INT) BETWEEN 5 AND 10 THEN 'We found that ' 
                                                || value 
                                                || 
' queries consumed more than 80% of the CPU. This is OK.' 
  WHEN Cast(value AS INT) < 5 THEN 
  'Awesome!!! You do not have much High CPU consuming queries.' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 5 AND 10 THEN 2 
  WHEN Cast(value AS INT) < 5 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 25) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Most frequent Alert (> 500 times)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
from   ( 
              SELECT checkid, 
                     CASE 
                            WHEN cast(value AS INT) IS NOT NULL THEN 'We found that ' 
                                          || value 
                                          ||' events are occuring too many times (more then 500 times), Its better to take a look at the tables who has these alerts and tune'
                            ELSE 'You do not have much alerts on alert table' 
                     END AS details, 
                     ( 
                            SELECT count(event) 
                            FROM   ( 
                                            SELECT   trim(split_part(event, ':', 1)) AS event, 
                                                     count(*) 
                                            FROM     stl_alert_event_log 
                                            GROUP BY 1) 
                            WHERE  count > 500) AS count, 
                     CASE 
                            WHEN cast(count AS INT) > 10 THEN 1 
                            WHEN cast(count AS INT) BETWEEN 5 AND    10 THEN 2 
                            WHEN cast(count AS INT) < 5 THEN 3 
                     END AS priority 
              FROM   rstk_metric_result 
              WHERE  checkid = 26) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- 	Long running queries (> 30mins)
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 120 THEN 'You have ' 
                                                    || value 
                                                    || 
' queries that runs more than 120 mins, Its a Red Flag, please take a look and tune those queries.'
  WHEN Cast(value AS INT) BETWEEN 30 AND 120 THEN 'You have ' 
                                                  || value 
                                                  || 
' queries that runs from 1Hr to 2Hrs, lease take a look and tune those queries.' 
  WHEN Cast(value AS INT) < 30 THEN 
  'Awesome!!! All of your queries are completed within 30mins' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 300 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 300 AND 100 THEN 2 
  WHEN Cast(value AS INT) < 100 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 27) sq 
WHERE  rstk_metric_result.checkid = sq.checkid; 

-- Max temp space used by queries
UPDATE rstk_metric_result 
SET    details = sq.details, 
       priority = sq.priority 
FROM   (SELECT checkid, 
               CASE 
                 WHEN Cast(value AS INT) > 10 THEN 
                 'One of your queries consumed ' 
                 || value 
                 || 
' GB of disk space to store the intermediate results. If your cluster is already consumed 70% of the disk, then its a Red Flag. This may lead to spill the disk too quickly.'
  WHEN Cast(value AS INT) BETWEEN 5 AND 10 THEN 'One of your queries consumed ' 
                                                || value 
                                                || 
' GB of disk space to store the intermediate results. This may lead to spill the disk too quickly.'
  WHEN Cast(value AS INT) < 5 THEN 
'Your queries never consumed more than 5Gb to store intermediate results. This seems OK.' 
  ELSE 'Unknown -  - Your system tables may not have enough data' 
END AS details, 
CASE 
  WHEN Cast(value AS INT) > 10 THEN 1 
  WHEN Cast(value AS INT) BETWEEN 5 AND 10 THEN 2 
  WHEN Cast(value AS INT) < 5 THEN 3 
END AS priority 
 FROM   rstk_metric_result 
 WHERE  checkid = 28) sq 
WHERE  rstk_metric_result.checkid = sq.checkid;