Thanks, @Scott Nishizaki, for the shout-out.
There are already some excellent answers here about how to query the metadata, and that can get you most of the way. For instance, finding the fields should be easy using Stephen Forrest's first SQL.
Expressions will be harder, as the field references are replaced by some numeric references that aren't easy to translate back. I ran across an intriguing presentation about how UNC built a "Metadata Detective Tool" that looks like it might help make investigations like this easier, but I haven't taken the time to watch it yet.
The presentation "other Scott" is referring to spends a lot of time talking about all kinds of metadata tables, but then goes into the basics of a tool we developed at Azusa Pacific to export full SQL text for each PS Query into a table to allow for more easily searching the text for instances of hard-coded values, etc. With this tool, you could do the following:
- Run an export of all query SQL to a table (ours is called PS_APU_QRY_SQL_EXP)
- Write a query as follows:
- Use PSQRYDEFN to rule out disabled queries, get the Query Type, etc., then
- Use PSQRYRECORD to filter to queries that use the sensitive record(s) and get the CORRNAME used as the table alias (e.g., the ""A" in "A.EMPLID").
- Use PSRECFIELDALL to find all fields associated with that record, and optionally you could filter to just the fields you care about
- Use your Query export table to look for mentions of [Alias].[Fieldname]
SELECT A.OPRID
, A.QRYNAME
, DECODE(A.OPRID,' ','Public','') PUBLIC_QRY
, B.CORRNAME
, B.RECNAME
, C.FIELDNAME
FROM PSQRYDEFN A
JOIN PSQRYRECORD B
ON A.OPRID = B.OPRID
AND A.QRYNAME = B.QRYNAME
JOIN PSRECFIELDALL C
ON B.RECNAME = C.RECNAME
JOIN PS_APU_QRY_SQL_EXP D
ON A.OPRID = D.APU_OPRID
AND A.QRYNAME = D.QRYNAME
WHERE A.QRYDISABLED <> 'Y'
AND B.RECNAME IN ('SENSITIVE_REC1', 'SENSITIVE_REC2')
AND C.FIELDNAME IN ('SSN', 'GPA', 'OTHER_SENSITIVE_FIELD')
AND D.QRYSQL LIKE '%' || B.CORRNAME || '.' || C.FIELDNAME || '%'
This still isn't perfect, as it will find any reference to these fields, including those just in joins and criteria... but it could be a good start! If I were doing this research and didn't have time to build an even more perfect tool for the job, I would first run Stephen Forrest's SQL to find the offending fields, then run this and perhaps filter out the queries that I already looked at from the first list. That would give a smaller list of queries to investigate for sensitive expressions.
Also, if you're recreating my SQL above in a PS Query, you'll probably need to use a fancy trick on the last line of criteria where you say expression 'x' is equal to: 'x' AND D.QRYSQL LIKE '%' || B.CORRNAME || '.' || C.FIELDNAME || '%'
Good luck!
------------------------------
Scott Douglas
Sr. Business Systems Analyst, Student Information Systems
Azusa Pacific University
------------------------------
Original Message:
Sent: 11-07-2025 08:40 AM
From: David Ehrlich
Subject: Auditing Queries that output sensitive data
Hi -
QRYTYPE is referring to the "Query Type" field which you can select on via the advanced search record display
...and view within the Properties link in the query.
The value of 1 refers to "User".
Original Message:
Sent: 11/6/2025 1:11:00 PM
From: Scott Nishizaki
Subject: RE: Auditing Queries that output sensitive data
Hi Lidia,
My fantastic colleague @Scott Douglas did an Alliance presentation on this topic a few years back. You can find the recording here. I know that he has done something like what you are attempting (with different fields) so hopefully he will add some context here for you.
As for why you're only getting 1, best guess would be a PS Query "secret" security join, where it's automatically restricting query results based on your oprid, but I don't know for sure without know what records you're querying.
------------------------------
Scott Nishizaki
Connected Campus Community of Practice
Developer/Analyst
Azusa Pacific University
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
Original Message:
Sent: 11-05-2025 10:48 AM
From: Lidia Anderson
Subject: Auditing Queries that output sensitive data
Hello HEUG Friends!
I am working on a project to audit all queries that contain sensitive data, such as DOB, SS#, Banking Information, etc. I have a few questions and hope all you awesome people can help.
- How would you go about finding all the sensitive fields?
- How do you determine if the record is public or private? I saw a query type of 1 or 0, but for some reason everything comes back as 1 for me, even though I'm using the correct record and field.
I have identified the records to query, but this creates a lot of false positives because a record may be used and its fields may exist, even if the field isn't being presented.
- How would you find the outputs, as a field and as an expression?
- How would you find data sources used in things like BI-Publisher for example?
Any help and a push in the right direction will help me.
THANK YOU!
------------------------------
Lidia Anderson
Manager, Campus Solutions
Central Washington University
lidia.anderson@cwu.edu
------------------------------