/* ckmodules.sql
     check modules by middle tiers
*/
col umachine format a20 trunc head UserSrvr
col totact format 999999 head ACTIVE
col totinact format 999999 head INACTIVE
col totsess format 999999 head TOTAL
col earliest_logon format a12 head Earliest
col latest_logon format a12 head Latest
col module format a10 trunc
break on report
compute sum of totact on report
compute sum of totinact on report
compute sum of totsess on report
select module,
 replace(machine,'GEIPS-AMER\',null) umachine,
sum(decode(status,'ACTIVE',1,0)) totact,
sum(decode(status,'INACTIVE',1,0)) totinact,
 count(*) totsess,
min(to_char(logon_time,'mm/dd hh24:mi')) earliest_logon,
max(to_char(logon_time,'mm/dd hh24:mi')) latest_logon
from v$session
where type = 'USER'
and module is not null
group by module, machine
order by 5,1,2
/