Below are some queries that can be used to audit your Integration Broker configuration. These are useful for troubleshooting and monitoring.
It is a good idea to periodically audit what service operations are active in your production database.
-- Find All Active Service Operations and routings
SELECT O.IB_OPERATIONNAME,
RD.SENDERNODENAME, RD.RECEIVERNODENAME, O.DESCR
FROM PSOPERATION O, PSOPRVERDFN OV, PSIBRTNGDEFN RD, PSMSGNODEDEFN MN
WHERE O.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND OV.ACTIVE_FLAG = 'A'
AND RD.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND RD.VERSIONNAME = OV.VERSIONNAME
AND RD.EFF_STATUS = 'A'
AND RD.RECEIVERNODENAME = MN.MSGNODENAME
AND MN.ACTIVE_NODE = 1
AND MN.LOCALNODE = 1
ORDER BY O.IB_OPERATIONNAME, RD.SENDERNODENAME;Find Active Nodes that do NOT have any authentication type.
You may consider adding Node: WADL_NODE, WSDL_NODE to this but typically if you need an external integration partner to pull a WSDL or WADL you would want to secure it.
See the Nodes chapter chapter for more information.
SELECT A.MSGNODENAME, A.DESCR, A.ACTIVE_NODE, A.AUTHOPTN, A.LOCALNODE, A.NODE_TYPE, A.USERID
FROM PSMSGNODEDEFN A
WHERE A.MSGNODENAME NOT IN ('ANONYMOUS')
-- No Authentication
AND A.AUTHOPTN = 'N'
-- Active Nodes Only
AND A.ACTIVE_NODE = '1'
ORDER BY 1List NODEs that have no authentication and the web services that can be executed. This is useful for finding nodes that are open to the world. This is a good starting point for securing your system.
This will likely produce some false positives. Why? Because the nodes and routings for the service operation (if NOT REST) may also block the execution of the service operation. For example, if you have a node that is open to the world, but the routing for the service operation is only for a specific node, then the service operation is not open to the world. This query will not take that into account. However, in these cases you should tighten up the security on the node.
This should return a very small list if any at all.
-- Find Web Service Open to the world with no authentication
SELECT
N.MSGNODENAME,
N.DESCR,
N.ACTIVE_NODE,
N.AUTHOPTN,
N.LOCALNODE,
N.NODE_TYPE,
N.USERID,
B.ROLENAME,
C.CLASSID,
WSA.IB_OPERATIONNAME,
WSA.AUTHORIZEDACTIONS
FROM
PSMSGNODEDEFN N,
PSOPRDEFN A,
PSROLEUSER B,
PSROLECLASS C,
PSAUTHWS WSA
WHERE
-- No Authentication
N.AUTHOPTN = 'N'
-- Active Nodes Only
AND N.ACTIVE_NODE = '1'
-- Join in the Node User to the OPRID Table
AND N.USERID = A.OPRID
AND A.OPRID = B.ROLEUSER
AND B.ROLENAME = C.ROLENAME
AND C.CLASSID = WSA.CLASSID
-- Unlocked Users OOnly
AND A.ACCTLOCK = 0
ORDER BY N.MSGNODENAME, WSA.IB_OPERATIONNAME
;Synchronous service operations that fail in production can be almost impossible to diagnose if logging is disabled. On the routing definition (PSIBRTNGDEFN), the LOGMSGDTLFLG field controls what is recorded:
0 = Header Only (no request or response payload)1 = Header & Detail (full request and response payload logged)2 = No Logging (nothing written to PSIBLOGHDR or PSIBLOGDATA)The query below finds all active synchronous service operations where active routings have logging disabled (LOGMSGDTLFLG = 2) or limited to headers only (LOGMSGDTLFLG = 0):
-- Active synchronous service operations with no detail logging
SELECT O.IB_OPERATIONNAME,
OV.VERSIONNAME,
RD.ROUTINGDEFNNAME,
RD.SENDERNODENAME,
RD.RECEIVERNODENAME,
RD.LOGMSGDTLFLG,
CASE RD.LOGMSGDTLFLG
WHEN 0 THEN 'Header Only'
WHEN 1 THEN 'Header & Detail'
WHEN 2 THEN 'No Logging'
ELSE 'Unknown'
END AS LOGGING_LEVEL,
O.DESCR
FROM PSOPERATION O
JOIN PSOPRVERDFN OV
ON O.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND OV.ACTIVE_FLAG = 'A'
JOIN PSIBRTNGDEFN RD
ON RD.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND RD.VERSIONNAME = OV.VERSIONNAME
AND RD.EFF_STATUS = 'A'
WHERE O.OPERATIONTYPE = 1 -- Synchronous
AND RD.LOGMSGDTLFLG IN (0, 2)
ORDER BY RD.LOGMSGDTLFLG DESC, O.IB_OPERATIONNAME;~~ANY~~ to Local Node RoutingsWhen an inbound routing specifies SENDERNODENAME = '~~ANY~~' and delivers to your default local node, Integration Broker accepts messages from any external node or client without requiring an explicit node-to-node routing whitelist.
This is a major security exposure unless the operation is explicitly designed to be completely open to all callers:
-- Inbound routings accepting messages from any sender (~~ANY~~) into the local node
SELECT RD.ROUTINGDEFNNAME,
RD.IB_OPERATIONNAME,
RD.VERSIONNAME,
RD.SENDERNODENAME,
RD.RECEIVERNODENAME,
MN.LOCALDEFAULTFLG,
RD.EFF_STATUS
FROM PSIBRTNGDEFN RD
JOIN PSMSGNODEDEFN MN
ON RD.RECEIVERNODENAME = MN.MSGNODENAME
AND MN.LOCALNODE = 1
AND MN.LOCALDEFAULTFLG = 'Y'
AND MN.ACTIVE_NODE = 1
WHERE RD.SENDERNODENAME = '~~ANY~~'
AND RD.EFF_STATUS = 'A'
ORDER BY RD.IB_OPERATIONNAME;A service operation with an active version (PSOPRVERDFN.ACTIVE_FLAG = 'A') but zero active routings is unreachable. This query identifies dead or abandoned configurations that were partially configured or left behind after a project migration:
-- Active service operations with NO active routings
SELECT O.IB_OPERATIONNAME,
OV.VERSIONNAME,
O.OPERATIONTYPE,
O.DESCR
FROM PSOPERATION O
JOIN PSOPRVERDFN OV
ON O.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND OV.ACTIVE_FLAG = 'A'
WHERE NOT EXISTS (
SELECT 1
FROM PSIBRTNGDEFN RD
WHERE RD.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND RD.VERSIONNAME = OV.VERSIONNAME
AND RD.EFF_STATUS = 'A'
)
ORDER BY O.IB_OPERATIONNAME;A service operation in Integration Broker is only fully active when all three conditions are satisfied:
PSOPRVERDFN.ACTIVE_FLAG = 'A')PSIBRTNGDEFN.EFF_STATUS = 'A')PSOPRHDLR.EFF_STATUS = 'A')If any of the three legs is missing, the service operation cannot process transactions. The query below checks all active operations and flags which components are missing:
-- Audit service operation completeness across versions, routings, and handlers
SELECT O.IB_OPERATIONNAME,
O.OPERATIONTYPE,
CASE WHEN EXISTS (
SELECT 1 FROM PSOPRVERDFN V
WHERE V.IB_OPERATIONNAME = O.IB_OPERATIONNAME
AND V.ACTIVE_FLAG = 'A'
) THEN 'YES' ELSE 'NO' END AS HAS_ACTIVE_VERSION,
CASE WHEN EXISTS (
SELECT 1 FROM PSIBRTNGDEFN R
WHERE R.IB_OPERATIONNAME = O.IB_OPERATIONNAME
AND R.EFF_STATUS = 'A'
) THEN 'YES' ELSE 'NO' END AS HAS_ACTIVE_ROUTING,
CASE WHEN EXISTS (
SELECT 1 FROM PSOPRHDLR H
WHERE H.IB_OPERATIONNAME = O.IB_OPERATIONNAME
AND H.EFF_STATUS = 'A'
) THEN 'YES' ELSE 'NO' END AS HAS_ACTIVE_HANDLER
FROM PSOPERATION O
WHERE EXISTS (
-- Only inspect operations that have at least one active piece
SELECT 1 FROM PSOPRVERDFN V WHERE V.IB_OPERATIONNAME = O.IB_OPERATIONNAME AND V.ACTIVE_FLAG = 'A'
UNION ALL
SELECT 1 FROM PSIBRTNGDEFN R WHERE R.IB_OPERATIONNAME = O.IB_OPERATIONNAME AND R.EFF_STATUS = 'A'
UNION ALL
SELECT 1 FROM PSOPRHDLR H WHERE H.IB_OPERATIONNAME = O.IB_OPERATIONNAME AND H.EFF_STATUS = 'A'
)
ORDER BY O.IB_OPERATIONNAME; SELECT OD.OPRID, RU.ROLENAME, RC.CLASSID, WS.IB_OPERATIONNAME
FROM PSOPRDEFN OD,
PSROLEUSER RU,
PSROLECLASS RC,
PSAUTHWS WS
WHERE OD.OPRID = RU.ROLEUSER
AND RU.ROLENAME = RC.ROLENAME
AND RC.CLASSID = WS.CLASSID
AND OD.ACCTLOCK = 0
-- AND OD.OPRID NOT IN ('PS', 'PSAPPS')
;Get a listing of what Permission List (CLASSID) and Role are granting service operation access.
SELECT DISTINCT A.IB_OPERATIONNAME, B.ROLENAME, A.CLASSID
FROM PSAUTHWS A, PSROLECLASS B
-- change or remove this based on what you are researching.
WHERE A.IB_OPERATIONNAME LIKE 'PERSON%'
AND A.CLASSID = B.CLASSID
ORDER BY 1, 2, 3There should not be shared node users unless that node user has zero permissions. It is god practice to have a distinct OPRID for each node. This is especially true for any node that accepts inbound traffic.
SELECT USERID, COUNT(1)
FROM PSMSGNODEDEFN
WHERE ACTIVE_NODE = 1
GROUP BY USERID
HAVING COUNT(1) > 1;
If you have a root PeopleCode Application Package that you want to find the Service Operation it is associated with, you can use the following query. This is useful for finding the root package of a service operation.
SELECT * fROM PSOPERATIONAC
-- Application Package Root Name
WHERE PACKAGEROOT = 'PERSON_BASIC_FULLSYNC'
;Look up any record, field, page, or component, audit security, and monitor Integration Broker across every database — in seconds.
A web console built for the PeopleSoft community — operational monitoring, security auditing, and metadata browsing in one tool.
On-demand security and operational reports for your PeopleSoft environment — no client install required.
Research any PeopleSoft object and monitor system health from a single browser tab — no App Designer, no SQL.
Chris Malek is a PeopleTools® Technical Consultant with over two decades of experience. He is available for consulting engagements.
Work with ChrisSWS turns SQL into production REST APIs — ready for AI, modern apps, and partner integrations. One install, unlimited potential.
A powerful PeopleSoft bolt-on that makes REST web services easy. You bring the SQL, SWS handles the rest.
Traditional PeopleSoft web services cost $3,600–$13,000 each to develop. SWS deploys production REST APIs in under 5 minutes through configuration alone.
Turn PeopleSoft data into clean REST APIs for AI integrations, modern applications, and vendor data feeds. Configuration-driven — no PeopleCode required.