Automic Workload Automation

 View Only
Expand all | Collapse all

How to extract daily execution and historical daily executions from AWI 12.2.0

  • 1.  How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 19, 2021 10:15 AM
    Hello everyone,

    We are in the process of taking a count of daily executions of objects in Automic which includes Jobs, Workflows, Events, etc. Can someone help on how to extract those information from Automic AWI. There may be jobs which runs multiple times in a day, we would like to extract the total number of objects executing in a day. 

    Automic version 12.2.0

    Please help. Thanks. 

    Regards,
    Gokul Krishnan


  • 2.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 19, 2021 10:53 AM
    If you want to build your own extractor, the database table that contains run history is "AH".  It joins to the object header table "OH" based upon column AH_OH_Idnr.  The OH table provides you with the object name and the object type.

    This reporting utility has the ability to write the selected run history into csv format;
    https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/12.3/Automic%20Automation%20Guides/help.htm#Utilities/admin_AEDBReportingTool_About.htm?TocPath=Reference%257CUtilities%257C_____6




    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 3.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 10:42 AM
    Hi Pete,
    I am not really sure how to do it. I was going through the link you provided, is the query mentioned can be configured on any objects on the Automic GUI?

    What type of job needs to be created in Automic to query the results?

    Please note, I don't have access to the Automic servers and Database so am looking for a way to extract these information directly from the Automic GUI for audit purpose. Putting the filters is a manual task as I cannot see executions beyond 500(Don't have the option to change the limit in settings). 

    Thanks, 
    Gokul


  • 4.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 11:18 AM
    If your only experience with the product is as a user of the AWI, then this is a very difficult task.  (You can increase the 500 restriction yourself under personal settings, but it won't allow more than 999.)

    The way I would approach this would be to run my own SQL query statement against the database.  This can also be done from a VARA.SEC_SQLI object within the AWI.  If you think you would be comfortable doing so, I could post some SQL for you to try using that method?

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 5.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 11:36 AM
    Here is a SQL to get things started.  SQL can also be used to compute summary counts.  But you should familiarize yourself with the results from this SQL first to get a feeling for what sort of data is available.
    select oh_client as Client
    , oh_otype as ObjectType
    , oh_name as ObjectName
    , ah_status as FinalStatus
    , ah_timestamp1 as ActivationTime
    , ah_timestamp2 as StartTime
    , ah_timestamp4 as EndTime
    from oh, ah
    where oh_idnr = ah_oh_idnr
    and oh_client = ah_client
    and ah_timestamp2 > '2021-02-22'


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 6.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 02:41 PM
    Hi Pete,

    Thanks for your quick response. 

    Hope you're referring to VARA.SEC_SQL from Automic AWI? Attaching screenshot. I am not sure what login should we select to get the results. Can we select default login?





  • 7.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 05:09 PM
    Nope, wrong type of object.  There is a VARA.SEC_SQLI that you need to use.  The trailing "I" is important.  It takes you into a similar looking object that automatically connects to the AE database. I just remembered however that your administrators may have decided to restrict access to the AE database, and therefore the SQLI objects may not be available to you personally. So you may be running into site-defined restrictions, and you may need to ask for more access.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 8.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 05:30 PM
    Hi Pete,

    I found the VARA.SEC_SQLI object. I just need to enter the query statements in the SQL server statement column and click preview to get the appropriate results? 



    Thanks,
    Gokul


  • 9.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 07:30 PM
    Yes, the preview button will return your data query results to you.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 10.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 22, 2021 07:35 PM
    Edited by Pete Wirfs Feb 22, 2021 07:35 PM
    don't panic when you see that the timestamps don't seem to make sense.  The timestamps that are returned are all stored in the database as greenwich time, and have to be adjusted to your timezone for them to make sense.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 11.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 23, 2021 04:36 AM
    Hi Pete,

    Appreciate your valuable helps.

    I got some results on the screen after clicking the preview button, please see the screenshot below. How do we export this data? I don't see an option to export it. Our primary goal is to calculate the average daily executions (JOBS, JOBP, JSCH, EVNT, CALL) and historical daily executions.

    We are also trying to pull the active executable objects count from the Automic. 


    Thanks,
    Gokul


  • 12.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 24, 2021 11:05 AM
    I have used the following sql query as @Pete Wirfs has suggested and we received the no of executions on a particular day. I am not sure how to export this data, I can copy paste it but as it's huge it doesn't allow all the lines to be copy pasted at once and it's more of a manual task. 

    Can you help me with how to extract the list of jobs & jobp that are active in prod? Not the jobs that are currently running/active.

    select oh_client as Client
    , oh_otype as ObjectType
    , oh_name as ObjectName
    , ah_status as FinalStatus
    , ah_timestamp1 as ActivationTime
    , ah_timestamp2 as StartTime
    , ah_timestamp4 as EndTime
    from oh, ah
    where oh_idnr = ah_oh_idnr
    and oh_client = ah_client
    and ah_timestamp2 >= '2021-02-14 00:00:00'
    and ah_timestamp4 <= '2021-02-14 23:59:59'
    and ah_otype in ('JOBS','JOBP','EVNT','CALL')​

    Thanks,
    Gokul


  • 13.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 24, 2021 11:24 AM
    To transfer a large SQL resultset from a VARA to a CSV, create a new SCRI object to run this code, and modify it to match your requirements;
    ! CAPTURE THE NAME OF THE UC4 HOST WE ARE RUNNING ON
    :set &HOST# = get_att(host)

    ! RUN THE DATABASE QUERY INTO A RESULT SET
    :SET &HND# = PREP_PROCESS_VAR(EXAMPLE.SQL.TO.CSV.QUERY)

    ! WRITE THE RESULT SET OUT TO A FLAT FILE
    :SET &RET# = WRITE_PROCESS(&HND#,"\\targetpath\example_sql_to_csv.csv",&host#,myloginobject,OVERWRITE)


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 14.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 24, 2021 11:31 AM
    The "AH" table only holds history of completed tasks.
    Change "AH" to "EH" to query currently active tasks.

    Well written SQL statements can also return summary counts instead of long data lists. But doing this effectively depends a lot upon your knowledge of the data and a clear picture of what you want to learn from it.  For instance do you want your counts to be summarized by task type?  By day? by week? by month? by user? by type of completion status?  By how long the task ran? By what agent it ran against? Frequently I find myself having to summarize data on several of these data points at the same time.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 15.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 24, 2021 12:36 PM
    Hi Pete,

    Our immediate requirement is to fetch the number of jobs and workflows that are active in Prod. We got a request to verify how many jobs and workflows that are scheduled to run in Prod, no matter if it's a daily job, weekly or monthly.


  • 16.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 24, 2021 01:50 PM
    If your shop uses JSCH schedule objects for scheduling tasks, then this SQL will produce a list of what is scheduled to run, and when.
    select oh_client as client
    , oh_name as SCHEDULE
    , jpp_object as OBJECT
    , JPP_ErlstStTime as STARTTIME
    , jppc_calekeyname as CALENDAR
    from oh
    inner join jpp
    on oh_idnr = jpp_oh_idnr
    left outer join jppc
    on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
    where oh_otype = 'JSCH' and oh_deleteflag = 0 and oh_client = ###
    order by 4,3,5;




    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 17.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 26, 2021 11:49 AM
    Thanks, Pete. 

    We have encountered a problem here while trying to list out what is scheduled to run using JSCH.

    1. We have many schedules which are not in use but still they are inside JSCH's. They are unchecked but not removed from JSCH's hence unwanted workflows are also getting listed. Is there a way to filter out only those are active in JSCH's? 

    2. Secondly, there are few JSCHs which are obsolete, I don't need those but I am seeing those JSCHs as well and it's associated workflows. 

    Thanks for the helps.

    Gokul.



  • 18.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Feb 26, 2021 12:13 PM
    If you add this to the SQL, it will only select those JSCH objects that are currently active;
    and oh_name in (select eh_name from eh where eh_otype = 'JSCH')


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 19.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Mar 01, 2021 11:28 AM
    It is also important to add this column to the result set.  It gives you more information about how the calendar rule is to be applied;
     , case
    when jpp_cctype = 1 then 'All match'
    when jpp_cctype = 2 then 'One match'
    when jpp_cctype = 3 then 'No match'
    else jpp_cctype
    end as CalendarMatchRule


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 20.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Mar 02, 2021 11:22 AM
    Hi Pete,

    These have been very helpful for us. I have got information's about the workflows now after using the script you provided. We are checking the jobs associated to those workflows. Is there anyway we can extract the jobs in the workflows as well using a query?


    SQL query used,

    select oh_client as client
    , oh_name as SCHEDULE
    , jpp_object as OBJECT
    , JPP_ErlstStTime as STARTTIME
    , jppc_calekeyname as CALENDAR
    , case
    when jpp_cctype = 1 then 'All match'
    when jpp_cctype = 2 then 'One match'
    when jpp_cctype = 3 then 'No match'
    else jpp_cctype
    end as CalendarMatchRule
    from oh
    inner join jpp
    on oh_idnr = jpp_oh_idnr
    left outer join jppc
    on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
    where oh_otype = 'JSCH' and oh_deleteflag = 0 and oh_client = 500
    and oh_name in (select eh_name from eh where eh_otype = 'JSCH')
    order by 4,3,5;


    Thanks much for the helps.

    Regards,
    Gokul


  • 21.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Apr 29, 2021 11:34 AM
    Hello Expert , 

    Can someone please help us to get total count of jobs configured for S4 SAP system. I have tried with below script but count is not correct.  

    select COUNT(OH_Idnr) FROM OH WHERE OH_Client = 100 AND OH_Otype = 'JOBS' AND OH_Name LIKE '%SAP_JOBS#S4%'

    And how many S4 jobs completed and failed in last 24 hrs.

    Note: I have tried to pull the job report (completed and failed) using Dashboard but that is not matching with SAP SM37 job count. 


    Thanks in advance. 

    Regards,
    Viral 



  • 22.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Apr 30, 2021 01:55 AM
    Hi,

    what about:

    select COUNT(OH_Idnr)
    FROM OH
    WHERE OH_Client = 100 AND OH_Otype = 'JOBS' AND OH_Name LIKE '%SAP_JOBS#S4%'
    GROUP BY OH_Idnr

    But your statement above should spit out the same result.

    Regards,
    Juergen

    ------------------------------
    Juergen Lechner
    Senior Consultant
    setis GmbH
    Germany
    ------------------------------



  • 23.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Apr 30, 2021 05:15 AM
    Hello Juergen ,

    Thanks for quick update. 

    I assume, why job count was not correct as job object might have archived and SQL query is fetching those records too. So I have created some new test jobs object and executed SQL query and got the correct count. :)  

    Could you please help me with second query, to extract the count of jobs starting with name (SAP_JOBS#S4*) completed/failed in last 24hrs. 

    Regards,
    Viral 





  • 24.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Apr 30, 2021 11:04 AM
    Edited by Pete Wirfs Apr 30, 2021 11:15 AM
    This SQLServer query should give you a good starting point.  The ah_status column is your indicator of final run status.
    select oh_name as Name
    , ah_alias as Alias
    , oh_otype as Type
    , convert(datetime, ah_timestamp1 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Acivation
    , convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Start_Time
    , convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as end_time
    , ah_runTime as Duration_Seconds
    , ah_client as client
    , ah_Hostdst as Host
    , ah_queue as Queue
    , ah_idnr as RunID
    , ah_status Status
    , (select Y.oh_name from uc4.dbo.oh Y where y.oh_idnr = b.ah_usr_idnr) as Uc4User
    from uc4.dbo.oh A
    , uc4.dbo.ah B
    where oh_idnr = ah_oh_idnr
    and oh_name like 'ObjectNameFilter'
    --and oh_otype in('JOBP', 'JOBS')
    and convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') > '2021-03-01 00:00:00.000'
    and convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') < '2021-03-31 23:59:59.000'
    order by 5


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 25.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted May 03, 2021 06:11 AM
    Thank you Pete.


  • 26.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 03, 2021 05:51 PM
    @Pete Wirfs I have learned quite some from this thread. I created a SQLI VARA object as you suggested and was able to get some data by running a query. Now I am trying to download the output to a CSV file.

    I created a SCRI object and ran ​the first line of code as you posted in previous message as below:


    However, I got an error message:

    Could you please advise where I did wrong?





  • 27.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 03, 2021 08:08 PM
    I'm sorry, my instructions were incorrect.  you have two alternatives to consider;

    (1) put this script inside of a JOBS object that runs on an agent/host (not a SCRI object)

    (2) Remove this statement, and hard-code the desired agent/host into the WRITE_PROCESS() function.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 28.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 04, 2021 06:29 PM
    @Pete Wirfs Thank you! I created a JOBS.SQL object and I got the first two steps done!

    ​For the 3rd step, where can I find the "myloginobject"? 
    ! WRITE THE RESULT SET OUT TO A FLAT FILE
    :SET &RET# = WRITE_PROCESS(&HND#,"\\targetpath\example_sql_to_csv.csv",&host#,myloginobject,OVERWRITE)



  • 29.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 04, 2021 06:47 PM
    I think you should be able to use the same login object that you are using on your JOBS.SQL "attributes" page.

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 30.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 04, 2021 06:50 PM
    The variable   &$LOGIN#   might do the trick...

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 31.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 04, 2021 06:54 PM

    @Pete Wirfs This is what I found under the Attribute tab of the JOBS.SQL object I created:

    ​I am using a testing environment. However, even in our PROD environment, the Login doesn't have any object in it either. 




  • 32.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 04, 2021 07:02 PM

    Some agents, particularly SQL agents, will pick up their login information from the ServiceManager which is responsible for starting/stopping that agent.  That seems to be the case here.

    Could you run this from a different kind of agent, one that has a LOGIN object?

    Since the script is doing all of the SQL work for you through a SQL variable, you don't really need to run this on a SQL agent.



    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 33.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 16, 2021 12:35 PM
    Hi @Pete Wirfs Can you help with a query to list all job executions for a particular agent within a time period? ​


  • 34.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 17, 2021 02:04 AM
    Here is the SQLServer query that I use for listing all executions that were running within a time period;

    select oh_name as Name
    , ah_alias as Alias
    , oh_otype as Type
    --, convert(datetime, ah_timestamp1 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Acivation
    , convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Start_Time
    , convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as end_time
    , ah_runTime as Duration_Seconds
    , ah_client as client
    , ah_Hostdst as Host
    , ah_queue as Queue
    , ah_idnr as RunID
    , ah_status Status
    , (select Y.oh_name from uc4.dbo.oh Y where y.oh_idnr = b.ah_usr_idnr) as Uc4User
    from uc4.dbo.oh A
    , uc4.dbo.ah B
    where oh_idnr = ah_oh_idnr
    --and oh_name like 'objectname%' -- and ah_status <> 1930 and ah_status <> 1933 --and ah_status <> 1900
    and not oh_otype in('JOBP', 'HOST', 'SYNC', 'JSCH', 'SERV', 'CLNT', 'QUEUE', 'USER')
    and convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') > '2021-05-01 11:00:00.000' --FROM
    and convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') < '2021-05-01 12:00:00.000' --TO
    order by 4

    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 35.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Posted Jun 04, 2021 07:27 PM
    @Pete Wirfs I checked our OEBS objects as well but the LOGIN​ was also empty. Maybe ours are all managed by the Service Manager. 



  • 36.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Broadcom Employee
    Posted Jun 09, 2021 07:02 PM
    The OEBS agent does not utilize a login object like most of the RA agents.


  • 37.  RE: How to extract daily execution and historical daily executions from AWI 12.2.0

    Broadcom Employee
    Posted Jun 09, 2021 07:05 PM
    Also the database agent can either utilize a login object or a connection object.