User's last login and number of current EBS connections

- To answer who is logged in EBS R12.1, Oracle DocID 269799.1 says:

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';