1 of 9

Support

The Immuta Support section includes guides to troubleshoot issues with the Immuta application.

Section Contents

Background Jobs: These pages include SQL queries that allow you to examine Immuta's various background jobs.

Application Logs: This page details accessible logs of various Immuta components, necessary for administrators who need to troubleshoot installation issues with the Immuta application.

Fingerprint Service Timeout: This page details a tutorial about the Fingerprint Service in Immuta, which shows how to increase the timeout threshold for the service.

Background Jobs

Determine Background Job Batches

Audience: System Administrators
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.

WITH
vars AS (
    SELECT
    NOW() 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 (
    SELECT name, state, startedon, createdon, completedon, data FROM 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 (
    SELECT
        CASE
            WHEN name ILIKE 'nativeSqlDataSourceSync_Redshift%' THEN 'Redshift Native Update'
            WHEN name = 'nativeSqlAutomaticSubscriptionNotifications_Redshift' THEN 'Immuta Upd Redshift DS'
            WHEN name = 'nativeSqlAutomaticSubscriptionNotifications_Snowflake' THEN 'Immuta Upd Snowflake DS'
            WHEN name = 'updatePolicies' THEN 'updatePolicies'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Redshift%' THEN 'nativeSqlProfileRefresh_Redshift'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Snowflake%' THEN 'nativeSqlProfileRefresh_Snowflake'
            ELSE name
        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 TRUE
    WHERE all_jobs_raw.createdon > vars.start_dt and all_jobs_raw.createdon < vars.end_dt
    AND name NOT LIKE '\_\_%' AND name NOT ILIKE 'cleanUp%' AND name NOT ILIKE 'initiateScheduled%'
    AND name NOT IN (
        '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
),
batches AS (
    SELECT DISTINCT name, createdon_trunc, COUNT(*) AS total_count,
        MIN(createdon_trunc) AS createdon, MAX(completedon) AS completedon
    FROM all_jobs GROUP BY name, createdon_trunc),
created_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS created_count
    FROM all_jobs WHERE state = 'created' GROUP BY name, createdon_trunc),
retry_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS retry_count
    FROM all_jobs WHERE state = 'retry' GROUP BY name, createdon_trunc),
active_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS active_count
    FROM all_jobs WHERE state = 'active' GROUP BY name, createdon_trunc),
completed_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS completed_count,
        AVG(completedon - startedon) AS average_time
    FROM all_jobs WHERE state = 'completed' GROUP BY name, createdon_trunc),
expired_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS expired_count
    FROM all_jobs WHERE state = 'expired' GROUP BY name, createdon_trunc),
cancelled_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS cancelled_count
    FROM all_jobs WHERE state = 'cancelled' GROUP BY name, createdon_trunc),
failed_jobs AS (
    SELECT name, createdon_trunc, COUNT(*) AS failed_count
    FROM all_jobs WHERE state = 'failed'  GROUP BY name, createdon_trunc)
SELECT
    batches.*,
    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_count
FROM batches
LEFT 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_trunc
LEFT 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 BY name, createdon_trunc DESC;

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 excludes START_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 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.

