/* * Check Job status * * p_history is how far into job_log's past the check will go. Don't go further back than the longest job's interval that is contained * in job_check_config to keep check efficient * Return code 1 means a successful job run * Return code 2 is for use with jobs that support a warning indicator. Not critical, but someone should look into it * Return code 3 is for use with a critical job failure */ CREATE FUNCTION check_job_status(p_history interval, OUT alert_code integer, OUT alert_text text) LANGUAGE plpgsql AS $$ DECLARE v_jobs RECORD; v_job_errors RECORD; v_count int = 1; v_trouble text[]; v_alert_code_3 text; v_version int; BEGIN SELECT current_setting('server_version_num')::int INTO v_version; alert_text := '('; alert_code := 1; -- Generic check for jobs without special monitoring. Should error on 3 failures FOR v_job_errors IN SELECT l.job_name FROM @extschema@.job_check_log l WHERE l.job_name NOT IN (SELECT c.job_name FROM @extschema@.job_check_config c WHERE l.job_name <> c.job_name) GROUP BY l.job_name HAVING count(*) > 2 LOOP v_trouble[v_count] := v_job_errors.job_name; v_count := v_count+1; END LOOP; IF array_upper(v_trouble,1) > 0 THEN alert_code = 3; alert_text := alert_text || 'Jobs w/ 3 consecutive errors: '||array_to_string(v_trouble,', ')||'; '; END IF; SELECT jt.alert_text INTO v_alert_code_3 FROM @extschema@.job_status_text jt WHERE jt.alert_code = 3; -- Jobs with special monitoring (threshold different than 3 errors; must run within a timeframe; etc) IF v_version >= 90200 THEN FOR v_jobs IN SELECT job_name, current_timestamp, current_timestamp - end_time AS last_run_time, CASE WHEN (SELECT count(*) FROM @extschema@.job_check_log WHERE job_name = job_check_config.job_name) > sensitivity THEN 'ERROR' WHEN end_time < (current_timestamp - error_threshold) THEN 'ERROR' WHEN end_time < (current_timestamp - warn_threshold) THEN 'WARNING' ELSE 'OK' END AS error_code, CASE WHEN status = v_alert_code_3 THEN 'CRITICAL' WHEN status is null THEN 'MISSING' WHEN (end_time < current_timestamp - error_threshold) OR (end_time < current_timestamp - warn_threshold) THEN CASE WHEN status = 'OK' THEN 'MISSING' ELSE status END ELSE status END AS job_status FROM @extschema@.job_check_config LEFT JOIN (SELECT job_name, max(start_time) AS start_time, max(end_time) AS end_time FROM @extschema@.job_log WHERE (end_time > now() - p_history OR end_time IS NULL) GROUP BY job_name ) last_job using (job_name) LEFT JOIN (SELECT job_name, start_time, coalesce(status, (SELECT CASE WHEN (SELECT count(*) FROM pg_locks WHERE not granted and pid = m.pid) > 0 THEN 'BLOCKED' ELSE NULL END), (SELECT CASE WHEN (SELECT count(*) FROM pg_stat_activity WHERE pid = m.pid) > 0 THEN 'RUNNING' ELSE NULL END), 'FOOBAR') AS status FROM @extschema@.job_log m WHERE start_time > now() - p_history ) lj_status using (job_name,start_time) WHERE active LOOP IF v_jobs.error_code = 'ERROR' THEN alert_code := 3; alert_text := alert_text || v_jobs.job_name || ': ' || coalesce(v_jobs.job_status,'null??'); END IF; IF v_jobs.error_code = 'WARNING' THEN IF alert_code <> 3 THEN alert_code := 2; END IF; alert_text := alert_text || v_jobs.job_name || ': ' || coalesce(v_jobs.job_status,'null??'); END IF; IF v_jobs.job_status = 'BLOCKED' THEN alert_text := alert_text || ' - Object lock is blocking job completion'; ELSIF v_jobs.job_status = 'MISSING' THEN IF v_jobs.last_run_time IS NULL THEN alert_text := alert_text || ' - Last run over ' || p_history || ' ago. Check job_log for more details'; ELSE alert_text := alert_text || ' - Last run at ' || current_timestamp - v_jobs.last_run_time; END IF; END IF; IF alert_code <> 1 AND v_jobs.job_status <> 'OK' THEN alert_text := alert_text || '; '; END IF; END LOOP; ELSE -- version less than 9.2 with old procpid column FOR v_jobs IN SELECT job_name, current_timestamp, current_timestamp - end_time AS last_run_time, CASE WHEN (SELECT count(*) FROM @extschema@.job_check_log WHERE job_name = job_check_config.job_name) > sensitivity THEN 'ERROR' WHEN end_time < (current_timestamp - error_threshold) THEN 'ERROR' WHEN end_time < (current_timestamp - warn_threshold) THEN 'WARNING' ELSE 'OK' END AS error_code, CASE WHEN status = v_alert_code_3 THEN 'CRITICAL' WHEN status is null THEN 'MISSING' WHEN (end_time < current_timestamp - error_threshold) OR (end_time < current_timestamp - warn_threshold) THEN CASE WHEN status = 'OK' THEN 'MISSING' ELSE status END ELSE status END AS job_status FROM @extschema@.job_check_config LEFT JOIN (SELECT job_name, max(start_time) AS start_time, max(end_time) AS end_time FROM @extschema@.job_log WHERE (end_time > now() - p_history OR end_time IS NULL) GROUP BY job_name ) last_job using (job_name) LEFT JOIN (SELECT job_name, start_time, coalesce(status, (SELECT CASE WHEN (SELECT count(*) FROM pg_locks WHERE not granted and pid = m.pid) > 0 THEN 'BLOCKED' ELSE NULL END), (SELECT CASE WHEN (SELECT count(*) FROM pg_stat_activity WHERE procpid = m.pid) > 0 THEN 'RUNNING' ELSE NULL END), 'FOOBAR') AS status FROM @extschema@.job_log m WHERE start_time > now() - p_history ) lj_status using (job_name,start_time) WHERE active LOOP IF v_jobs.error_code = 'ERROR' THEN alert_code := 3; alert_text := alert_text || v_jobs.job_name || ': ' || coalesce(v_jobs.job_status,'null??'); END IF; IF v_jobs.error_code = 'WARNING' THEN IF alert_code <> 3 THEN alert_code := 2; END IF; alert_text := alert_text || v_jobs.job_name || ': ' || coalesce(v_jobs.job_status,'null??'); END IF; IF v_jobs.job_status = 'BLOCKED' THEN alert_text := alert_text || ' - Object lock is blocking job completion'; ELSIF v_jobs.job_status = 'MISSING' THEN IF v_jobs.last_run_time IS NULL THEN alert_text := alert_text || ' - Last run over ' || p_history || ' ago. Check job_log for more details'; ELSE alert_text := alert_text || ' - Last run at ' || current_timestamp - v_jobs.last_run_time; END IF; END IF; IF alert_code <> 1 AND v_jobs.job_status <> 'OK' THEN alert_text := alert_text || '; '; END IF; END LOOP; END IF; -- end version check IF IF alert_text = '(' THEN alert_text := alert_text || 'All jobs run successfully'; END IF; alert_text := alert_text || ')'; END $$;