POiSON WORLD

You can run the Active Users Data Collection Test diagnostic script to get information about all active users currently logged into EBS. This diagnostic test (on “Application Object Library”) will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.

– To check running EBS forms sessions, go to System Administration > Oracle Application Manager > Dashboard > Site Map > Monitoring > Forms Sessions
It links the OS session ID with user ID who runs the Forms session.

- FND_USER table stores the details of all end users. Below query can give a good idea who is logged on:
SQL> select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

- Use below code to get active users' last login:
set heading on
set feedback off
set echo off
ttitle on
Column the_today noprint new_value the_date format a20
Select distinct to_char(sysdate,'MM/DD/YY HH:MIPM') the_today from dual;
ttitle skip 2 -
left 'Run on: ' the_date center  'EBS Active Users and their Active Roles'  skip 1 -
left 'Page: ' format 99999 sql.pno skip 1
set lines 145
set pages 30000
col user for a55 trunc
col RESPONSIBILITY_NAME for a40 trunc
col Last_Logon_date for a20
select substr(c.user_name||' - '||c.description, 1, 60) "USER", RESPONSIBILITY_NAME,
       c.start_date "ActiveDate", to_char(last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date
from FND_Responsibility_tl a,
     FND_USER_RESP_GROUPS b,
     fnd_user c
where a.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
  and b.user_id = c.user_id
  and (c.end_date is null or c.end_date > sysdate)
  and (b.end_date is null or b.end_date > sysdate)
--  and c.last_logon_date > sysdate - 180
order by 1, 2
spool EBS_Active_users_08_2017.txt
/
Spool off

- For terminated user, run below to find the last login. Seems the user never logged onto EBS if its last_logon_date is NULL.
SQL> select substr(c.user_name||' - '||c.description, 1, 60) "USER",
to_char(c.last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date, user_id, creation_date, password_date, start_date, end_date
from fnd_user c
where c.user_name like '%ABC%';

- POSSIBLE queries to get number of EBS users connections. (not sure how accurate they are)
1) SQL> select count(*), to_char(sysdate, 'DY MON DD HH24:MI:SS YYYY') format
               from v$session
              where module like '%FNDSCSGN';
2)  The number of users on the system in the past 1 hour:
     SQL> select count(distinct user_id) "users" from icx_sessions
               where  last_connect > sysdate - 1/24 and user_id != '-1';
     The number of users on the system in the past 1 day:
     SQL> select count(distinct user_id) "users" from icx_sessions
               where  last_connect > sysdate - 1 and user_id != '-1';