Content Summary: This page provides a SQL query that determines batches of background jobs.
Query Overview
Below is a SQL query, which will help determine batches of background jobs.
Note: This query unions the job and archive table. Adjust the top interval appropriately for your needs.
Immuta >= 2022.1
This query is for all 2022.1 versions of Immuta and above.
Determine Long-Running Background Jobs
Audience: Data Users
Content Summary: This page shows users how to identify background jobs that take too long to run.
Query Overview
Below is a SQL query, which will help determine background jobs that take too long to run.
Note: This query unions the job and archive table. Adjust the top interval appropriately for your needs.
Instructions
Run the following SQL query to determine background jobs that take too long to run.
WITHvars AS (SELECTNOW() AS end_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_dt
NOW() - INTERVAL '7 days'AS start_dt),all_jobs_raw AS (SELECTname, state, startedon, createdon, completedon, dataFROM pgboss.job UNION SELECT name, state, startedon, createdon, completedon, data FROM pgboss.archive -- COMMENT OUT THIS TABLE IF LESS THAN 12 HOURS AND RUNNING SLOW
),all_jobs AS (SELECTCASEWHENname ILIKE 'nativeSqlDataSourceSync_Redshift%'THEN'Redshift Native Update'WHENname='nativeSqlAutomaticSubscriptionNotifications_Redshift'THEN'Immuta Upd Redshift DS'WHENname='nativeSqlAutomaticSubscriptionNotifications_Snowflake'THEN'Immuta Upd Snowflake DS'WHENname='updatePolicies'THEN'updatePolicies'WHENname ILIKE 'nativeSqlProfileRefresh_Redshift%'THEN'nativeSqlProfileRefresh_Redshift'WHENname ILIKE 'nativeSqlProfileRefresh_Snowflake%'THEN'nativeSqlProfileRefresh_Snowflake'ELSEname END AS name, state, startedon, completedon, date_trunc('hour', createdon) AS createdon_trunc -- Change the date_trunc value to 'hour' or 'day'
FROM all_jobs_raw INNER JOIN vars ON TRUEWHERE all_jobs_raw.createdon > vars.start_dt and all_jobs_raw.createdon < vars.end_dtANDnameNOTLIKE'\_\_%'ANDnameNOT ILIKE 'cleanUp%'ANDnameNOT ILIKE 'initiateScheduled%'ANDnameNOTIN ( 'bootstrapImmutaQueryEngine', 'checkForRollOver', 'checkInWithLicenseServer','clearMaxTTLTokens', 'columnEvolutionCheck', 'createPolicySearchRecords',
'dataSourceTest', 'entitlements_cache_job_root', 'entitlements_cache_job_worker','executeCustomerMetricsQueries', 'expirePolicyAdjustments',
'expirePolicyCertifications', 'loadIamGroups', 'nativeSqlMigration_Redshift','recomputeDslFromJsonPolicies', 'removeIamAuths', 'scheduleDbtUpdates',
'scheduleSchemaEvolutionChecks', 'schemaProjectsQueryableMigration', 'visibilitySchemaUpdate') -- ignore list--AND (name ILIKE '%snowflake%') -- adjust to filter down to specific jobs),batchesAS (SELECT DISTINCTname, createdon_trunc, COUNT(*) AS total_count,MIN(createdon_trunc) AS createdon, MAX(completedon) AS completedonFROM all_jobs GROUP BYname, createdon_trunc),created_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS created_countFROM all_jobs WHEREstate='created'GROUP BYname, createdon_trunc),retry_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS retry_countFROM all_jobs WHEREstate='retry'GROUP BYname, createdon_trunc),active_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS active_countFROM all_jobs WHEREstate='active'GROUP BYname, createdon_trunc),completed_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS completed_count,AVG(completedon - startedon) AS average_timeFROM all_jobs WHEREstate='completed'GROUP BYname, createdon_trunc),expired_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS expired_countFROM all_jobs WHEREstate='expired'GROUP BYname, createdon_trunc),cancelled_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS cancelled_countFROM all_jobs WHEREstate='cancelled'GROUP BYname, createdon_trunc),failed_jobs AS (SELECTname, createdon_trunc, COUNT(*) AS failed_countFROM all_jobs WHEREstate='failed'GROUP BYname, createdon_trunc)SELECTbatches.*, completed_jobs.average_time, batches.completedon - batches.createdon AS duration, created_jobs.created_count, retry_jobs.retry_count, active_jobs.active_count, completed_jobs.completed_count, expired_jobs.expired_count, cancelled_jobs.cancelled_count, failed_jobs.failed_countFROMbatchesLEFT OUTER JOIN created_jobs ON batches.name = created_jobs.name AND batches.createdon_trunc = created_jobs.createdon_trunc
LEFT OUTER JOIN retry_jobs ON batches.name = retry_jobs.name AND batches.createdon_trunc = retry_jobs.createdon_truncLEFT OUTER JOIN active_jobs ON batches.name = active_jobs.name AND batches.createdon_trunc = active_jobs.createdon_trunc
LEFT OUTER JOIN completed_jobs ON batches.name = completed_jobs.name AND batches.createdon_trunc = completed_jobs.createdon_trunc
LEFT OUTER JOIN expired_jobs ON batches.name = expired_jobs.name AND batches.createdon_trunc = expired_jobs.createdon_trunc
LEFT OUTER JOIN cancelled_jobs ON batches.name = cancelled_jobs.name AND batches.createdon_trunc = cancelled_jobs.createdon_trunc
LEFT OUTER JOIN failed_jobs ON batches.name = failed_jobs.name AND batches.createdon_trunc = failed_jobs.createdon_trunc
ORDER BYname, createdon_trunc DESC;
WITH--FORINTERVALUSAGE===>NOW() -INTERVAL'4 hours'ASstart_dt--FOR SPECIFIC DATETIME USAGE ===> TO_TIMESTAMP('2021-11-23 12:05:00', 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'EDT' AS start_dt
varsAS (SELECTNOW() AS end_dt,NOW() - INTERVAL '7 days' AS start_dt),all_jobs_rawAS (SELECTname,state,startedon,createdon,completedon,dataFROMpgboss.job UNION SELECT name, state, startedon, createdon, completedon, data FROM pgboss.archive -- COMMENT OUT THIS TABLE IF LESS THAN 12 HOURS AND RUNNING SLOW
),all_jobsAS (SELECTCASEWHENname='automaticSubscription'ANDdata->>'profileId'ISNULLTHEN'autoSub - ByDataSource'WHENname='automaticSubscription'ANDdata->>'profileId'ISNOTNULLTHEN'autoSub - ByUser'WHENnameILIKE'nativeSqlProfileRefresh_Snowflake%'THEN'nativeSqlProfileRefresh_Snowflake%'WHENnameILIKE'nativeSqlProfileRefresh_Redshift%'THEN'nativeSqlProfileRefresh_Redshift%'ELSEnameENDASname,state,startedon,completedonFROMall_jobs_rawINNERJOINvarsONTRUEWHEREall_jobs_raw.createdon>vars.start_dtandall_jobs_raw.createdon<vars.end_dt),batchesAS (SELECTDISTINCTname,COUNT(*) ASjob_count,AVG(completedon-startedon) ASaverage_timeFROMall_jobsGROUPBYname)SELECT*,job_count*average_timeAStotal_time,end_dt-start_dtASall_timeFROMbatchesJOINvarsONTRUEORDERBYtotal_timeDESC;
Long Running SQL Commands and Queries
Audience: Data Users
Content Summary: This page details how to terminate long-running or hanging SQL commands or queries.
Query Overview
Below is a SQL Query, which contains instructions to help terminate long-running SQL commands or queries.
Note: This query excludesSTART_REPLICATION SLOT %.
Instructions
Use the inner select to determine if you have hanging queries, and replace pg_cancel_backend with pg_terminate_backend if they do not actually cancel. You can also change the interval if needed.
SELECT pg_cancel_backend(pid)
FROM (
SELECT
pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '1 day'
AND query NOT LIKE 'START_REPLICATION SLOT %'
) long_pids;
Determine Background Jobs Estimated Completion
Audience: System Administrators
Content Summary: This page shows how to determine when Immuta background jobs will complete.
Query Overview
Below is a SQL query, which will help determine when some background jobs will be complete.
Note: This query unions the job and archive table. Adjust the top interval appropriately for your needs. In addition, collections of jobs aren’t typically differentiated. For example, if you hit LDAP three times, that is one collection of jobs, not three.
Caveats
Avg Time: The value for the average time will have a standard deviation applied to it.
Duration: How long it has been running for. If this reads towards 12 hours, then this value is longer than the reported value.
Until Complete: How long it will take to complete the remaining queue. This is assuming the job is the only active job to utilize all of the web pods.
When EDT: When the jobs will be completed. This is assuming the job is the only active job to utilize all of the web pods.
Instructions
Run the following SQL query to investigate Immuta's background jobs.
WITH--FILTER by an INTERVAL or a START timestampvars AS (SELECTNOW() AS now_dt, NOW() - INTERVAL '4 hours'ASstart, NOW()),--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()),
all_jobs_raw AS (SELECTname, state, startedon, createdon, completedon, dataFROM pgboss.job--UNION SELECT name, state, startedon, createdon, completedon, data FROM pgboss.archive),all_jobs AS (SELECTCASEWHENname='automaticSubscription'ANDdata->>'profileId'ISNULLTHEN'autoSub - ByDataSource'WHENname='automaticSubscription'ANDdata->>'profileId'IS NOT NULLTHEN'autoSub - ByUser'WHENname ILIKE 'nativeSqlProfileRefresh_Snowflake%'THEN'nativeSqlProfileRefresh_Snowflake%'WHENname ILIKE 'nativeSqlProfileRefresh_Redshift%'THEN'nativeSqlProfileRefresh_Redshift%'WHENname ILIKE 'nativeSqlDataSourceSync_Snowflake%'THEN'nativeSqlDataSourceSync_Snowflake%'WHENname ILIKE 'nativeSqlDataSourceSync_Redshift%'THEN'nativeSqlDataSourceSync_Redshift%'ELSEnameENDASname, state, startedon, completedonFROM all_jobs_raw INNER JOIN vars ON TRUEWHERE all_jobs_raw.createdon > vars.startANDnameNOTLIKE'\_\_%'ANDnameNOT ILIKE 'cleanUp%'ANDnameNOT ILIKE 'initiateScheduled%'ANDnameNOTIN ( 'bootstrapImmutaQueryEngine', 'checkForRollOver', 'checkInWithLicenseServer', 'clearMaxTTLTokens','columnEvolutionCheck', 'createPolicySearchRecords',
'dataSourceTest', 'entitlements_cache_job_root', 'entitlements_cache_job_worker','executeCustomerMetricsQueries', 'expirePolicyAdjustments',
'expirePolicyCertifications', 'loadIamGroups', 'nativeSqlMigration_Redshift', 'recomputeDslFromJsonPolicies','removeIamAuths', 'scheduleDbtUpdates',
'scheduleSchemaEvolutionChecks', 'schemaProjectsQueryableMigration', 'visibilitySchemaUpdate') -- ignore list--AND (name ILIKE '%redshift%' OR name ILIKE '%bulk%') -- adjust to filter down to specific jobs),distinct_jobs AS ( SELECT DISTINCTnameFROM all_jobs),completed_jobs AS (SELECTname,COUNT(*) AS total,AVG(completedon - startedon) AS average_time,MIN(completedon) AS min_completedon,MAX(completedon) AS max_completedonFROM all_jobsWHEREstate='completed'GROUP BYname),active_jobs AS ( SELECTname, count(*) AS total FROM all_jobs WHEREstate='active'GROUP BYname ),queued_jobs AS ( SELECTname, count(*) AS total FROM all_jobs WHEREstate='created'GROUP BYname ),expired_jobs AS ( SELECTname, count(*) AS total FROM all_jobs WHEREstate='expired'GROUP BYname ),failed_jobs AS ( SELECTname, count(*) AS total FROM all_jobs WHEREstate='failed'GROUP BYname )SELECT distinct_jobs.name as"Job Name",--vars.start AT TIME ZONE 'EDT' AS "Filter EDT", completed_jobs.min_completedon ATTIMEZONE'EDT'AS"Earliest Job EDT",--completed_jobs.max_completedon AT TIME ZONE 'EDT' AS "Latest Job EDT", TO_CHAR(completed_jobs.max_completedon - completed_jobs.min_completedon, 'HH24:MI:SS.MS' ) AS"Duration", TO_CHAR(completed_jobs.total, 'FM9,999,999,999') AS"# Completed", TO_CHAR(completed_jobs.average_time, 'MI:SS.MS') AS"Avg Time", TO_CHAR(active_jobs.total, 'FM9,999,999,999') AS"# Active", TO_CHAR(queued_jobs.total, 'FM9,999,999,999') AS"# Queued", TO_CHAR(expired_jobs.total, 'FM9,999,999,999') AS"# Expired", TO_CHAR(failed_jobs.total, 'FM9,999,999,999') AS"# Failed", ((queued_jobs.total / active_jobs.total) * completed_jobs.average_time) AS"Until Complete" --(vars.now_dt + ((queued_jobs.total / active_jobs.total) * completed_jobs.average_time)) AT TIME ZONE 'EDT' AS "When EDT"
FROM distinct_jobsINNER JOIN vars ON TRUELEFT OUTER JOIN completed_jobs ON distinct_jobs.name = completed_jobs.nameLEFT OUTER JOIN active_jobs ON distinct_jobs.name = active_jobs.nameLEFT OUTER JOIN queued_jobs ON distinct_jobs.name = queued_jobs.nameLEFT OUTER JOIN expired_jobs ON distinct_jobs.name = expired_jobs.nameLEFT OUTER JOIN failed_jobs ON distinct_jobs.name = failed_jobs.nameORDER BY queued_jobs.total DESCNULLSLAST;
Demo Video
For further instruction on how to monitor background jobs, refer to this video.
Determine Background Jobs Performance Over a Duration
Audience: System Administrators
Content Summary: This page contains support for administrators who are trying to determine the change in performance over a given duration for background jobs.
Query Overview
Below is a SQL query, which will help determine the change in performance over a given duration for background jobs.
Caveats
SELECT INTERVAL '1 hour' AS duration.
The duration of the interval can be altered, though durations of 12 hours are not advised.
/ : The average duration increased.
\ : The average duration decreased.
Instructions
Run the following SQL query to determine the change in performance over a given duration for background jobs.
WITHvars AS (SELECT INTERVAL '1 hour'AS duration, NOW() AS m0, 3/* VALIDATE THIS */AS webpods_count ),intervals AS (SELECT m0, m0 - (duration /10) *1AS m1, m0 - (duration /10) *2AS m2, m0 - (duration /10) *3AS m3, m0 - (duration /10) *4AS m4, m0 - (duration /10) *5AS m5, m0 - (duration /10) *6AS m6, m0 - (duration /10) *7AS m7, m0 - (duration /10) *8AS m8, m0 - (duration /10) *9AS m9, m0 - (duration /10) *10AS m10FROM vars),all_jobs_raw AS (SELECTname, state, startedon, createdon, completedon, dataFROM pgboss.job UNION SELECT name, state, startedon, createdon, completedon, data FROM pgboss.archive -- COMMENT OUT THIS TABLE IF LESS THAN 12 HOURS AND RUNNING SLOW
),all_jobs AS (SELECTCASEWHENname='automaticSubscription'ANDdata->>'profileId'ISNULLTHEN'autoSub - ByDataSource'WHENname='automaticSubscription'ANDdata->>'profileId'IS NOT NULLTHEN'autoSub - ByUser'WHENname ILIKE 'nativeSqlProfileRefresh_Snowflake%'THEN'nativeSqlProfileRefresh_Snowflake%'WHENname ILIKE 'nativeSqlProfileRefresh_Redshift%'THEN'nativeSqlProfileRefresh_Redshift%'WHENname ILIKE 'nativeSqlDataSourceSync_Snowflake%'THEN'nativeSqlDataSourceSync_Snowflake%'WHENname ILIKE 'nativeSqlDataSourceSync_Redshift%'THEN'nativeSqlDataSourceSync_Redshift%'ELSEnameENDASname, state, startedon, completedonFROM all_jobs_raw INNER JOIN intervals ON TRUEWHERE all_jobs_raw.createdon > intervals.m10ANDnameNOTLIKE'\_\_%'ANDnameNOT ILIKE 'cleanUp%'ANDnameNOT ILIKE 'initiateScheduled%'ANDnameNOTIN ( 'bootstrapImmutaQueryEngine', 'checkForRollOver', 'checkInWithLicenseServer', 'clearMaxTTLTokens','columnEvolutionCheck', 'createPolicySearchRecords',
'dataSourceTest', 'entitlements_cache_job_root', 'entitlements_cache_job_worker', 'executeCustomerMetricsQueries', 'expirePolicyAdjustments',
'expirePolicyCertifications', 'loadIamGroups', 'nativeSqlMigration_Redshift', 'recomputeDslFromJsonPolicies','removeIamAuths', 'scheduleDbtUpdates',
'scheduleSchemaEvolutionChecks', 'schemaProjectsQueryableMigration', 'visibilitySchemaUpdate') -- ignore list--AND (name ILIKE '%redshift%' OR name ILIKE '%bulk%') -- adjust to filter down to specific jobs),distinct_jobs AS ( SELECT DISTINCTnameFROM all_jobs),completed_jobs AS ( SELECTname, startedon, completedon FROM all_jobs WHEREstate='completed' ),completed_jobs_m1 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m1 AND intervals.m0 GROUP BY name ),
completed_jobs_m2 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m2 AND intervals.m1 GROUP BY name ),
completed_jobs_m3 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m3 AND intervals.m2 GROUP BY name ),
completed_jobs_m4 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m4 AND intervals.m3 GROUP BY name ),
completed_jobs_m5 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m5 AND intervals.m4 GROUP BY name ),
completed_jobs_m6 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m6 AND intervals.m5 GROUP BY name ),
completed_jobs_m7 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m7 AND intervals.m6 GROUP BY name ),
completed_jobs_m8 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m8 AND intervals.m7 GROUP BY name ),
completed_jobs_m9 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m9 AND intervals.m8 GROUP BY name ),
completed_jobs_m10 AS ( SELECT name, COUNT(*) AS total, AVG(completedon - startedon) AS average_time, AVG(EXTRACT(EPOCH FROM (completedon - startedon))) AS average_epoch_ms FROM completed_jobs JOIN intervals ON TRUE WHERE completedon BETWEEN intervals.m10 AND intervals.m9 GROUP BY name ),
queued_jobs AS ( SELECTname, count(*) AS total_todo FROM all_jobs WHEREstateIN ('active', 'created') GROUP BYname )SELECT distinct_jobs.name, vars.duration AS"Total Duration", vars.duration /10AS"Each Interval",CONCAT(' ', TO_CHAR(completed_jobs_m9.average_time, 'MI:SS.MS')) AS"Oldest", CONCAT(CASE WHEN completed_jobs_m10.average_time IS NULL OR completed_jobs_m9.average_time IS NULL THEN ' ' WHEN completed_jobs_m9.average_time > completed_jobs_m10.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m9.average_time, 'MI:SS.MS')) AS "_",
CONCAT(CASE WHEN completed_jobs_m9.average_time IS NULL OR completed_jobs_m8.average_time IS NULL THEN ' ' WHEN completed_jobs_m8.average_time > completed_jobs_m9.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m8.average_time, 'MI:SS.MS')) AS "__",
CONCAT(CASE WHEN completed_jobs_m8.average_time IS NULL OR completed_jobs_m7.average_time IS NULL THEN ' ' WHEN completed_jobs_m7.average_time > completed_jobs_m8.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m7.average_time, 'MI:SS.MS')) AS "___",
CONCAT(CASE WHEN completed_jobs_m7.average_time IS NULL OR completed_jobs_m6.average_time IS NULL THEN ' ' WHEN completed_jobs_m6.average_time > completed_jobs_m7.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m6.average_time, 'MI:SS.MS')) AS "____",
CONCAT(CASE WHEN completed_jobs_m6.average_time IS NULL OR completed_jobs_m5.average_time IS NULL THEN ' ' WHEN completed_jobs_m5.average_time > completed_jobs_m6.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m5.average_time, 'MI:SS.MS')) AS "----",
CONCAT(CASE WHEN completed_jobs_m5.average_time IS NULL OR completed_jobs_m4.average_time IS NULL THEN ' ' WHEN completed_jobs_m4.average_time > completed_jobs_m5.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m4.average_time, 'MI:SS.MS')) AS "---",
CONCAT(CASE WHEN completed_jobs_m4.average_time IS NULL OR completed_jobs_m3.average_time IS NULL THEN ' ' WHEN completed_jobs_m3.average_time > completed_jobs_m4.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m3.average_time, 'MI:SS.MS')) AS "--",
CONCAT(CASE WHEN completed_jobs_m3.average_time IS NULL OR completed_jobs_m2.average_time IS NULL THEN ' ' WHEN completed_jobs_m2.average_time > completed_jobs_m3.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m2.average_time, 'MI:SS.MS')) AS "-",
CONCAT(CASE WHEN completed_jobs_m2.average_time IS NULL OR completed_jobs_m1.average_time IS NULL THEN ' ' WHEN completed_jobs_m1.average_time > completed_jobs_m2.average_time THEN '/ ' ELSE '\ ' END, TO_CHAR(completed_jobs_m1.average_time, 'MI:SS.MS')) AS "Newest",
vars.webpods_count AS"WebPods", to_char(queued_jobs.total_todo, 'FM9,999,999,999') AS"Queued",NOW() + (queued_jobs.total_todo / vars.webpods_count) * completed_jobs_m1.average_time AS"Should Complete By"FROM distinct_jobsINNER JOIN vars ON TRUELEFT OUTER JOIN completed_jobs_m1 ON distinct_jobs.name = completed_jobs_m1.nameLEFT OUTER JOIN completed_jobs_m2 ON distinct_jobs.name = completed_jobs_m2.nameLEFT OUTER JOIN completed_jobs_m3 ON distinct_jobs.name = completed_jobs_m3.nameLEFT OUTER JOIN completed_jobs_m4 ON distinct_jobs.name = completed_jobs_m4.nameLEFT OUTER JOIN completed_jobs_m5 ON distinct_jobs.name = completed_jobs_m5.nameLEFT OUTER JOIN completed_jobs_m6 ON distinct_jobs.name = completed_jobs_m6.nameLEFT OUTER JOIN completed_jobs_m7 ON distinct_jobs.name = completed_jobs_m7.nameLEFT OUTER JOIN completed_jobs_m8 ON distinct_jobs.name = completed_jobs_m8.nameLEFT OUTER JOIN completed_jobs_m9 ON distinct_jobs.name = completed_jobs_m9.nameLEFT OUTER JOIN completed_jobs_m10 ON distinct_jobs.name = completed_jobs_m10.nameLEFT OUTER JOIN queued_jobs ON distinct_jobs.name = queued_jobs.name;