ODI代理信息

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;