Query to extract All JOBS details within a time frame

[Ankur sharma]

Hi Community,

Can we please have a mysql query that can satisfy the following requirements. 
List of all JOBS in the past 15 mins with all status like
  1. active
  2. Ended Ok
  3. Waiting
  4. Failed
  5. Error Condition


For query output  include:

       1. JOBS Name
  1. Job Status
  2. Runtime

regards
Ankur  

[Vimal Nallajerla V.V.N]

Hi Ankur, 

Please try below query and let me know the result. 

SELECT 
    AH_Name AS 'JOBS Name',
    CASE 
        WHEN AH_Status = 1900 THEN 'Ended Ok'
        WHEN AH_Status BETWEEN 1800 AND 1899 THEN 'Failed / Error Condition'
        ELSE CAST(AH_Status AS CHAR)
    END AS 'Job Status',
    COALESCE(TIMESTAMPDIFF(SECOND, AH_TimeStamp2, AH_TimeStamp4), 0) AS 'Runtime (Seconds)'
FROM AH
WHERE AH_OType = 'JOBS' 
  -- AH_TimeStamp4 is the End Time. Automic stores DB timestamps in UTC.
  AND AH_TimeStamp4 >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 MINUTE)

UNION ALL

SELECT 
    EH_Name AS 'JOBS Name',
    CASE 
        WHEN EH_Status BETWEEN 1500 AND 1599 THEN 'Active'
        WHEN EH_Status BETWEEN 1600 AND 1799 THEN 'Waiting'
        ELSE CAST(EH_Status AS CHAR)
    END AS 'Job Status',
    -- Calculates runtime from start time to right now. Outputs 0 if waiting/hasn't started.
    COALESCE(TIMESTAMPDIFF(SECOND, EH_StartTime, UTC_TIMESTAMP()), 0) AS 'Runtime (Seconds)'
FROM EH
WHERE EH_OType = 'JOBS'
  -- Filters only for inflight statuses to prevent duplicate rows for jobs that have ended 
  -- but haven't been deactivated from the Activities window yet.
  AND EH_Status BETWEEN 1500 AND 1799;

Regards

Vimal