{"type":"video","version":"1.0","html":"<iframe src=\"https://www.loom.com/embed/f8f0d069df7b4dfa8f30e7615bfe29e1\" frameborder=\"0\" width=\"1768\" height=\"1326\" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe>","height":1326,"width":1768,"provider_name":"Loom","provider_url":"https://www.loom.com","thumbnail_height":1326,"thumbnail_width":1768,"thumbnail_url":"https://cdn.loom.com/sessions/thumbnails/f8f0d069df7b4dfa8f30e7615bfe29e1-00001.gif","duration":258,"title":"Monitor Background Jobs","description":"WITH\n--FILTER by an INTERVAL or a START timestamp\nvars AS (SELECT NOW() AS now_dt, NOW() - INTERVAL '4 hours' AS start, NOW()),\n--vars AS (SELECT NOW() AS now_dt, TO_TIMESTAMP('2021-11-23 12:05:00', 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'EDT' AS start, NOW()),\nall_jobs_raw AS (\n    SELECT name, state, startedon, createdon, completedon, data FROM pgboss.job\n    --UNION SELECT name, state, startedon, createdon, completedon, data FROM pgboss.archive\n),\nall_jobs AS (\n    SELECT\n        CASE\n            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NULL THEN 'autoSub - ByDataSource'\n            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NOT NULL THEN 'autoSub - ByUser'\n            WHEN name LIKE 'nativeSqlProfileRefresh_Redshift%' THEN 'nativeSqlProfileRefresh_Redshift%'\n            ELSE name\n        END AS name, state, startedon, completedon\n    FROM all_jobs_raw INNER JOIN vars ON TRUE\n    WHERE all_jobs_raw.createdon > vars.start\n    AND name NOT LIKE '\\_\\_%' AND name NOT ILIKE 'cleanUp%' AND name NOT ILIKE 'initiateScheduled%'\n    AND name NOT IN ('clearMaxTTLTokens', 'dataSourceTest', 'loadIamGroups', 'visibilitySchemaUpdate', 'checkInWithLicenseServer') -- ignore list\n    --AND (name ILIKE '%redshift%' OR name ILIKE '%bulk%') -- adjust to filter down to specific jobs\n),\ndistinct_jobs AS ( SELECT DISTINCT name FROM all_jobs),\ncompleted_jobs AS (\n    SELECT\n        name,\n        COUNT(*) AS total,\n        AVG(completedon - startedon) AS average_time,\n        MIN(completedon) AS min_completedon,\n        MAX(completedon) AS max_completedon\n    FROM all_jobs\n    WHERE state = 'completed'\n    GROUP BY name),\nactive_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'active' GROUP BY name ),\nqueued_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'created' GROUP BY name ),\nexpired_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'expired' GROUP BY name ),\nfailed_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'failed' GROUP BY name )\nSELECT\n    distinct_jobs.name as \"Job Name\",\n    --vars.start AT TIME ZONE 'EDT' AS \"Filter EDT\",\n    completed_jobs.min_completedon AT TIME ZONE 'EDT' AS \"Earliest Job EDT\",\n    --completed_jobs.max_completedon AT TIME ZONE 'EDT' AS \"Latest Job EDT\",\n    TO_CHAR(completed_jobs.max_completedon - completed_jobs.min_completedon, 'HH24:MI:SS.MS' ) AS \"Duration\",\n    TO_CHAR(completed_jobs.total, 'FM9,999,999,999') AS \"# Completed\",\n    TO_CHAR(completed_jobs.average_time, 'MI:SS.MS') AS \"Avg Time\",\n    TO_CHAR(active_jobs.total, 'FM9,999,999,999') AS \"# Active\",\n    TO_CHAR(queued_jobs.total, 'FM9,999,999,999') AS \"# Queued\",\n    TO_CHAR(expired_jobs.total, 'FM9,999,999,999') AS \"# Expired\",\n    TO_CHAR(failed_jobs.total, 'FM9,999,999,999') AS \"# Failed\",\n    ((queued_jobs.total / active_jobs.total) * completed_jobs.average_time) AS \"Until Complete\"\n    --(vars.now_dt + ((queued_jobs.total / active_jobs.total) * completed_jobs.average_time)) AT TIME ZONE 'EDT' AS \"When EDT\"\nFROM distinct_jobs\nINNER JOIN vars ON TRUE\nLEFT OUTER JOIN completed_jobs ON distinct_jobs.name = completed_jobs.name\nLEFT OUTER JOIN active_jobs ON distinct_jobs.name = active_jobs.name\nLEFT OUTER JOIN queued_jobs ON distinct_jobs.name = queued_jobs.name\nLEFT OUTER JOIN expired_jobs ON distinct_jobs.name = expired_jobs.name\nLEFT OUTER JOIN failed_jobs ON distinct_jobs.name = failed_jobs.name\nORDER BY queued_jobs.total DESC NULLS LAST;"}