Query to find number of active users Node wise

 

SELECT icx.node_id,

       fnd.node_name,

       count(distinct icx.session_id) "ACTIVE_USER_SESSIONS_COUNT"

  FROM icx_sessions icx

      ,fnd_nodes fnd

 WHERE icx.disabled_flag != 'Y'

   AND icx.PSEUDO_FLAG    = 'N'

   AND icx.node_id        = fnd.node_id

   AND (icx.last_connect +

       decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),

               NULL,

               icx.limit_time,

               0,

               icx.limit_time,

               FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) >  SYSDATE

   AND counter             < icx.limit_connects

 GROUP by icx.node_id, fnd.node_name