Which EBS server did a user session log into

Oracle Doc ID 364439.1 (Tips and Queries for Troubleshooting Advanced Topologies) provides a few useful queries. One of them is to tell what server a user logged into.

A query to show and monitor all users who logged to the system within the last hour.

col server_name format a12

col user_name format a30

select

  to_char(first_connect, 'HH24:MI:SS') "TIME",

  user_name,

  decode

   (a.node_id,

   a.node_id,

   (select node_name  

   from fnd_nodes n  

   where a.node_id=n.node_id),a.node_id) server_name

from

  icx_sessions a,

  fnd_user b,

  fnd_nodes svr

where

  first_connect > (sysdate-1/24)

  and (a.user_id=b.user_id

       and a.node_id=svr.node_id)

  and disabled_flag='N'

order by first_connect;

TIME      USER_NAME         SERVER_NAME

----------- --------------------------- ------------

20:11:21 SYSADMIN          HOSTBE

20:17:53 USER                   HOSTINT

20:19:16 OPERATIONS     HOSTEMT

20:39:21 GUEST                HOSTPC8  (did not login, hit "forgot password")