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:

Monitor database connections to PeopleSoft
Find when table was built and analyzed
Find portal objects hidden from navigation

 

Prashant
 

Click Here to Leave a Comment Below 0 comments