Christian_Boeck_57

Agent SQL Query

Discussion created by Christian_Boeck_57 on Jun 9, 2017
We are undergoing a project to clean up our QA environment, and currently our focus is UC4 Agents. The environment has approximately 2,200 Agents and we are convinced that some, maybe many are obsolete. By obsolete we mean that they are no longer being used – no longer assigned to jobs, or assigned to jobs that have not run in say 35 days or more. This is not a simple thing in my mind because there are many ways that Agents can be assigned to a job, and the issue of Agent Groups complicate the matter more. 

I searched the Automic Community and found some helpful ideas. Query #1 below written by Phillip Elmer seems to be a good solution, but I do not believe the results are complete. The results listed quite a few Agent Groups, but only a couple of Agents, and we are convinced that there are more. 

Query #2 is helpful in checking what jobs are assigned to each Agent, but again it is not complete. 

CAN YOU PROVIDE A WAY (SQL QUERY OR OTHERWISE) BY WHICH WE CAN QUERY OUR ENVIRONMENT OF 2,200 SERVERS AND DEFINITELY DETERMINE WHICH ONES ARE NOT USED, OR HAVE NOT BEEN USED SAY IN THE LAST 35 DAYS? THANKS. 

QUERY #1: 
--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' -35 --Oracle 
order by LastActivation; 


QUERY #2: 
Select 
JBA_HOSTDST,OH_CLIENT,OH_OTYPE,OH_NAME,OH_CRDATE,OH_MODUSERIDNR,OH_MODDATE,OH_MODCNT,OH_LASTUSERIDNR,OH_LASTDATE 
from OH, JBA 
where OH_IDNR = JBA_OH_IDNR 
and OH_DeleteFlag = 0 
AND oh_otype IN ('JOBP', 'JOBS', 'EVNT', 'JOBF', 'SCRI') 
and JBA_HOSTDST = '&AGENTNAME#'

Outcomes