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.

PSACCESSLOG fields

• OPRID - The PeopleSoft 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 that 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.

1. 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.
2. If the user session crashed for some reason, the LOGOUTDTTM and LOGINDTTM columns will remain the same.

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 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;

Additional Reading

A Look at the PSPTLOGINAUDIT Security Table