Workload Automation1

Expand all | Collapse all

SQL Query to get the list of Job execution status per day

  • 1.  SQL Query to get the list of Job execution status per day

    Posted 02-21-2019 10:25 AM

    Dear Experts,

    Actually I'm looking forward to find a SQL query where I can get the below list

     

    1. Number of jobs executed yesterday
    2. Number of jobs failed yesterday

     

    Can anyone help me with the SQL query from which I can get these details please ?

     

    Thanks and Regards
    Vimalan



  • 2.  Re: SQL Query to get the list of Job execution status per day

    Posted 02-28-2019 09:19 AM

    Take a look at the autoaggr command or the WCC reports.  CA does not recommend using direct database queries against the AutoSys db. 



  • 3.  Re: SQL Query to get the list of Job execution status per day

    Posted 02-28-2019 09:27 AM

    BTW my response was For AutoSys 



  • 4.  Re: SQL Query to get the list of Job execution status per day

    Posted 03-11-2019 02:42 AM

    Hi,

     

    I am responding for DSeries , there are pre-defined reports - FAILED_JOBS and JOBS_RUN_FOR_TIME_PERIOD

    in 'Services' perspective that can be used to handle this situation

     

     

    Please let me know if this does not solve the problem.

     

    Thanks and regards,

    Ravi Kiran



  • 5.  Re: SQL Query to get the list of Job execution status per day

    Posted 03-14-2019 09:08 AM

    What engine are you looking for information on.



  • 6.  Re: SQL Query to get the list of Job execution status per day

    Posted 04-23-2019 12:41 AM

    I also have the same kind of requirement where in i want to know the number of executions happened in a day or month for a specific job or jobs along with the avg run time.

     

    Btw... Autosys R11.3.6 SP7 is what i am working on.



  • 7.  Re: SQL Query to get the list of Job execution status per day

    Posted 04-23-2019 03:16 AM

    We tried this query and it works fine. May be you can give a try with it.

    This gives the overall status for each client based on the client numbers you have set in the query and for the previous day.

     

    select distinct (AH_Client),
    CASE
      WHEN AH_STATUS = '1800'  THEN 'ENDED_NOT_OK - Aborted'
      WHEN AH_STATUS = '1802'  THEN 'ENDED_JP_ABEND - Not executed because of abnormal workflow end.'
      WHEN AH_STATUS = '1810'  THEN 'ENDED_VANISHED - Disappeared'
      WHEN AH_STATUS = '1815'  THEN 'ENDED_LOST - Ended undefined (host terminated prematurely)'
      WHEN AH_STATUS = '1820'  THEN 'FAULT_OTHER - Start impossible. Other error.'
      WHEN AH_STATUS = '1822'  THEN 'FAULT_ALREADY_RUNNING - Task is already running'
      WHEN AH_STATUS = '1823'  THEN 'FAULT_POST_PROCESSING - Error in post processing'
      WHEN AH_STATUS = '1850'  THEN 'ENDED_CANCEL - Manually canceled.'
      WHEN AH_STATUS = '1851'  THEN 'ENDED_JP_CANCEL - Workflow canceled manually.'
      WHEN AH_STATUS = '1852'  THEN 'Rejected'
      WHEN AH_STATUS = '1856'  THEN 'ENDED_ESCALATED - Aborted due to escalation'
      WHEN AH_STATUS = '1900'  THEN 'ENDED_OK - Ended normally'
      WHEN AH_STATUS = '1904'  THEN 'ENDED_ROLLBACKED - Task was rolled back successfully'
      WHEN AH_STATUS = '1905'  THEN 'ENDED_WF_ROLLBACKED - The workflow was rolled back successfully.'
      WHEN AH_STATUS = '1910'  THEN 'ENDED_EMPTY - Task is empty (STOP NOMSG)'
      WHEN AH_STATUS = '1912'  THEN 'ENDED_EMPTY - Nothing found'
      WHEN AH_STATUS = '1913'  THEN 'ENDED_ROLLBACK_EMPTY - No rollback defined.'
      WHEN AH_STATUS = '1920'  THEN 'ENDED_INACTIVE - Inactive today because of the calendar.'
      WHEN AH_STATUS = '1922'  THEN 'ENDED_INACTIVE - Task has manually been set inactive.'
      WHEN AH_STATUS = '1930'  THEN 'ENDED_SKIPPED - Skipped because of WHEN clause.'
      WHEN AH_STATUS = '1933'  THEN 'ENDED_SKIPPED - Skipped because of conditions'
      WHEN AH_STATUS = '1940'  THEN 'ENDED_TIMEOUT - Not executed because of timeout (WHEN clause).'
      WHEN AH_STATUS = '1942'  THEN 'ENDED_TIMEOUT - Ended untimely.'
      WHEN AH_STATUS = '1944'  THEN 'ENDED_TIMEOUT - Period stopped'
    ELSE  'Others'
    END AS TEST , COUNT(AH_STATUS) as COUNT
    from AH
    where AH_CLIENT IN (<Client Number>) AND convert (DATE, AH_TimeStamp1) =  CONVERT(DATE, (SELECT DATEADD(dd, 0, DATEDIFF(dd, 1, GETDATE()))))  and AH_STATUS !='0'  group by AH_Client , AH_STATUS
    order by AH_Client asc

     

     

    Sample output as follows

     

    Client

    Object Type

    Count of Executions

    1001

    ENDED_NOT_OK - Aborted

    8

    1001

    ENDED_OK - Ended normally

    135715

    1002

    ENDED_NOT_OK - Aborted

    50

    1002

    ENDED_OK - Ended normally

    60413

    1003

    ENDED_OK - Ended normally

    39402

    1005

    ENDED_OK - Ended normally

    789



  • 8.  Re: SQL Query to get the list of Job execution status per day

    Posted 04-23-2019 08:07 AM

    This is for AE r11.3.6 and above.

    The Aggregator is a good source of job statistics.

    I am in full agreement with what Karen has often said in past such posts i.e. leave the database queries as an absolute last resort.

    Regards

    Chris  <CJ>