In August I received an email from Stefan, a past participant in my AE Database Knowledge Workshop. He thanked me because I had – without knowing it – helped him solve a problem.
There had been a big change in the company where Stefan worked. Because of that, many agents in the AE System had become useless all at once. To identify and delete them is a challenge, even for experienced users of the Automation Engine. However, Stefan had subscribed to my newsletter under this link and had received all my old blogs. Among them was an article on Unused Agents, including a useful script for the identification of agents that are no longer needed.
Below is a new version of that article plus an even quicker version of the script.
Where Agents are Located in the Database
Agents can be used in many different objects:
- Jobs – Table JBA, Field JBA_HostDst
- Filetransfer – Table JFA, Fields JFA_HostSrc and JFA_HostDst
- Events – Table OEA, Fields OEA_HostDst, OEA_OP1_Host and OEA_OP2_Host
- Queue Manager – Table OQA, Field OQA_HostDst
- Filelist-VARA Objects – Table OVD, Field OVD_Host
Agents can also be set through scripting, PromptSets or variable inheritance.
In a nutshell: To find all agent usages in the object definitions is not exactly easy.
Instead, I suggest another solution.
Script for the Identification of Unused Agents
Instead of searching through the object definitions for agents that have not been used in a long time, my script accesses another set of data: The statistics data.
To begin with, the script creates a short list of agents that have not been used in 30 days. This list covers all agents in Jobs, Filetransfers, Events and Queue Manager objects. The only list missing is VARA Objects.
My old script worked very well for Stefan. However, I have reworked it for even better and significantly faster functionality. The database query searches for all agents for which the most recent set of task statistics is at least 30 days old, or for which there are no task statistics.
--Agentname, Last modification date,
--and last time when a task (JOBS, JOBF or EVNT) was activated on this agent
select OH_Name, OH_ModDate, MAX(AH_TImestamp1) as LastActivation
from OH
--Filter in AH: Object types, and the Agent name as AH_HostSrc or AH_HostDst
left outer join AH on AH_OType in ('JOBS', 'JOBF', 'EVNT', 'JOBQ') and (OH_Name = AH_HostSrc or OH_Name = AH_HostDst)
where OH_DeleteFlag = 0
--Real Agents must have an entry in HACL
and exists (
select 1
from HACL
where HACL_OH_Idnr = OH_IDNR
)
group by OH_Name, OH_ModDate
--There either is no statistic entry for tasks on this agent, or the oldest one is more than 30 days old
having MAX(AH_Timestamp1) is NULL
--or MAX(AH_TImestamp1) < dateadd(DAY,-30,GETUTCDATE()) --T-SQL
or MAX(AH_TImestamp1) < systimestamp at time zone 'UTC' -30 --Oracle
order by LastActivation;
This query results in a shortlist. Before you actually delete the agents, you should manually verify that they actually are no longer needed.
And what about Filelist-VARAs?
The script works for Jobs, Filetransfers, Events and Queue Manager Objects. But it won't allow you to find out when the last usage of a filelist VARA has happened.
There is a quite simple solution to finding the last usage of a filelist VARA. For this, the setting LAST_USE has to be activated. An administrator can set this configuration at UC_SYSTEM_SETTINGS on the client 0000. You will then find the last usage of a VARA in the database (table OH, column OH_LastDate) and in the header tab of the object (here in the Automic Database).
When using this method, be aware that Last Used synchronizes with a delay of several minutes (!). Calls that have happened only a short time ago, might not yet be entered.
If the setting Last Used is not set, the task gets more difficult. You'll have to identify all objects using the VARA and check there latest statistics. That's a pretty tiresome process – and also very prone to errors.
More Treasures to Discover
More than 60 articles were accumulated in my old AutomicBlog between 2011 and 2016. I have edited several of those articles in the meantime; for example, the short series on SQL Querying (here is how you get to Part 1 and Part 2 of the series). I plan on editing a few additional articles one by one.
If you do not want to wait for the edited versions, or just want to rummage around the old articles, you can download them from the archive. You will find, for example, the following articles:
- UC4 and MS-Powershell (Teil 1 und 2)
- Quit/Cancel all activities in one UC4 client
- Statistics of UC4 jobs inside a certain folder
Perhaps you are in a situation like Stefan’s, and one of the articles saves you a lot of time and aggravation.