Useful Queries for PeopleSoft IH


I have gathered and written a number of queries for the PeopleSoft Interaction Hub.  They help with aspects such as troubleshooting and getting additional information to help with system administration.

These queries require that you have at least read access to your IH Database via SQL Developer.  As I come across more useful queries, I'll keep this page up to date.

Usage: look for the parameter 'CUST', short for CUSTOM ~ this usually refers to a custom role in these series of queries, and can be changed to customize the query to your needs.




Get a list of a users Favorites: (peopletools uses: PSPRUFDEFN, however IH has much more comprehensive favorites (able to save favorites from different content providers).
=============================
select * from SYSADM.PS_EPPSC_MYSHRTCTS where OPRID like 'AAA';


Find out difference of roles between user AAA and user BBB:
=============================
SELECT ROLENAME FROM PSROLEUSER WHERE roleuser = 'AAA'
MINUS
SELECT ROLENAME FROM PSROLEUSER WHERE roleuser = 'BBB';


Get a count of a all users with two specific roles:
=============================
select count(*) from
(select Roleuser from sysadm.PSROLEUSER where rolename= 'CUST_AAA'
intersect
select Roleuser from sysadm.PSROLEUSER where rolename= 'CUST_BBB');


Find out users that have one role but not another.
===================================================
SELECT ROLEUSER FROM PSROLEUSER WHERE ROLENAME = 'CUST_AAA'
MINUS
SELECT ROLEUSER FROM PSROLEUSER WHERE ROLENAME = 'CUST_BBB';


Create DMS to insert records into PSROLEUSER based on Delta of 2 Roles
============================================
SELECT  DISTINCT 'INSERT INTO PSROLEUSER VALUES (' || '''' || ROLEUSER || ''''|| ',' ||''''|| 'CUST_CCC' ||''''||',' || ''''||'N'||''''||');' FROM PSROLEUSER
WHERE ROLEUSER IN (SELECT ROLEUSER FROM PSROLEUSER WHERE ROLENAME = 'CUST_DDD'
MINUS
SELECT ROLEUSER FROM PSROLEUSER WHERE ROLENAME = 'CUST_EEE');


Create DMS to delete a role based on a class definition
==============================================
select 'delete from psroleclass where rolename = '||''''||rolename||''''||' and classid = '||''''||classid||''''||';' from psroleclass a where not exists (select 'X' from psclassdefn b where a.classid = b.classid);


Find out how many users have accessed portal as signed in as CUST_ROLEX
====================================================================
select count(*) from sysadm.PSOPRDEFN a join sysadm.PSROLEUSER_VW ba on a.oprid = ba.oprid where a.lastsignondttm is not null and ba.rolename = 'CUST_ROLEX';


Failed Logins:
===============
select * from sysadm.PSOPRDEFN a join sysadm.PSROLEUSER_VW ba on a.oprid = ba.oprid where a.failedlogins > 0 and ba.rolename = 'CUST_ROLEX';

Structure and content view what has what:
==========================================
SELECT a.PORTAL_REFTYPE, a.portal_label, a.portal_cntprv_nam, a.portal_cref_usgt, a.portal_expire_dt, a.portal_ispublic, a.portal_linkobjname, a.portal_link_portal,  a.descr254,  a.portal_objname, a.portal_prntobjname, a.portal_seq_num, a.lastupddttm, a.portal_ispublic
FROM sysadm.PSPRSMDEFN a JOIN sysadm.PSPRSMPERM b ON  a.PORTAL_OBJNAME = b.portal_objname where b.portal_permname like 'CUST_ROLE%' and a.portal_name = 'EMPLOYEE' order by a.portal_objname;


Find a oprid based on emplid
============================
select OPRID from SYSADM.PSOPRDEFN where EMPLID = '0000000000';


List all custom roles in the system, specify how many References they have, and if the role is dynamic
=============================
select x.PORTAL_PERMNAME, x.counted, y.DYNAMIC_SW from
(SELECT b.portal_permname, count(b.portal_permname) as counted
FROM sysadm.PSPRSMDEFN a JOIN sysadm.PSPRSMPERM b ON  a.PORTAL_OBJNAME = b.portal_objname
where b.portal_permname like 'CUST%' and a.portal_name = 'EMPLOYEE' and b.PORTAL_PERMTYPE = 'R'
group by b.portal_permname
) x LEFT OUTER JOIN
(select  distinct ROLENAME, DYNAMIC_SW from sysadm.PSROLEUSER where DYNAMIC_SW = 'Y') y on
x.PORTAL_PERMNAME = y.ROLENAME
order by x.COUNTED desc;


Find out who is tagging information (peoplesoft IH news)
===================================
select a.EPPTG_PRIV_FLAG, a.OPRID, a.EPPTG_TAG_NAME, EPPTG_ADDTIME, b.EPPTG_ID from sysadm.PS_EPPTG_TAG_DATA a join sysadm.PS_EPPTG_TAG_KEYS b on a.EPPTG_UUID = b.epptg_uuid order by EPPTG_ADDTIME desc;


Summarize Role Counts by beginning with a CUST name
===================================
select rolename, count(rolename) as counted from sysadm.PSROLEUSER where rolename like 'CUST%' group by rolename order by Rolename;


Summarize IB URI Text (Between IH/EP/HR/LS/PF to see if there is an out of place URL)
===================================
select * from PSNODEURITEXT where
MSGNODENAME = 'EMPL' or
msgnodename = 'PSFT_PA' or
msgnodename = 'ERP' or
msgnodename = 'PSFT_EP' or
msgnodename = 'HRMS' or
msgnodename = 'PSFT_HR' or
msgnodename = 'HRMS_LS' or
msgnodename = 'PSFT_LS' or
msgnodename = 'MCM_PSFT_BO' or
msgnodename = 'EPM' or
msgnodename = 'PSFT_PF';