Automic Workload Automation

Expand all | Collapse all

Clear Down the Activity Window using SQL on the Automic Database

  • 1.  Clear Down the Activity Window using SQL on the Automic Database

    Posted 03-29-2017 09:29 AM
    Currently there exist a large number of active objects in the Automic Automation Engine > Activity Window (4484). Please can you advise how to clear down the Activity Window using SQL to delete them from the Automic database?

    Would like to keep the auditing/statistics data, can this be  filtered by date? I.e., delete all rows in activity window where date  <= '2017-02-28'

    We do not recommend to update or remove data from the database outside of the software.
    However here are a few hints to prevent the Activity window from overflowing:

     

     

    1. Configure tasks to automatically deactivate :


    ru5k4v7ji8gf.pnghttps://us.v-cdn.net/5019921/uploads/editor/mr/ru5k4v7ji8gf.png" width="542">
    There are many ways of using this, the most common being to deactivate tasks when they end OK as can be seen above.

     

     

    2 - Use scripting

    You can use a SQLI variable to retrieve tasks that fit certain criteria, and then use DEACTIVATE_UC_OBJECT to remove them from the activities.

    Quick example:

     

    VARA.SQLI.GET_RUNIDS

    SELECT EH_AH_Idnr
    FROM EH
    WHERE EH_EndTime
    <= '2017-02-28'

    Note : this SQL statement was made on SQL Server, if you're using Oracle you'll need to convert the timestamp.

     


    SCRI.DEACTIVATE_TASKS

    :SET &HND# = PREP_PROCESS_VAR(VARA.SQLI.GET_RUNIDS)
    :PROCESS &HND#
    :  SET &RUNID# = GET_PROCESS_LINE(&HND#,1)
    :  SET &DEACT# = DEACTIVATE_UC_OBJECT(&RUNID#)
    :  SET &TASKNAME# = GET_STATISTIC_DETAIL(&RUNID#, NAME)
    :  PRINT Task &TASKNAME# with RunID &RUNID# has been deactivated
    :ENDPROCESS


    Result:
    fs8xhvagj73w.png

     

     

    3 - Create different activity windows with different filters

    The goal is to only display some tasks and make it easier to deactivate them.
    For instance show only activities of tasks activated before 2017-02-28, that ran on a Windows agent and ended with ANY_ABEND status:

     

    oak1id3iq5tp.png
    a64ct36slqgt.png
    l83hweyqk38g.png

     

    Best regards,
    Antoine



  • 2.  Clear Down the Activity Window using SQL on the Automic Database

    Posted 03-29-2017 02:01 PM
    Hi Antoine,

    I will test the suggestion.

    Thank you



  • 3.  Clear Down the Activity Window using SQL on the Automic Database

    Posted 09-18-2017 03:02 AM
    Hi Antoine_Sauteron

    I just stubled over the SQL to determine all Activities Window entries -

    Why do you query the AH table?  - was this by intention or a typo?
    As we know Activities Windows = EH (Execute Header)

    Furthermore there is no limitation on client, object status, object type etc...?

    I would suggest using this one (T-SQL)

    select eh_ah_idnr from eh
    where eh_client = 1
    and eh_OTYPE in ('JOBS','JOBF','JOBP')
    and eh_status in ('1800','1820','1850')
    and EH_Starttime <= '2017-08-31'


    cheers, Wolfgang


  • 4.  Clear Down the Activity Window using SQL on the Automic Database

    Posted 03-15-2018 05:29 AM
    Hi Wolfgang,

    Just saw your comment... Of course the query should be made on EH, I edited my post to change it.

    Best regards,
    Antoine


  • 5.  Re: Clear Down the Activity Window using SQL on the Automic Database

    Posted 09-27-2018 09:21 AM

    Hi,

    the table EH does not have a column EH_TIMESTAMP4...

    Best regards

    Tim



  • 6.  Re: Clear Down the Activity Window using SQL on the Automic Database

    Posted 09-27-2018 09:47 AM

    Right... It should be EH_EndTime. Thanks for bringing this up.

     

     

    It has been corrected. Thanks @Octavie for the help

     

    Best regards,

    Antoine