SQL Tip : Find users logged into PeopleSoft PIA with this SQL
You can find the users logged into your PeopleSoft application in multiple ways as this activity is recorded at various location.
Whether you want to check who is logged into Application designer or monitor database connections to PeopleSoft, there is always a way to gather that info.
Here is a quick SQL that you can run to find out PeopleSoft users that are logged into PeopleSoft via PIA web login. This SQL will give you the Operator ID, Operator Description and the number of login count.
-- Find users who have logged into PeopleSoft PIA in last hour SELECT A.OPRID, B.OPRDEFNDESC, COUNT (*) FROM PSACCESSLOG A, PSOPRDEFN B WHERE A.LOGINDTTM = A.LOGOUTDTTM AND A.LOGINDTTM >= SYSDATE - 1 / 24 -- Only pull rows with a login in the last hour AND A.PT_SIGNON_TYPE = 1 -- PIA logins only AND A.OPRID = B.OPRID GROUP BY A.OPRID, B.OPRDEFNDESC ORDER BY COUNT (*) DESC;
Possible modifications to above SQL
Find the non-PIA logins
Change the PT_SIGNON_TYPE from ‘1’ to ‘0’
AND A.PT_SIGNON_TYPE = 1 -- non-PIA logins only
Find Logins for last few hours
Change the LOGINDTTM to show data for last few hours
AND A.LOGINDTTM >= SYSDATE - 2 / 24 -- Only pull rows with a login in the last 2 hour
Other useful SQLs: