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.

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.

This example illustrates an access group, AUDITS, and record, AUDIT_JOB, added to the QUERY_TREE_PT tree on the Query Access Manager page.

 Query Access Manager page with query tree, access group, and record

Access Query Manager (select Reporting Tools > Query > Query Manager), create a new query, and add the audit record.

Select all the fields from AUDIT_JOB. There are no extra criteria to add at this point.

This example illustrates Query Manager - Query page, with all the fields in the AUDIT_JOB record selected.

 Query Manager - Query page, with all the fields in the AUDIT_JOB record selected.

This query is similar to the previous one. This query adds a criteria that is based on a prompt for user ID.

On the Query Manager - Criteria page, click Add Criteria and define an expression with AUDIT_OPRID equal to a prompt.

This example illustrates the Edit Criteria Properties page, with a field equal to a prompt.

 Edit Criteria Properties in Query Manager, with a field equal to a prompt

Select New Prompt to set the prompt properties. This example prompt is a character type named BIND1, based on the PSOPRDEFN prompt table.

This example illustrates the Edit Prompt Properties page with the PSOPRDEFN prompt table.

 Edit Prompt Properties page.

This is set up as 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 AADAMS.

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

 Prompt field for user ID

After defining the criteria, it appears in the second row in the Criteria page grid for the query.

This example illustrates the Query Manager - Criteria page.

Query Manager - Criteria page with criteria that prompts for user ID.

This query is similar to the previous one. In this case, when you edit the criteria properties, you use an expression with the condition "not in list," and define a subquery to compare the OPRID.

This example illustrates an expression on the Edit Criteria Properties page that uses a subquery.

 Edit Criteria Properties page with an expression using a subquery.

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

This example illustrates the Fields page when defining a subquery for AUDIT_OPRID.

 Query Manager - Fields page for subquery for OPRID

The subquery selects distinct User ID from PSOPRDEFN.

This example shows a query containing the same fields as in the previous queries, with two criteria that select data between two specified dates.

This example illustrates the Criteria page for the query after defining the criteria based on the AUDIT_STAMP field.

 Query Manager - Criteria page with two AUDIT_STAMP date criteria

When you add the criteria and define the prompt properties, change the AUDIT_STAMP field type to Date to enable the user to take advantage of the calendar control as a prompt mechanism. The Heading Text and Unique Prompt Name must be different for the two criteria.

This example illustrates the Edit Prompt Properties page for the AUDIT_STAMP Date prompt.

 Edit Prompt Properties page for AUDIT_STAMP Date prompt