Automic Workload Automation

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

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

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

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

Thanks for your efforts PhilippElmer , I really appreciate it!

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

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

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