Automic Workload Automation

 View Only
Expand all | Collapse all

AWI JOB STATUS Report

  • 1.  AWI JOB STATUS Report

    Posted Jul 24, 2018 12:45 PM

    Hi Everyone,

     

    We have a schedule which consist of more than 1000 jobs with workflows.
    I need to pull out a report which shows the current status of Jobs (ENDED_OK, ENDED_NOT_OK, WAITING for SYNC, Waiting for predecessor etc) with their respective Workflows.

    Is that possible to generate a report with these specifics.

    PLease help.

     

    Thanks

    Shani



  • 2.  Re: AWI JOB STATUS Report

    Posted Jul 26, 2018 02:45 AM

    Hi Shani,

     

    I think the best way to do this is to get the information directly from the database. Because of the numbers of jobs you would like to monitor I would guess this is the fastest way to do it. Use the EH table to collect the information and process the result in an Automic Script. In the EH you find the actual status and the connection between all the tasks (Parent - Child connection from the Schedule object down to the Jobs)

     

    regards

     

    Thomas



  • 3.  Re: AWI JOB STATUS Report

    Posted Aug 06, 2018 01:47 PM

    Keith - We have similar requirements and need to produce scheduled reports of jobs with differing completion status.  I'm looking for examples that can be imported and then customized.  Please provide working examples.



  • 4.  Re: AWI JOB STATUS Report

    Posted Aug 06, 2018 06:02 PM

    Here is a query we use to report "currently active workflows".  You should be able to modify it for your needs.

     

    select eh_client as Client
    , eh_Alias as WorkflowName
    , case eh_status
    when 1850 then 'Inactive'
    when 1560 then 'Blocked'
    when 1698 then 'WaitingForStartTime'
    when 1709 then 'WaitingForParallel'
    else
    isnull((select distinct 'Active '
    from eh b where a.eh_ah_idnr = b.EH_ParentAct
    and not b.eh_starttime is null and b.eh_endtime is null)
    ,case a.EH_External
    when 1 then 'WaitingForExternal'
    else 'Processing'
    end
    )
    end as WorkflowState
    --, eh_status, a.eh_ah_idnr as Runid
    --, *
    from eh a
    where eh_otype = 'JOBP'
    order by 3,2



  • 5.  RE: Re: AWI JOB STATUS Report

    Posted May 29, 2020 02:47 AM
    Hi Pete - Thanks for this info.

    We would like to pull the UC4 failed jobs like JOBS, JOBP ... etc

    But I'm confused with AH & EH tables. Which one we need to use. Could you pls elobarate.


  • 6.  Re: AWI JOB STATUS Report

    Broadcom Employee
    Posted Aug 07, 2018 01:27 AM

    Did you also have a look at the REST API for getting the required data? The 12.2 release has a REST endpoint that can retrieve all activities.



  • 7.  Re: AWI JOB STATUS Report



  • 8.  Re: AWI JOB STATUS Report

    Posted Aug 30, 2018 12:26 PM

    We are running 10 night reports.

    Anyone want the XML or documentation just send me an email.

    Anyone have additional reports, please contribute them to the success of everyone.

    Thanks to everyone

    Be Well, 

    Phil Frankford

     

    AH 24HR ABEND History JOBS.GROOVY.SQL.REPORT.AH_24HR_ABENDS.1 

    SELECT ah_alias as Job
    ,ah_idnr as runid
    ,ah_otype as type
    ,ah_title as title
    ,ah_hostdst as host
    ,ah_status as status
    ,ah_RetCode as RC
    ,m.msgtx_text as status_text
    ,ah_timestamp1 as activation_time
    ,ah_timestamp2 as start_time
    ,ah_timestamp4 as end_time
    FROM ah a, msgtx m
    WHERE ah_client = ?
    and (ah_hostdst in (?) or ? IS NULL)
    and (ah_status in (?) or ? IS NULL)
    and (ah_otype IN (?) or ? IS NULL)
    and m.msgtx_msgl_short = ?
    and CASE
    WHEN 'ACTIVATION' = ? THEN ah_timestamp1
    WHEN 'START' = ? THEN ah_timestamp2
    ELSE ah_timestamp4
    END between ? and ?
    and m.msgtx_msg_idnr = a.ah_status and ah_status between 1800 and 1899
    order by ah_otype, ah_status, ah_timestamp1


    AH 24HR Good History JOBS.GROOVY.SQL.REPORT.AH_24HR_GOOD.1 

    SELECT ah_alias as Job
    ,ah_idnr as runid
    ,ah_otype as type
    ,ah_title as title
    ,ah_hostdst as host
    ,ah_status as status
    ,ah_RetCode as RC
    ,m.msgtx_text as status_text
    ,ah_timestamp1 as activation_time
    ,ah_timestamp2 as start_time
    ,ah_timestamp4 as end_time
    FROM ah a, msgtx m
    WHERE ah_client = ?
    and (ah_hostdst in (?) or ? IS NULL)
    and (ah_status in (?) or ? IS NULL)
    and (ah_otype IN (?) or ? IS NULL)
    and m.msgtx_msgl_short = ?
    and CASE
    WHEN 'ACTIVATION' = ? THEN ah_timestamp1
    WHEN 'START' = ? THEN ah_timestamp2
    ELSE ah_timestamp4
    END between ? and ?
    and m.msgtx_msg_idnr = a.ah_status and ah_status between 1900 and 1920 and ah_otype <> 'REPORT' and ah_otype <> 'USER'
    order by ah_otype, ah_status, ah_timestamp1


    AH 24HR History Status JOBS.GROOVY.SQL.REPORT.AH_24HR_STATUS.1 

    SELECT ah_alias as Job
    ,ah_idnr as runid
    ,ah_otype as type
    ,ah_title as title
    ,ah_hostdst as host
    ,ah_status as status
    ,ah_RetCode as RC
    ,m.msgtx_text as status_text
    ,ah_timestamp1 as activation_time
    ,ah_timestamp2 as start_time
    ,ah_timestamp4 as end_time
    FROM ah a, msgtx m
    WHERE ah_client = ?
    and (ah_hostdst in (?) or ? IS NULL)
    and (ah_status in (?) or ? IS NULL)
    and (ah_otype IN (?) or ? IS NULL)
    and m.msgtx_msgl_short = ?
    and CASE
    WHEN 'ACTIVATION' = ? THEN ah_timestamp1
    WHEN 'START' = ? THEN ah_timestamp2
    ELSE ah_timestamp4
    END between ? and ?
    and m.msgtx_msg_idnr = a.ah_status
    order by ah_otype, ah_status, ah_timestamp1


    24 Hour Job/JCL Mod History JOBS.GROOVY.SQL.REPORT.24HR_JOBJCL_HISTORY.1

    with maxrc (oh_idnr, ov_vname, ov_value) as
    (
    select oh_idnr
    ,ov_vname
    ,ov_value
    from ov, oh
    where oh_idnr = ov_oh_idnr
    and oh_client = ?
    and oh_deleteflag = 0
    and ov_vname = '&MAXRC#'
    )
    ,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLFILE#'
    )
    ,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLLIB#'
    )
    ,hosts (oh_idnr, jba_hostdst, jba_logindst) as
    (
    select oh_idnr
    ,jba_hostdst
    ,jba_logindst
    from oh, jba
    where oh.oh_idnr = jba.jba_oh_idnr
    and oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    )
    select oh.oh_idnr as runit
    ,oh_name as jobname
    ,oh_archive1 as job
    ,jcl.opud_value as jcl
    ,jcllib.opud_value as jcllib
    ,oh_crdate as cre_date
    ,oh_moddate as mod_date
    ,maxrc.ov_value as maxrc
    ,oh_hostattrtypedst as host
    ,jba_hostdst as host_group
    ,jba_logindst as jba_login
    from oh
    LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
    LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
    LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
    LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
    where oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    and (oh_moddate >= GetDate() - 1)
    order by oh_name, oh_moddate


    48 Hour Job/JCL Mod History JOBS.GROOVY.SQL.REPORT.48HR_JOBJCL_HISTORY.1

    with maxrc (oh_idnr, ov_vname, ov_value) as
    (
    select oh_idnr
    ,ov_vname
    ,ov_value
    from ov, oh
    where oh_idnr = ov_oh_idnr
    and oh_client = ?
    and oh_deleteflag = 0
    and ov_vname = '&MAXRC#'
    )
    ,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLFILE#'
    )
    ,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLLIB#'
    )
    ,hosts (oh_idnr, jba_hostdst, jba_logindst) as
    (
    select oh_idnr
    ,jba_hostdst
    ,jba_logindst
    from oh, jba
    where oh.oh_idnr = jba.jba_oh_idnr
    and oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    )
    select oh.oh_idnr as runit
    ,oh_name as jobname
    ,oh_archive1 as job
    ,jcl.opud_value as jcl
    ,jcllib.opud_value as jcllib
    ,oh_crdate as cre_date
    ,oh_moddate as mod_date
    ,maxrc.ov_value as maxrc
    ,oh_hostattrtypedst as host
    ,jba_hostdst as host_group
    ,jba_logindst as jba_login
    from oh
    LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
    LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
    LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
    LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
    where oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    and (oh_moddate >= GetDate() - 4)
    order by oh_name, oh_moddate


    72 Hour Job/JCL Mod History JOBS.GROOVY.SQL.REPORT.72HR_JOBJCL_HISTORY.1

    with maxrc (oh_idnr, ov_vname, ov_value) as
    (
    select oh_idnr
    ,ov_vname
    ,ov_value
    from ov, oh
    where oh_idnr = ov_oh_idnr
    and oh_client = ?
    and oh_deleteflag = 0
    and ov_vname = '&MAXRC#'
    )
    ,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLFILE#'
    )
    ,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLLIB#'
    )
    ,hosts (oh_idnr, jba_hostdst, jba_logindst) as
    (
    select oh_idnr
    ,jba_hostdst
    ,jba_logindst
    from oh, jba
    where oh.oh_idnr = jba.jba_oh_idnr
    and oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    )
    select oh.oh_idnr as runit
    ,oh_name as jobname
    ,oh_archive1 as job
    ,jcl.opud_value as jcl
    ,jcllib.opud_value as jcllib
    ,oh_crdate as cre_date
    ,oh_moddate as mod_date
    ,maxrc.ov_value as maxrc
    ,oh_hostattrtypedst as host
    ,jba_hostdst as host_group
    ,jba_logindst as jba_login
    from oh
    LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
    LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
    LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
    LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
    where oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    and (oh_moddate >= GetDate() - 7)
    order by oh_name, oh_moddate


    Job/JCL History JOBS.GROOVY.SQL.REPORT.JOBJCL_HISTORY.1

    with maxrc (oh_idnr, ov_vname, ov_value) as
    (
    select oh_idnr
    ,ov_vname
    ,ov_value
    from ov, oh
    where oh_idnr = ov_oh_idnr
    and oh_client = ?
    and oh_deleteflag = 0
    and ov_vname = '&MAXRC#'
    )
    ,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLFILE#'
    )
    ,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
    (
    select oh_idnr
    ,opu_name
    ,opud_vname
    ,opud_value
    from oh, opu, opud
    where oh_deleteflag = 0
    and oh_client = ?
    and oh_idnr = opu_oh_idnr
    and opu_oh_idnr = opud_oh_idnr
    and opud_vname = 'JCLLIB#'
    )
    ,hosts (oh_idnr, jba_hostdst, jba_logindst) as
    (
    select oh_idnr
    ,jba_hostdst
    ,jba_logindst
    from oh, jba
    where oh.oh_idnr = jba.jba_oh_idnr
    and oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    )
    select oh.oh_idnr as runit
    ,oh_name as jobname
    ,oh_archive1 as job
    ,jcl.opud_value as jcl
    ,jcllib.opud_value as jcllib
    ,oh_crdate as cre_date
    ,oh_moddate as mod_date
    ,maxrc.ov_value as maxrc
    ,oh_hostattrtypedst as host
    ,jba_hostdst as host_group
    ,jba_logindst as jba_login
    from oh
    LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
    LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
    LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
    LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
    where oh_otype = 'JOBS'
    and oh_client = ?
    and oh_deleteflag = 0
    order by oh_name, oh_moddate


    Prior 24 HR Long Running Jobs JOBS.GROOVY.SQL.REPORT.24HR_LONG_RUNNING_JOBS.1 

    select ah_idnr as runid
    ,ah_otype as type
    ,ah_alias as job
    ,ah_title as title
    ,ah_hostdst as host
    ,ah_status as status
    ,m.msgtx_text as status_text
    ,ah_runtime as runtime
    ,ah_timestamp1 as activation_time
    ,ah_timestamp2 as start_time
    ,ah_timestamp4 as end_time
    FROM ah a, msgtx m
    WHERE ah_client = ?
    and (ah_status in (?) or ? IS NULL)
    and (ah_otype IN (?) or ? IS NULL)
    and ah_timestamp1 between ? and ?
    and m.msgtx_msg_idnr = a.ah_status
    and ah_runtime >= (? * 60 * 60) + (? * 60) + ?
    and m.msgtx_msgl_short = ?
    order by ah_alias, ah_timestamp2


    External Schedule Jobs JOBS.GROOVY.SQL.REPORTS.EXTERNALS 

    with tasks (workflow, object, alias) as
    (
    select oh_name, jpp_object, jpp_alias
    from oh, jpp
    where oh_client = ?
    and oh_deleteflag = 0
    and oh_otype = 'JOBP'
    and oh_idnr = jpp_oh_idnr
    and jpp_otype <> '<XTRNL>'
    )
    select oh_name
    ,jpp_object as ext_object
    ,jpp_parentobject as ext_parent
    ,jpp_alias as ext_alias
    ,jpp_extwhen
    ,tasks.*
    from oh, jpp
    LEFT OUTER JOIN tasks ON jpp_parentobject = workflow and jpp_object = object
    where oh_client = ?
    and oh_deleteflag = 0
    and oh_otype = 'JOBP'
    and oh_idnr = jpp_oh_idnr
    and jpp_otype = '<XTRNL>'
    order by oh_name, jpp_object

     

    Wait Job Status JOBS.GROOVY.SQL.REPORT.WAIT_JOB_STATUS.1 

    select a.eh_client as Client
    ,a.eh_Alias as WorkflowName
    ,a.eh_ah_idnr as Runid
    ,a.eh_status as RC
    ,case a.eh_status
    when 1850 then 'Inactive'
    when 1560 then 'Blocked'
    when 1698 then 'WaitingForStartTime'
    when 1709 then 'WaitingForParallel'
    else
    isnull(
    (select distinct 'Active '
    from eh b
    where b.eh_ah_idnr = b.EH_ParentAct
    and not b.eh_starttime is null and b.eh_endtime is null
    )
    ,case a.EH_External
    when 1 then 'WaitingForExternal'
    else 'Processing'
    end
    )
    end as WorkflowState
    from eh a
    where a.eh_otype = 'JOBP'
    order by a.eh_status, a.eh_Alias



  • 9.  Re: AWI JOB STATUS Report

    Posted Sep 07, 2018 10:33 AM

    Hi Phil,

     

    COuld you please share your email id, I would like to explore xmls you have.

     

    Thanks

    Shani



  • 10.  Re: AWI JOB STATUS Report

    Posted Sep 07, 2018 05:01 PM

    Be Well,

    Phil Frankford      | O 608-324-5200

    DBA and Engineer    | M 608-558-7320

    SC Data Center, Inc.,            | mail: Phillip.Frankford@sccompanies.com

    an affiliate of Colony Brands, Inc. 



  • 11.  RE: Re: AWI JOB STATUS Report

    Posted Mar 10, 2022 02:25 PM
    I have been asked by several community members to update this thread and share what we do now. So, I will upload some samples.  If you want to use the CSX_to_xlsx utility, you will need to copy it to a utility folder that Automic can access and rename csv_to_xlsx.txt back to csv_to_xlsx.exec.
    The following are some sample jobs with includes that we use.   

    Please let me know if you have any questions.
    Be Well,
    Phil

    ------------------------------
    [JobTitle]
    [CompanyName]
    [State]
    ------------------------------

    Attachment(s)

    xml
    jobi.call.proc.xml   687 B 1 version
    xml
    call_alert_fail.xml   2 KB 1 version
    xml
    JOBI.SQL.CSV2XLSX.xml   1 KB 1 version
    txt
    csv_to_xlsx.txt   2.11 MB 1 version
    xml
    DevJobs.xml   63 KB 1 version
    docx
    Automic_MS-SQL.docx   862 KB 1 version