By Chris Malek | 07 August 2012
The PSACCESSLOG PeopleTools table Explained
The PSACCESSLOG PeopleTools table logs all login and logout activity of both online web (PIA) users. It also logs batch and application server logins.
Here is a look at the table structure.
|OPRID||The PepleSoft user who logged in|
|LOGIPADDRESS||The IP Address of the user. (see note below)|
|LOGINDTTM||The date and time that the user logged in. (see note below)|
|LOGOUTDTTM||The date and time tha the user logged out. (see note below)|
|PT_SIGNON_TYPE||A value of “1” means it was a web login. If the Value is 0 (zero) then it was a batch or other type of login.|
LOGIPADDRESS Field Notes
LOGIPADDRESS may or may not be the actual IP address of the person logging in. It really depends on your installation, your network and load balancer configuration. Additionally, the location of the person’s workstation all come into play.
LOGINDTTM and LOGOUTDTTM Field Notes
If LOGINDTTM is the same as LOGOUTDTTM then that means one of two things.
- The user is still active in the system. When a user logs into PeopleSoft a row is inserted into PSACCESSLOG and the LOGOUTDTTM and LOGINDTTM will remain the same until they logout.
- If the user session crashed for some reason, the LOGOUTDTTM and LOGINDTTM columns will remain the same.
PT_SIGNON_TYPE Field Notes
There is not document on PT_SIGNON_TYPE in PeopleBooks or Oracle support. However, I have found that
PT_SIGNON_TYPE = 0 has several meaning one being that they got into PeopleSoft using PeopleSoft to Psoft Authentication token and did not signin directly to this database. This may be because you have a portal and they logged into Peoplesoft in the portal and got to HR or CS via an authentication token.
SQL to Find Currently Logged in Users
So with the knowledge we now have about the PSACCESSLOG table, we can write a simple query to determine who is currently logged into the system. Here is an oracle query that will give you this count.
-- Current Logins from the last hours SELECT OPRID, COUNT(*) FROM PSACCESSLOG WHERE LOGINDTTM = LOGOUTDTTM and LOGINDTTM >= SYSDATE - 1/24 -- Only pull rows with a login in the last hour and PT_SIGNON_TYPE = 1 -- PIA logins only GROUP BY OPRID;
Of course, if anyone had a session that was lasting longer than an hour they would not be included in this query because of this criteria
LOGINDTTM >= SYSDATE - 1/24. For the purposes of this post that is OK because we are not trying to “launch any rockets.”
SQL to find User’s who abended
What if you wanted to see users who got kicked out or had some issue. We can find these by looking for historical rows where
LOGINDTTM = LOGOUTDTTM. Here is an Oracle query that will find users who had a session abend in the last 24 hours. Here we assume that no one will have a session active for more than two hours using this criteria
LOGINDTTM < SYSDATE - 1/24*2.
SELECT * FROM PSACCESSLOG WHERE LOGINDTTM = LOGOUTDTTM AND LOGINDTTM < SYSDATE - 1/24*2 -- Exclude logins from the last two hours AND LOGINDTTM >= SYSDATE - 1 -- Logged in the last day and PT_SIGNON_TYPE = 1; -- PIA Logins only
SQL To find Historical Abends
So now we can take the abend research a step further and determine the history of these types of abends to see if our count from the last 24 hours is close to what happens on average.
This Oracle query will show the abend counts from the last 20 days.
SELECT TO_CHAR(LOGINDTTM, 'YYYY-MM-DD'), COUNT(*) FROM PSACCESSLOG WHERE LOGINDTTM = LOGOUTDTTM AND LOGINDTTM < SYSDATE - 1/24*2 -- Exclude logins from the last two hours AND LOGINDTTM >= SYSDATE - 20 -- go back 20 days and PT_SIGNON_TYPE = 1 -- PIA Logins only GROUP BY TO_CHAR(LOGINDTTM, 'YYYY-MM-DD') ORDER BY 1 DESC;