Automic Workload Automation

Expand all | Collapse all

SQL Query Pulling Execution User:  Auto Processing or Manual Execute

  • 1.  SQL Query Pulling Execution User:  Auto Processing or Manual Execute

    Posted 06-13-2018 01:57 PM

    We have a requirement to develop a SQL query that will select Objects executed over a certain time period, display their statuses, and display the Users who executed those Objects.

    The Query below works fine, except for one point.  The User field represents the User who manually executed the Object, but it also lists Objects that were automatically executed by the Schedule.  in the latter case, the User is the last person who saved a change to the Schedule Object.  So, this is not very helpful.

    How could the query be modified to differentiate between Users who manually executed the Object, as apposed to those Objects that were automatically executed by a Schedule? 

     

    Thanks,

    Gerry

    -------------------------------------------------------

    SELECT UNIQUE ah_name as "Object Name", ah_RetCode as "Return Code",

    CAST((FROM_TZ(CAST(ah_timestamp2 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "Start Date Time",

    CAST((FROM_TZ(CAST(ah_timestamp4 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "End Date Time",

    usr_FirstName as "First Name", usr_LastName as "Last Name"

    FROM uc4.ah, uc4.oh, uc4.usr

    WHERE USR_OH_Idnr = AH_USR_Idnr

              AND AH_OH_Idnr =  OH_Idnr

              AND ah_timestamp2 < SYSDATE -1

              AND ah_timestamp2 > SYSDATE -2

              AND ah_oh_idnr IN (SELECT oh_idnr

                        FROM uc4.oh

              WHERE oh_otype in ('JOBP','JOBS','EVNT','SCRI','JOBF'))

    ORDER BY ah_name ;

    -------------------------------------------------------



  • 2.  Re: SQL Query Pulling Execution User:  Auto Processing or Manual Execute

    Posted 07-22-2018 09:30 PM

    I would suggest you to try DB Reporting tool. It'll be easier for you. 

    Reference:

    https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/12.0/AWA%20Guides/help.htm#AE_AWA_Source/Administration/ucacha.htm

    Check some related posts in this community will also help you to understand about this tool.



  • 3.  Re: SQL Query Pulling Execution User:  Auto Processing or Manual Execute

    Posted 07-23-2018 11:38 AM

    Thanks, I'll try it!



  • 4.  Re: SQL Query Pulling Execution User:  Auto Processing or Manual Execute

    Posted 07-23-2018 11:54 AM

    I use the DB Reporting tool whenever I can.  SQL is always subject to breakage with the next product upgrade.

     

    Looking at your SQL problem however, the ah table has an attribute of ah_stype that tells you what type of object your object was initiated from.

     

    However this is a recursive problem.  As an example, your object could be initiated from a JOBP that could be initiated from a JOBP that could be initiated from a JOBP that could be initiated from a JSCH.  To analyze all of these relationships, you'd need to use the attribute AH_ParentAct which gives you the runid of the immediate parent object, and check for JSCH values in ah_stype recursively.   

     

    And being Monday morning, it makes my brain hurt to think about how one would code this!



  • 5.  Re: SQL Query Pulling Execution User:  Auto Processing or Manual Execute

    Posted 09-14-2018 03:11 PM

    Thanks for the advise.  Helpful.