SQL query to identify workflows executed in the last 30 days

[Stefan Stoynov]

Hi everyone,

I'm looking for an SQL query that can provide a list of all Workflow objects in the Automic system that have been executed at least once during the last 30 days.

Ideally, the query would also return the following information for the most recent execution only (in case there were multiple executions within the last 30 days):

• Workflow name
• Start time
• End time
• Status (optional, if available)

The goal is to identify which workflows are actively being used in our environment.

Has anyone implemented something similar or has ready to use query for this?

Thank you in advance!

Best Regards,
Stefan Stoynov

[Rick Murray]

Would this help

select
ah_client AS "Client",
ah_name AS "Object Name",
ah_otype as 'Jobtype',
ah_status AS "Status",
ah_idnr AS "RunID",
Ah_HOSTDST as 'Agent',
AH_TimeStamp2 as "Start Time",
AH_TimeStamp4 as "End Time"

from ah WITH(NOLOCK)

where AH_TimeStamp2 >= '2025-12-01 00:00:00.0'
and AH_TimeStamp2 <= '2025-12-31 23:59:59.9'
and ah_otype in ('JOBS','JOBP')
and ah_client = '100'
order by 1,4,2 desc;

[KIRAN P]

Little more.. (PostgreSQL)

WITH latest_execution AS (
    SELECT
        ah.AH_CLIENT,
        ah.AH_NAME,
        ah.AH_OTYPE,
        ah.AH_IDNR,
        ah.AH_TIMESTAMP1,
        ah.AH_TIMESTAMP2,
        ah.AH_STATUS,
        ROW_NUMBER() OVER (
            PARTITION BY ah.AH_CLIENT, ah.AH_NAME
            ORDER BY ah.AH_TIMESTAMP1 DESC
        ) AS rn
    FROM AH ah
    WHERE ah.AH_OTYPE = 'JOBP'
      AND ah.AH_TIMESTAMP1 >= CURRENT_TIMESTAMP - INTERVAL '30 days'  ---only the last 30 days of history
)

SELECT
    AH_CLIENT,
    AH_NAME        AS workflow_name,
    AH_IDNR,
    AH_TIMESTAMP1  AS start_time,
    AH_TIMESTAMP2  AS end_time,
    AH_STATUS
FROM latest_execution
WHERE rn = 1
ORDER BY AH_CLIENT, AH_NAME;

Returns only the latest execution for each unique workflow.

Thanks,
/Kiran

[Eric Lontz]

Here is a slight variation on the one we use for monthly reports. We capture a custom attribute value and don't capture start time or status. Not very efficient.

SELECT
    ROW_NUMBER() OVER (ORDER BY oh.OH_Name) AS Row#,
    oh.OH_Name,
    COALESCE(CAST(ah.AH_TimeStamp2 AS VARCHAR), '> 30 Days') AS AH_TimeStamp2,
    ah.AH_Status,
    COALESCE(CAST(ah.AH_TimeStamp4 AS VARCHAR), '> 30 Days') AS Last_Run
FROM OH oh
JOIN OFS ofs
    ON ofs.OFS_OH_Idnr_O = oh.OH_Idnr
OUTER APPLY (
    SELECT TOP 1
        AH_TimeStamp2,
        AH_TimeStamp4,
        AH_Status
    FROM AH
    WHERE AH_OH_Idnr = oh.OH_Idnr
    ORDER BY AH_TimeStamp4 DESC
) ah
WHERE oh.OH_OType = 'JOBP'
  AND ofs.OFS_Link = 0
  AND oh.OH_Client IN (10)
  AND oh.OH_Name NOT LIKE 'PCK%'
  AND NOT EXISTS (
      SELECT 1
      FROM JPP jpp
      WHERE jpp.JPP_Object = oh.OH_Name
        AND jpp.JPP_OType <> '<XTRNL>'
        AND jpp.JPP_OH_Idnr NOT IN (
            SELECT OH_Idnr
            FROM OH
            WHERE OH_OType = 'JSCH'
        )
  )
ORDER BY oh.OH_Name;