SQL to list Agents that are not in use ?

Discussion created by Christine_Chavez_6412 on Apr 6, 2017
Latest reply on Apr 7, 2017 by Christine_Chavez_6412

On UserInterface, you can find out if an agent is in use or not:
1) Go to System Overview > Agents
2) Right-click on an Agent, select "Search for Use"

If you want a "list" though, you can write a SQL for this (Automic does not have an out of the box SQL for this reporting).

You will need to go through each agent for this approach.
You can write an SQL to list Agents not used at all but this information is not stored in just one or two tables.

Each object (JOB, EVENT, HOSTGROUP, etc) has it's own attributes table.

You will also need to take into consideration:
1) if a host is assigned during runtime by pulling the agent name from a VARA object
2) if a host is set in a Pre/Process tab eg: SET=WIN_AGENT

Tables to consider:

* ACTEX / HOST - list of agents. ACTEX for 11 and up, HOST for version 10.

* JBA - JBA_HOstDst holds the name of agent for JOBS
* OEA - OEA_HostDst holds the name of the agent for EVENT
* JFA - JFA_HostSrc and JFA_HostDst holds the name of the agent for a FileTransfer, Source and Destination respectively

* OVW  - holds the values for VARA objects
* OT - OT_Content holds the values for the Pre/Process tab

For a complete list of tables:

If anybody already has a SQL on hand that will list the agents not in use, please feel free to share :)