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#'
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#'
This entry was made based on an Automic Support Ticket that I filed. I made the assertion that the Query provided by Philip Elmer did not work properly - not extracting all the rows. But, in subsequent testing it was proved that the query WAS WORKING PROPERLY, and the issue was due to something specific to our environment.