Automic Workload Automation

Expand all | Collapse all

Get active agents of an agent group based on the job name

Jump to Best Answer
  • 1.  Get active agents of an agent group based on the job name

    Posted 09-15-2018 10:37 PM

    Hi AWA & SQL experts,

     

    I would need a SQL statement (Oracle) that shows the active agents of an agent group that is assigned to the current job.

    I.e.

     

    Preview -- one agent is active, the other one inactive

     

    We were able to get the active agent based on the OHG_OH_Idnr, but not the OH_Name. Thanks to chema14 for this statement.

     

    select oh_idnr from oh where oh_name='HOSTG.ACTIVE.OR.INACTIVE';

     

    Result:

     

     

    Now the current statement base don the OHG_OH_Idnr

     

    select OHGF_Name, OHG_OH_Idnr, OH_Idnr, HOST_Active from OHG
       INNER JOIN OHGF ON OHGF.OHGF_OH_Idnr = OHG_OH_Idnr
       INNER JOIN OH ON OH_Name = OHGF_Name
       INNER JOIN HOST ON OH_Idnr =  HOST_OH_Idnr
       where OHG_OH_Idnr = 1007014
       and Host_Active=1
       ORDER BY OHGF_Name;

     

    Result:

     

     

    Which is perfect, but it would be nice to have that result based on the OH_Name of the job it is part of since it's used in an internal variable.

     

    Maybe our SQL experts like PhilippElmer could help out with this statement.

     

    Any help is highly appreciated.

    Thanks in advance.

     

    Cheers,

    Alexander



  • 2.  Re: Get active agents of an agent group based on the job name

    Posted 09-17-2018 01:58 AM

    Hi,

    you can integrate the OH-query for the HOSTG's OH record into the OHG/OHGF query like so:

    select OHGF_Name, OHG_OH_Idnr, g.OH_Idnr as HOSTG_OH_IDNR,
    h.OH_Idnr as AGENT_OH_IDNR, HOST_Active   
    FROM OH g  
    INNER JOIN OHG on g.OH_IDNR = OHG_OH_IDNR  
    INNER JOIN OHGF ON OHGF_OH_Idnr = OHG_OH_Idnr  
    INNER JOIN OH h ON h.OH_Name = OHGF_Name  
    INNER JOIN HOST ON h.OH_Idnr =  HOST_OH_Idnr   
    where g.OH_NAME = 'HOSTG.ACTIVE.OR.INACTIVE'   
    and g.OH_Client = &$CLIENT#  
    and Host_Active=1  
    ORDER BY OHGF_Name;

    Please note that the query uses the predefined variable &$CLIENT# to specify the HOSTG's Client.

     

    IMPORTANT: What this query actually does is: it maps the HOSTG's entries to Agent names. So this will only work for HOSTG entries where the full Agent name is specified inside the HOSTG, see screenshot

     

     

    HTH, Philipp



  • 3.  Re: Get active agents of an agent group based on the job name

    Posted 09-17-2018 12:46 PM

    Hi PhilippElmer ,

     

    Thanks for your efforts. We're getting closer to what the customer needs.

    The challenge on this statement is, that the OH_Name should not be the HOSTG itself, but the object name (i.e. JOBS) that contains this HOSTG.

     

    Thanks in advance.

     

    Cheers,

    Alexander



  • 4.  Re: Get active agents of an agent group based on the job name
    Best Answer

    Posted 09-21-2018 11:12 AM

    Hi Alexander,

     

    you can find the "Agent" entry in JOBS in table JBA - but this is only for JOBS.
    Filetransfers are in JFA, Events in OEA.

     

    Here's an example that specifies a Jobname in the WHERE clause (probably still not what you really need, but maybe even closer than before ):

    with job2hostg (client, jobname, hostgname) as (
         select OH_Client, OH_Name, JBA_HostDst
         from OH
         inner join JBA on OH_IDNR = JBA_OH_IDNR
         where OH_NAME = 'JOBS.YOURJOBNAME'
         and OH_Client = &$CLIENT#
    )
    select OHGF_Name, OHG_OH_Idnr, g.OH_Idnr as HOSTG_OH_IDNR,
    h.OH_Idnr as AGENT_OH_IDNR, HOST_Active   
    FROM job2hostg j
    INNER JOIN OH g on g.OH_NAME = j.hostgname and g.OH_Client = j.Client
    INNER JOIN OHG on g.OH_IDNR = OHG_OH_IDNR  
    INNER JOIN OHGF ON OHGF_OH_Idnr = OHG_OH_Idnr  
    INNER JOIN OH h ON h.OH_Name = OHGF_Name  
    INNER JOIN HOST ON h.OH_Idnr =  HOST_OH_Idnr   
    where Host_Active=1  
    ORDER BY OHGF_Name;

     

    Cheers, Philipp



  • 5.  Re: Get active agents of an agent group based on the job name

    Posted 09-21-2018 02:31 PM

    Thanks for your efforts PhilippElmer , I really appreciate it!



  • 6.  Re: Get active agents of an agent group based on the job name

    Posted 09-21-2018 02:34 PM

    Btw. this is exactly doing what it's supposed too.



  • 7.  Re: Get active agents of an agent group based on the job name

    Posted 09-26-2018 09:13 AM

    Hi PhilippElmer,

     

    The SQL statement works perfect, but shows the agents of all clients. Could we also include the HACL_Client to only show the active agents of the current client?

     

    Thank you.

     

    Cheers,

    Alexander



  • 8.  Re: Get active agents of an agent group based on the job name

    Posted 09-27-2018 07:53 AM

    Hi Alex,

     

    sure, just add this to the WHERE clause

    and exists (
      select 1 from HACL 
      where HACL_OH_Idnr = h.OH_Idnr
      and HACL_Client = j.Client
      and HACL_Execute = 1
    )

     

    Want to be able to write some SQL without having to rely on my response-time and goodwill?

    Then check out https://philippelmer.com/services/classroom-training/automation-engine-database-knowledge-workshop/ 

     

    Cheers, Philipp