select
/*流程监控:ODI代理运行情况*/
[SCHOOL] SCHOOL,--学校/*[]*/
ZLK_LAGENT.LAGENT_NAME LAGENT_NAME,--逻辑代理
ZLK_AGENT.AGENT_NAME PAGENT_NAME,--物理代理
--'' OS,--运行系统
case when LOWER(ZLK_AGENT.HOST_NAME) = 'localhost' then (select SYS_CONTEXT('userenv','ip_address')from DUAL) else ZLK_AGENT.HOST_NAME end HOST,
ZLK_AGENT.HOST_PORT HOST_PORT,
COUNT(1) NUM_VALID_PROC,--有效调度挂载数
CASE WHEN sess.sess_end >= (sysdate-1) then '正常' else '异常' end STATU,--运行情况
sysdate TIMEFLAG
FROM ly_odi_zlk.snp_lagent ZLK_lagent--逻辑代理
LEFT JOIN ly_odi_zlk.snp_alloc_agent ZLK_alloc_agent ON ZLK_lagent.i_Lagent = ZLK_alloc_agent.i_Lagent--物理和逻辑代理id映射
left join LY_ODI_ZLK.SNP_AGENT ZLK_AGENT on ZLK_ALLOC_AGENT.I_AGENT = ZLK_AGENT.I_AGENT--物理代理和主机信息
left join (select agent_name,max(sess_end) sess_end from ly_odi_gzk.snp_session where sess_status = 'D' AND MASTER_AGENT_NAME IS NOT NULL AND SCEN_NAME IS NOT NULL group by agent_name) sess on upper(sess.agent_name) = upper(ZLK_AGENT.Agent_Name)--最新session时间判断代理是否正常
inner join (select * from LY_ODI_GZK.SNP_PLAN_AGENT where STAT_PLAN = 'E' AND SCEN_NAME||SCEN_VERSION IN (SELECT SCEN_NAME||SCEN_VERSION FROM SNP_SCEN)) AGEN on UPPER(ZLK_LAGENT.LAGENT_NAME) = UPPER(AGEN.LAGENT_NAME)--场景代理挂载情况
GROUP BY ZLK_lagent.Lagent_Name,ZLK_agent.Agent_Name,ZLK_agent.Host_Name,ZLK_agent.Host_Port,sess.sess_end
order by ZLK_AGENT.HOST_NAME,ZLK_AGENT.HOST_PORT;