WITH
vars AS (SELECT INTERVAL '1 hour' AS duration, NOW() AS m0, 3 /* VALIDATE THIS */ AS webpods_count ),
intervals AS (
    SELECT
        m0,
        m0 - (duration / 10) * 1 AS m1,
        m0 - (duration / 10) * 2 AS m2,
        m0 - (duration / 10) * 3 AS m3,
        m0 - (duration / 10) * 4 AS m4,
        m0 - (duration / 10) * 5 AS m5,
        m0 - (duration / 10) * 6 AS m6,
        m0 - (duration / 10) * 7 AS m7,
        m0 - (duration / 10) * 8 AS m8,
        m0 - (duration / 10) * 9 AS m9,
        m0 - (duration / 10) * 10 AS m10
    FROM vars
),
all_jobs_raw AS (
    SELECT name, state, startedon, createdon, completedon, data FROM 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 (
    SELECT
        CASE
            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NULL THEN 'autoSub - ByDataSource'
            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NOT NULL THEN 'autoSub - ByUser'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Snowflake%' THEN 'nativeSqlProfileRefresh_Snowflake%'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Redshift%' THEN 'nativeSqlProfileRefresh_Redshift%'
            WHEN name ILIKE 'nativeSqlDataSourceSync_Snowflake%' THEN 'nativeSqlDataSourceSync_Snowflake%'
            WHEN name ILIKE 'nativeSqlDataSourceSync_Redshift%' THEN 'nativeSqlDataSourceSync_Redshift%'
            ELSE name
        END AS name, state, startedon, completedon
    FROM all_jobs_raw INNER JOIN intervals ON TRUE
    WHERE all_jobs_raw.createdon > intervals.m10
    AND name NOT LIKE '\_\_%' AND name NOT ILIKE 'cleanUp%' AND name NOT ILIKE 'initiateScheduled%'
    AND name NOT IN (
        '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 DISTINCT name FROM all_jobs),
completed_jobs AS ( SELECT name, startedon, completedon FROM all_jobs WHERE state = '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 ( SELECT name, count(*) AS total_todo FROM all_jobs WHERE state IN ('active', 'created') GROUP BY name )
SELECT
    distinct_jobs.name,
    vars.duration AS "Total Duration",
    vars.duration / 10 AS "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_jobs
INNER JOIN vars ON TRUE
LEFT OUTER JOIN completed_jobs_m1 ON distinct_jobs.name = completed_jobs_m1.name
LEFT OUTER JOIN completed_jobs_m2 ON distinct_jobs.name = completed_jobs_m2.name
LEFT OUTER JOIN completed_jobs_m3 ON distinct_jobs.name = completed_jobs_m3.name
LEFT OUTER JOIN completed_jobs_m4 ON distinct_jobs.name = completed_jobs_m4.name
LEFT OUTER JOIN completed_jobs_m5 ON distinct_jobs.name = completed_jobs_m5.name
LEFT OUTER JOIN completed_jobs_m6 ON distinct_jobs.name = completed_jobs_m6.name
LEFT OUTER JOIN completed_jobs_m7 ON distinct_jobs.name = completed_jobs_m7.name
LEFT OUTER JOIN completed_jobs_m8 ON distinct_jobs.name = completed_jobs_m8.name
LEFT OUTER JOIN completed_jobs_m9 ON distinct_jobs.name = completed_jobs_m9.name
LEFT OUTER JOIN completed_jobs_m10 ON distinct_jobs.name = completed_jobs_m10.name
LEFT OUTER JOIN queued_jobs ON distinct_jobs.name = queued_jobs.name;

Example Output

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 timestamp
vars AS (SELECT NOW() AS now_dt, NOW() - INTERVAL '4 hours' AS start, 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 (
    SELECT name, state, startedon, createdon, completedon, data FROM pgboss.job
    --UNION SELECT name, state, startedon, createdon, completedon, data FROM pgboss.archive
),
all_jobs AS (
    SELECT
        CASE
            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NULL THEN 'autoSub - ByDataSource'
            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NOT NULL THEN 'autoSub - ByUser'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Snowflake%' THEN 'nativeSqlProfileRefresh_Snowflake%'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Redshift%' THEN 'nativeSqlProfileRefresh_Redshift%'
            WHEN name ILIKE 'nativeSqlDataSourceSync_Snowflake%' THEN 'nativeSqlDataSourceSync_Snowflake%'
            WHEN name ILIKE 'nativeSqlDataSourceSync_Redshift%' THEN 'nativeSqlDataSourceSync_Redshift%'
            ELSE name
        END AS name, state, startedon, completedon
    FROM all_jobs_raw INNER JOIN vars ON TRUE
    WHERE all_jobs_raw.createdon > vars.start
    AND name NOT LIKE '\_\_%' AND name NOT ILIKE 'cleanUp%' AND name NOT ILIKE 'initiateScheduled%'
    AND name NOT IN (
        '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 DISTINCT name FROM all_jobs),
completed_jobs AS (
    SELECT
        name,
        COUNT(*) AS total,
        AVG(completedon - startedon) AS average_time,
        MIN(completedon) AS min_completedon,
        MAX(completedon) AS max_completedon
    FROM all_jobs
    WHERE state = 'completed'
    GROUP BY name),
active_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'active' GROUP BY name ),
queued_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'created' GROUP BY name ),
expired_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'expired' GROUP BY name ),
failed_jobs AS ( SELECT name, count(*) AS total FROM all_jobs WHERE state = 'failed' GROUP BY name )
SELECT
    distinct_jobs.name as "Job Name",
    --vars.start AT TIME ZONE 'EDT' AS "Filter EDT",
    completed_jobs.min_completedon AT TIME ZONE '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_jobs
INNER JOIN vars ON TRUE
LEFT OUTER JOIN completed_jobs ON distinct_jobs.name = completed_jobs.name
LEFT OUTER JOIN active_jobs ON distinct_jobs.name = active_jobs.name
LEFT OUTER JOIN queued_jobs ON distinct_jobs.name = queued_jobs.name
LEFT OUTER JOIN expired_jobs ON distinct_jobs.name = expired_jobs.name
LEFT OUTER JOIN failed_jobs ON distinct_jobs.name = failed_jobs.name
ORDER BY queued_jobs.total DESC NULLS LAST;

Demo Video

For further instruction on how to monitor background jobs, refer to this video.

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.

Application Logs

Audience: System Administrators
Content Summary: This page outlines commands to retrieve Helm values, pods, and logs of various Immuta components (such as the Fingerprint Service or Immuta Web Service) to identify installation issues.

Kubernetes

Cache Logs

kubectl logs -l app.kubernetes.io/component=cache,app.kubernetes.io/name=immuta --timestamps --tail -1

Database Logs

kubectl logs -l app.kubernetes.io/component=database,app.kubernetes.io/name=immuta --timestamps --tail -1

Fingerprint Logs

kubectl logs -l app.kubernetes.io/component=nginx-ingress,app.kubernetes.io/name=immuta --timestamps --tail -1

Note: You can enable debug logging for the Fingerprint Service in the Helm Chart with fingerprint.logLevel: DEBUG.

Proxy Logs

kubectl logs -l app.kubernetes.io/component=nginx-ingress,app.kubernetes.io/name=immuta --timestamps --tail -1

Query Engine Logs

kubectl logs -l app.kubernetes.io/component=query-engine,app.kubernetes.io/name=immuta --timestamps --tail -1

Web (Service) Logs

kubectl logs -l app.kubernetes.io/component=service,app.kubernetes.io/name=immuta --timestamps --tail -1

Helm Values

helm get values immuta > immuta_values.yaml

Patroni

Find Master Pod (Database)

kubectl get pods -l app.kubernetes.io/component=database,app.kubernetes.io/name=immuta,k8s.immuta.com/database-role=master

Find Master Pod (Query Engine)

kubectl get pods -l app.kubernetes.io/component=query-engine,app.kubernetes.io/name=immuta,k8s.immuta.com/database-role=master

List Patroni Members

kubectl exec -it <POD> -- patronictl list

List Patroni Members for Database Pods

kubectl get pods -l app.kubernetes.io/component=database,app.kubernetes.io/name=immuta,k8s.immuta.com/database-role=master --no-headers | cut -d" " -f1 | xargs -I{} kubectl exec -it {} -- patronictl list

List Patroni Members for Query Engine Pods

kubectl get pods -l app.kubernetes.io/component=query-engine,app.kubernetes.io/name=immuta,k8s.immuta.com/database-role=master --no-headers | cut -d" " -f1 | xargs -I{} kubectl exec -it {} -- patronictl list

Note: You can change the value of the log level to DEBUG for Patroni in the Helm Chart for the Database and Query Engine pods by using the following values:

database.patroniLogLevel: DEBUG
database.patroniRequestsLogLevel: DEBUG
queryEngine.patroniLogLevel: DEBUG
queryEngine.patroniRequestsLogLevel: DEBUG

Fingerprint Service Timeout

Occasionally, the size of a table can cause the fingerprint service to repeatedly fail. Without a valid fingerprint, data sources show as Not Healthy in the UI, and you cannot apply advanced privacy enhancing techniques on those data sources.

To address this issue, you can increase timeout settings for the fingerprint service in Immuta.

As a user with the APPLICATION_ADMIN permission in Immuta, navigate to the App Settings Page.
Scroll to Advanced Configuration.
Enter the following configuration to increase the timeout threshold for the fingerprint service. Entries in the Advanced Configuration must adhere to YAML syntax. Read more here for YAML syntax details.
```
fingerprints:
  extraConfig:
    worker_timeout: 1200
```
Note: Timeout is set in seconds. The above example is a 20-minute timeout.
Click Save and confirm your changes.

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.

WITH
vars AS (SELECT INTERVAL '1 hour' AS duration, NOW() AS m0, 3 /* VALIDATE THIS */ AS webpods_count ),
intervals AS (
    SELECT
        m0,
        m0 - (duration / 10) * 1 AS m1,
        m0 - (duration / 10) * 2 AS m2,
        m0 - (duration / 10) * 3 AS m3,
        m0 - (duration / 10) * 4 AS m4,
        m0 - (duration / 10) * 5 AS m5,
        m0 - (duration / 10) * 6 AS m6,
        m0 - (duration / 10) * 7 AS m7,
        m0 - (duration / 10) * 8 AS m8,
        m0 - (duration / 10) * 9 AS m9,
        m0 - (duration / 10) * 10 AS m10
    FROM vars
),
all_jobs_raw AS (
    SELECT name, state, startedon, createdon, completedon, data FROM 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 (
    SELECT
        CASE
            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NULL THEN 'autoSub - ByDataSource'
            WHEN name = 'automaticSubscription' AND data->>'profileId' IS NOT NULL THEN 'autoSub - ByUser'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Snowflake%' THEN 'nativeSqlProfileRefresh_Snowflake%'
            WHEN name ILIKE 'nativeSqlProfileRefresh_Redshift%' THEN 'nativeSqlProfileRefresh_Redshift%'
            WHEN name ILIKE 'nativeSqlDataSourceSync_Snowflake%' THEN 'nativeSqlDataSourceSync_Snowflake%'
            WHEN name ILIKE 'nativeSqlDataSourceSync_Redshift%' THEN 'nativeSqlDataSourceSync_Redshift%'
            ELSE name
        END AS name, state, startedon, completedon
    FROM all_jobs_raw INNER JOIN intervals ON TRUE
    WHERE all_jobs_raw.createdon > intervals.m10
    AND name NOT LIKE '\_\_%' AND name NOT ILIKE 'cleanUp%' AND name NOT ILIKE 'initiateScheduled%'
    AND name NOT IN (
        '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 DISTINCT name FROM all_jobs),
completed_jobs AS ( SELECT name, startedon, completedon FROM all_jobs WHERE state = '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 ( SELECT name, count(*) AS total_todo FROM all_jobs WHERE state IN ('active', 'created') GROUP BY name )
SELECT
    distinct_jobs.name,
    vars.duration AS "Total Duration",
    vars.duration / 10 AS "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_jobs
INNER JOIN vars ON TRUE
LEFT OUTER JOIN completed_jobs_m1 ON distinct_jobs.name = completed_jobs_m1.name
LEFT OUTER JOIN completed_jobs_m2 ON distinct_jobs.name = completed_jobs_m2.name
LEFT OUTER JOIN completed_jobs_m3 ON distinct_jobs.name = completed_jobs_m3.name
LEFT OUTER JOIN completed_jobs_m4 ON distinct_jobs.name = completed_jobs_m4.name
LEFT OUTER JOIN completed_jobs_m5 ON distinct_jobs.name = completed_jobs_m5.name
LEFT OUTER JOIN completed_jobs_m6 ON distinct_jobs.name = completed_jobs_m6.name
LEFT OUTER JOIN completed_jobs_m7 ON distinct_jobs.name = completed_jobs_m7.name
LEFT OUTER JOIN completed_jobs_m8 ON distinct_jobs.name = completed_jobs_m8.name
LEFT OUTER JOIN completed_jobs_m9 ON distinct_jobs.name = completed_jobs_m9.name
LEFT OUTER JOIN completed_jobs_m10 ON distinct_jobs.name = completed_jobs_m10.name
LEFT OUTER JOIN queued_jobs ON distinct_jobs.name = queued_jobs.name;