SQL to find Out all the active users in the system

Posted on November 17, 2010 | 3 Comments

Execute the following SQL command to get all the active users with a particular role (ABCD)
select distinct SCUSER NT_ID, ABALPH UserName
from sys7334.F0092 a, sys7334.f98owsec b, devdta.F0101 c
where a.ULUSER = b.SCUSER
and a.ULUSER = c.ABALKY
and a.ULUGRP like ‘%ABCD%’
and b.SCEUSER = ’01’
and c.ABAT1 = ‘E’
and b.SCUSER not in
(‘JDEXYZ’, ‘JDE’, ‘AFPCRPMON’);

This entry was posted in Fun With SQL and tagged , , . Bookmark the permalink.

3 responses to “SQL to find Out all the active users in the system”

  1. Nandan | November 29, 2010 at 1:32 am | Reply

    that’s really nice Vikash, could you also help me to determine or get the list of active E1 user currently logged in. Also, is there any way that we can create a script and schedule that script to every hour and some how get the required data.
    thank you for your help.

    Thanks,
    Nandan.

  2. vikks | November 30, 2010 at 1:17 pm | Reply

    Hi Nandan,
    The only way that comes to mind for ERP8 is F9312 table with security history = 1 in the jde.ini. I would search for eventype= 01 based on time and specifically where there is no corresponding eventtype=02 for the same username with a time value greater than the value in the time for eventype=01.
    For TR 8.98 and up we have an option in the server manager to show all the users online at any given time. Go to the Management Dashboard and select the dropdown for Select View – “Search for User Resources” and search. I don’t think this information is fetched from any system table but is rather handled from the web server side.
    I will post the scipt for ERP8 on the blog shortly for your requirement. Thanks for asking and please visit again!

    Vikash

  3. Nandan Mishra | May 7, 2013 at 6:13 am | Reply

    Sahi hai guru.

Leave a comment