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.
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';
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;
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.
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
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.
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 jINNER JOIN OH g on g.OH_NAME = j.hostgname and g.OH_Client = j.ClientINNER 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;
Thanks for your efforts PhilippElmer , I really appreciate it!
Btw. this is exactly doing what it's supposed too.
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?
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/