Skip to Main Content
Return to Navigation

Creating Queries to View Audit Records Details

One way to view the information is to use PeopleSoft Query. This section assumes a working knowledge of PeopleSoft Query, and provides some sample queries that show the type of information that you can expect to view.

This section discusses how to:

Creating an Access Group

To track audit records, it's useful to create an Access Group in Query Access Manager that contains all audit records. This makes it easier to access the audit records under PeopleSoft Query:

Image: Query Access Manager

This example illustrates the fields and controls on the Query Access Manager.

Query Access Manager

Listing All Audit Records in PS_AUDIT_JOB

Select all the fields from AUDIT_JOB. There are no extra criteria to add:

Image: Query page

This example illustrates the fields and controls on the Query page.

Query page

Listing All Audit Records for a Specified User ID

This query is similar to the previous one but with the following criteria added:

Image: Criteria page

This example illustrates the fields and controls on the Criteria page.

Criteria page

The example shows the prompt for properties the AUDIT_OPRID field:

Image: Edit Prompt Properties page

This example illustrates the fields and controls on the Edit Prompt Properties page.

Edit Prompt Properties page

Set up a prompt for User ID against the PSOPRDEFN table. That way, when you run the query, you can specify a particular user ID. In this case, the query focuses on User ID VP1:

Image: Prompt field

This example illustrates a prompt for User ID against the PSOPRDEFN table.

Prompt field

Listing All Audit Records Containing an Invalid OPRID

This query is similar to the previous one, but you specify different criteria:

Image: Edit Criteria Properties page

This example illustrates the fields and controls on the Edit Criteria Properties page.

Edit Criteria Properties page

Click the Define/Edit Subquery link and select the OPRID field:

Image: Fields page

This example illustrates the fields and controls on the Fields page.

Fields page

The subquery selects distinct User ID from PSOPRDEFN.

Image: View SQL page

This example shows the SQL for the query.

View SQL page

Listing All Audit Records for a Specified Time Period

This example shows a query containing the same fields as in the previous queries above, with different criteria.

Image: Criteria page

This example illustrates the fields and controls on the Criteria page.

Criteria page

Set the prompt properties to follow this example.

Image: Edit Prompt Properties page

This example illustrates the fields and controls on the Edit Prompt Properties page.

Edit Prompt Properties page

Change the AUDIT_STAMP field type to Date to enable the user to take advantage of the calendar control as a prompt mechanism.