Establishing and Maintaining Diagnostics

This chapter provides an overview of diagnostics, and discusses how to:

• Establish diagnostics.

• Process diagnostics.

• View diagnostic processing errors.

• View diagnostic reports and manage benchmarks.

See Also

Adding Diagnostics

Access the PeopleSoft Customer Connection website and select Implement, Optimize, and Upgrade » Implementation Guide » Implementation Documentation and Software » Red Paper Library » PeopleTools, PeopleSoft Enterprise Portal, and Other Technology: “PeopleSoft Internal Controls Enforcer Diagnostics ” http://www.peoplesoft.com/corp/en/doc_archive/red_paper/tools_ps_int_controls.jsp

Understanding Diagnostics

This section discusses:

• Diagnostic elements.

• Diagnostic management.

• Diagnostic architecture.

• Diagnostics Data Feed process flow.

• Delivered diagnostics for Oracle's PeopleSoft Enterprise applications.

• Delivered diagnostics for Oracle's JD Edwards EnterpriseOne applications.

Diagnostic Elements

The diagnostic feature provides a way to track and monitor changes to external system configurations that are identified as control points to mitigate risks. The main elements of this feature are:

• Diagnostic.

  A diagnostic is a tool that retrieves data from an external source system by using a query, function, or SQL statement for the purpose of detecting any changes to that data. You can use a function instead of a query when you need to do some additional processing or calculations before sending the data from the external source system to the Enterprise Portal database.

• Diagnostic report.

  A diagnostic report is the set of data that is returned when a diagnostic is run; it contains the rows and columns of retrieved data.

• Diagnostic Data Feed Application Engine process (EPQ_DIAG_FCH).

  You run this process to generate diagnostic reports for a specified compliance project. You can run the process for all diagnostics or a specified list of diagnostics within a single compliance project, based on the parameters that you enter. For each diagnostic that is processed, the system passes the query or function to the external source system, retrieves the resulting data and compares it to the most recent diagnostic report, and if there are changes, stores the report records in PeopleSoft Internal Controls Enforcer.

• Benchmark.

  A benchmark is the version of a diagnostic report that is certified by system users to be the valid results for the diagnostic.

Diagnostic Management

The diagnostics feature uses benchmarks as a means to detect when any controls in the external source system have been modified, potentially indicating that established business rules have been violated. Initially, as you implement a diagnostic, your organization reviews the diagnostic report results that are created by running the diagnostic data feed process. If the report contains the expected values, the run sequence is designated as the benchmark. If the results are not the expected results, then your organization would need to examine the external source system to determine the cause and resolve any problems, then run the diagnostic data feed process to retrieve the correct results, and designate that run sequence as the benchmark. There can be only one currently active benchmark for any diagnostic.

Any time the system determines that the results of a subsequently generated diagnostic report differs from the most recent report, the system:

• Sets the status of every control that is associated with that diagnostic to unproven.

• Sends a notification to the subprocess owners of the associated controls, to inform them of the change.

You can reexamine the results at this time, and if an authorized change was made and the diagnostic report reflects the new values and are therefore correct, then you can designate that report as the new benchmark. If the results indeed reflect an unauthorized change, then you can take steps to reestablish the correct controls.

When the Diagnostic Data Feed process runs, it compares the report results with the most recently generated report, and only stores the new report if it differs. This provides an audit trail that tracks whenever there are changes, which you can review by using the Diagnostic Manager page. Because of this design, when you run diagnostics and a change is detected, if you determine that the change is not a new benchmark, you should correct the external source system and run diagnostics again, then designate that report as the benchmark. This ensures that the report being used for comparison purposes is the benchmark report.

Diagnostic Architecture

The diagnostic architecture is designed to track external source system configuration data that is either low in volume, or is exception information from high volume tables. For example, a diagnostic should not query the entire customer list, rather it should look for specific exceptions to the customer list, such as customers that do not have a defined credit limit. If the queries and functions that are used by diagnostics are not designed using this principal, system performance will severely degrade and the diagnostic could fail; a diagnostic report is limited to 1000 rows and 30 columns.

The following diagram depicts the diagnostic architecture, in which the enterprise portal and the source system communicate using Java routines and Iscript functions.

Diagnostic architecture and communication between the Enterprise Portal

Diagnostics Data Feed Process Flow

This diagram shows the process flow for the Diagnostic Data Feed Application Engine process (EPQ_DIAG_FCH), in which diagnostics data is extracted, the diagnostic report is built, and if the diagnostic data differs from the previous report the control status is set to not proven .

Diagnostic Data Feed process flow of diagnostic data extraction and comparison to previous runs

Delivered Diagnostics for Oracle's PeopleSoft Enterprise Applications

PeopleSoft Internal Controls Enforcer comes with several predefined diagnostic IDs for use with Oracle's PeopleSoft Enterprise Financial Management 8.4, 8.8, and 8.9 databases, and Oracle's PeopleSoft Enterprise Performance Management 8.9 and 9 databases. Because of the structure of the records in the PeopleSoft Enterprise Financial Management database, in many cases multiple diagnostic IDs are required for an audit. Therefore, all of the diagnostic IDs that are listed for a particular audit must be associated with the control definition in PeopleSoft Internal Control Enforcer to use the audit. For example, the journal options audit is comprised of three diagnostics, so to use the journal options audit, you must add three diagnostics to the control that you create for monitoring journal options, selecting JOURNAL_OPTIONS1, JOURNAL_OPTIONS2, and JOURNAL_OPTIONS3 respectively, as the diagnostic IDs.

The following table lists the audits that are provided by using the delivered PeopleSoft Enterprise diagnostics.

Note. The delivered diagnostics for Oracle's PeopleSoft Enterprise applications use the diagnostic source PS_SINGLESIGNON. To use these diagnostics with your implementation, you will need to use the Define Diagnostic Source page to modify the Data URL field for the diagnostic source.

See Specifying Diagnostic Sources.

Single Signon Prerequisite

The delivered diagnostics require that single signon be enabled between the PeopleSoft Enterprise Portal database and the source PeopleSoft Enterprise database.

See Enterprise PeopleTools 8.50 PeopleBook: Security Administration: “Implementing Single Signon”.

Delivered Diagnostics for Oracle's JD Edwards EnterpriseOne Applications

PeopleSoft Internal Controls Enforcer comes with several predefined diagnostic IDs for use with Oracle's JD Edwards EnterpriseOne 8.9, 8.10, and 8.11 applications. The following table lists the audits that are provided by using the delivered diagnostics.

Note. The delivered diagnostics for PeopleSoft EnterpriseOne applications use the diagnostic source PS_ENTERPRISEONE. To use these diagnostics with your implementation, you will need to use the Define Diagnostic Source page to modify the Data URL field for the diagnostic source.

Establishing Diagnostics

This section provides an overview of the diagnostic setup steps and discusses how to:

• Define diagnostic source types.

• Specify diagnostic sources.

• Define query references.

• Define function references.

• Define SQL references.

• Define diagnostics.

Understanding Diagnostic Setup

The steps to establish a diagnostic are:

• (Optional) Define the source type by using the Define Diagnostic Source Type page.

  The source type categorizes the external source systems that are used for diagnostics. When you define a diagnostic source, you must specify the source type with which it is associated. The system is delivered with the following source types: PQ_DAO, PQ_SingleSignon, PQ_EnterpriseOne, PQ_Other. You do not need to complete this step unless you need to define additional source types to suit your implementation.

  Note. If you add a new source type, you also must create and implement a Java routine for that source type.

• Define the location of the external source system by using the Define Diagnostic Source page.

  The system uses the URL and other information, if needed, that you specify on this page, to programmatically access the external source system. Once a source has been defined, it can be used for multiple diagnostics.

  You must modify the fields on this page for the delivered diagnostics to work with your implementation.

• Define references to the queries, functions, or SQL statements for diagnostics, by using the Define Query Reference, Define Function Reference, and Define SQL Reference pages.

  The reference definitions identify a query, function, or SQL statement to use with diagnostics, specify whether the diagnostic requires the system to pass the entity business unit when they run, and the values of any other additional parameters to pass during processing. The queries and functions that are used for diagnostics must exist in the external source system. For PeopleSoft EnterpriseOne diagnostics, complete this step only if you need to specify optional function parameters. You do not need to complete this step to use the delivered PeopleSoft Enterprise diagnostics.

• Establish the diagnostic definition, by using the Define Diagnostics page.

  On this page, you specify the diagnostic ID, indicate the source it uses, and specify the query, function, or SQL that it executes. The delivered diagnostics are already established.

Information about coding requirements for diagnostics is provided in a red paper that is available in the Red Paper Library that is located within the Implement, Optimize, and Upgrade section of the PeopleSoft Customer Connection website.

See Access the customer connection website and select Implement, Optimize, and Upgrade » Implementation Guide » Implementation Documentation and Software » Red Paper Library, PeopleTools, PeopleSoft Enterprise Portal, and Other Technology: “PeopleSoft Internal Controls Enforcer Diagnostics”

Required Steps for Delivered Diagnostics

To use the delivered diagnostics the only steps you need to complete are:

• Modify the diagnostic source definition so that it uses the correct URLs.

• For PeopleSoft EnterpriseOne diagnostics, only if you need to specify optional function parameters, define references to the functions for diagnostics.

Pages Used to Establish Diagnostics

Defining Diagnostic Source Types

Access the Define Diagnostic Source Types page Internal Controls Enforcer, Master Setup, Diagnostic Setup, Source Type Definition).

The following source types are delivered with the system:

• PQ_DAO.

  This source type is for utilizing free-text SQL to retrieve information from remote databases using JDBC (Java Database Connectivity). DAO is an acronym for data access object. Because of security concerns, this source type requires a user name and password to access the remote database. This is provided only as an example.

• PQ_Other.

  This source type is for PeopleSoft Enterprise Financial Management databases without single signon. This is provided only as an example.

• PQ_SingleSignon.

  This source type is for PeopleSoft Enterprise Financial Management databases with single signon. If you intend to set up diagnostics for use with PeopleSoft Enterprise databases, use this option.

• PQ_EnterpriseOne.

  This source type is for PeopleSoft EnterpriseOne applications. If you intend to set up diagnostics for use with PeopleSoft EnterpriseOne, use this option.

To define additional source types, insert rows into the Source Types grid, and complete the following fields:

When you use the Define Diagnostic Source page to set up a diagnostic source, the valid source types are derived from the source types that are established on this page.

Note. This is one of many steps required to add additional sources for use with diagnostics. For example, you must create a Java program to support a new source.

See Also

Access the customer connection website and select Implement, Optimize, and Upgrade » Implementation Guide » Implementation Documentation and Software » Red Paper Library, PeopleTools, PeopleSoft Enterprise Portal, and Other Technology: “PeopleSoft Internal Controls Enforcer Diagnostics”

Specifying Diagnostic Sources

Access the Define Diagnostic Source page (Internal Controls Enforcer, Master Setup, Diagnostic Setup, Source Definition).

PeopleSoft Internal Controls Enforcer uses the information in these fields to access the external source system when retrieving data for diagnostics.

Diagnostic Source Fields

This table defines the various syntax elements of the URLs:

Record.Field.Event.Function

Login Information Fields

The system uses these fields to log on to the external source system. These fields are unavailable for entry if the source type is PeopleSoft Single Signon, as they are not used.

When the Diagnostic Data Feed process is scheduled, it is possible that when the job runs, the single signon authentication may have expired. If so, the system will attempt to reestablish the single signon authentication by using the login information that is specified in the Diagnostic Setup Options group box on the Internal Controls Enforcer General Preferences page.

See Establishing General Preferences.

Additional Parameters Fields

These fields are used to pass additional parameters to the external source system that are required at logon.

For DAO-based data sources, you are required to provide values for the connection and driver parameters, as they specify the JDBC driver to use and the source to connect to. To use the JDBC-ODBC bridge that is delivered with PeopleTools, enter the following parameter names and values, substituting your actual data source name for <data source name>:

Other drivers will have different values. Refer to your driver manufacturer for the format of the connection string.

For an example of a DAO-based source definition, review the PS_DB source ID delivered with the demo database, which is shown here:

See Also

PeopleSoft EnterpriseOne 8.11 Application Integrations with PeopleSoft Enterprise Applications PeopleBook

Defining Query References

Access the Define Query Reference page (Internal Controls Enforcer, Master Setup, Diagnostic Setup, Query Definition).

Query ID and Description

Enter an identifier for the query and a description of the query.

Business Unit Required

Select this option to pass the value of an entity's business unit to the external source system as the first query parameter. This is needed in cases where you are querying control data in a PeopleSoft database, such as the PeopleSoft Enterprise Financial Management database, so that setID redirection can retrieve the appropriate set of records. The business unit for an entity is defined by using the Entity Definition page. See Defining Entities.

To pass additional values as parameters to the query in the external source system, insert rows in the Additional Parameters grid and complete this field:

Defining Function References

Access the Define Function Reference page (Internal Controls Enforcer, Master Setup, Diagnostic Setup, Function Definition).

Function ID and Description

Enter an identifier for the function and a description of the function.

Business Unit Required

Select this check box to pass the value of an entity's business unit to the external source system as the first function parameter. This is needed in cases where the system will access control data in a PeopleSoft database, such as the PeopleSoft Enterprise Financial Management database, so that setID redirection can retrieve the appropriate set of records. The business unit for an entity is defined by using the Entity Definition page. See Defining Entities.

To pass additional values as parameters to the function in the external source system, insert rows in the Additional Parameters grid and complete this field:

Defining SQL References

Access the Define SQL Reference page (Internal Controls Enforcer, Master Setup, Diagnostic Setup, SQL Definition).

SQL Identifier and Description	Enter an ID and description for the SQL reference.
SQL Statement	Enter the SQL to pass to the source system. The SQL statement is limited to 1800 characters. The SQL must adhere to JDBC prepared statement standards. This is especially important with respect to bind variables; they must be defined with “?” instead of “:1”. If the Business Unit Required check box is selected, then the SQL statement must contain at least 1 bind variable.
Business Unit Required	Select this check box to pass the value of an entity's business unit to the external source system as the first SQL parameter. This is needed in cases where the system will access control data in a PeopleSoft database, such as the PeopleSoft Enterprise Financial Management database, so that setID redirection can retrieve the appropriate set of records. The business unit for an entity is defined by using the Entity Definition page. See Defining Entities.

Defining Diagnostics

Access the Define Diagnostics page (Internal Controls Enforcer, Master Setup, Diagnostic Setup, Diagnostic Definition).

Diagnostic ID and Description	Enter an identifier for the diagnostic, and a description of the diagnostic.
Diagnostic Source ID	Select the ID of the external source system that the diagnostic uses. Diagnostic sources are established by using the Define Diagnostic Source page. See Specifying Diagnostic Sources.
<diagnostic source description>	Click to access the Define Diagnostic Source page, where you can review the details of the diagnostic source definition.
Source	Specify the type of reference this diagnostic uses. Options are: Query. Select to define a query-based diagnostic. Function. Select to define a function-based diagnostic. SQL. Select to define a SQL-based diagnostic.
Name	Select the query, function or SQL reference to use for this diagnostic. The list of available values is controlled by the selected source. These references are established by using the Define Function Reference, Define Query Reference, and Define SQL Reference pages, respectively. See Defining Query References. See Defining Function References. See Defining SQL References.

Processing Diagnostics

This section discusses how to:

• Run the Diagnostic Data Feed process.

• View the status of diagnostic runs.

Pages Used to Process Diagnostics

Running the Diagnostic Data Feed Process

Access the Diagnostic Data Feed page (Internal Controls Enforcer, Manage Diagnostic Feed, Run Diagnostic Feed).

Compliance Project	Specify the compliance project for which to run diagnostics.
Load All Diagnostics	Click to insert all diagnostics for the specified compliance project into the Diagnostic Selection grid.
Diagnostic ID	Specify a diagnostic to include in the Diagnostic Data Feed process. Only diagnostics for the currently specified compliance project appear in the list of available values.
Run Log	Click to access the Diagnostic Run Log page, where you can review the process status. See Viewing the Status of Diagnostic Runs.
Run	Click to run the Diagnostic Data Feed Application Engine process (EPQ_DIAG_FCH).

See Also

Enterprise PeopleTools 8.50 PeopleBook: Using PeopleSoft Applications

Enterprise PeopleTools 8.50 PeopleBook: PeopleSoft Process Scheduler

Viewing the Status of Diagnostic Runs

Access the Diagnostic Run Log page (Internal Controls Enforcer, Manage Diagnostic Feed, Diagnostic Run Log).

Process Instance	Displays the process instance for the diagnostic run.
Entity ID	Displays the entity that was processed.
Diagnostic ID	Displays the diagnostic that was used.
DateTime Stamp	Displays when the process ran.
Status	Displays the results of the diagnostic run. Values are: Data Fetched OK: Indicates that the diagnostic run completed and the external source data was successfully retrieved. Exceeded Row Limit of 1000: Indicates that more than 1000 rows of data were fetched. The diagnostics report is limited to 1000 rows. You should review and redesign the associated query or function so that this limit is not exceeded. Exceeded Column Limit of 30: Indicates that more than 30 columns of data were fetched. The diagnostics report is limited to 30 columns. You should review and redesign the associated query or function so that this limit is not exceeded. Query/Function Error: Indicates that the query or function executed, but an error occurred. Invalid XML: Indicates the XML was not in the required XSD format. Communication Error: Indicates that there were issues communicating with the external source system, and as a result the diagnostic could not be processed. Click to access the Diagnostic Error XML page, where you can review the details of the error. See Viewing XML Errors for Diagnostics. Number of rows - Mismatched: When the diagnostic runs, it creates a summary, or “header” row, that includes a count of the number of rows and columns of data that were retrieved. This error message indicates that the actual number of rows retrieved and stored in the Enterprise Portal database did not match the total number of rows calculated in the header row, indicating that there could be a communication error between the external source system database and the Enterprise Portal database. Number of columns - Mismatched: When the diagnostic runs, it creates a summary, or “header” row, that includes a count of the number of rows and columns of data that were retrieved. This error message indicates that the actual number of columns retrieved and stored in the Enterprise Portal database did not match the total number of columns calculated in the header row, indicating that there could be a communication error between the external source system database and the Enterprise Portal database. Query/Function not exists: Indicates that the query ID or function ID used for the diagnostic does not exist in the external source system.

Viewing Diagnostic Processing Errors

This section discusses how to:

• Specify criteria for viewing diagnostic errors.

• View diagnostic errors.

• View XML errors for diagnostics.

Pages Used to View Diagnostic Processing Errors

Specifying Criteria for Viewing Diagnostic Errors

Access the Diagnostic Errors Selection page (Internal Controls Enforcer, Subprocess Management, Diagnostics, Diagnostic Errors).

Compliance Project	Specify the compliance project for which to view diagnostic errors.
All Processes	Select to view errors for all diagnostic processing runs.
Process ID	Specify a process ID for which to review errors. The system automatically clears the All Processes check box when you complete this field.
All Entities	Select to view errors for all entities.
Entity ID	Specify an entity ID for which to review errors. The system automatically clears the All Entities check box when you complete this field.
All Controls	Select to view errors for all controls.
Control ID	Specify a control ID for which to review errors. The system automatically clears the All Controls check box when you complete this field.
Get Errors	Click to access the Diagnostic Errors page, where you can review the error messages for all of the specified processing runs, entities, and controls.

Viewing Diagnostic Errors

Access the Diagnostic Errors page (click the Get Errors button on the Diagnostic Errors Selection page).

This page lists the diagnostic error messages for a compliance project by Diagnostic ID and run date and time.

Viewing XML Errors for Diagnostics

Access the Diagnostic Error XML page (click the Show XML button on the Diagnostic Errors page).

Error Message	Displays the error message, if applicable.
XML	Displays the XML that was returned, which may provide more details about the cause of the error.

Viewing Diagnostic Reports and Managing Benchmarks

This section discusses how to:

• View and compare diagnostic results and manage benchmarks.

• View a diagnostic report.

• Review all diagnostic reports for a control.

• Comparing diagnostic run results.

• Specify criteria for viewing diagnostic history.

• Review diagnostic history.

• View benchmarks for a control.

Pages Used to View Diagnostic Reports and Manage Benchmarks

Viewing and Comparing Diagnostics Results and Managing Benchmarks

Access the Diagnostic Manager page (Internal Controls Enforcer, Subprocess Management, Diagnostics, Diagnostic Manager).

This page lists every diagnostic run for all of the diagnostics associated with a single control instance. Within the Diagnostics group box, each diagnostic is listed by diagnostic ID. For each diagnostic ID, the Diagnostic Instances grid lists every processing run on a separate row.

Compare Display Option

These options control the information from the diagnostic run results that the system displays when you view the Diagnostic Comparison page by clicking the various compare links.

Diagnostic Instances

Viewing a Diagnostic Report

Access the Diagnostic Report page (Click the View Report link on the Diagnostic Manager page).

This page lists the diagnostic results for a specific diagnostic run.

The fields that comprise the diagnostic report are listed as the columns of the Diagnostic Report grid, and each row in the grid corresponds to a row of diagnostic results.

Reviewing All Diagnostic Reports for a Control

Access the Diagnostic Reports by Control page (Internal Controls Enforcer, Subprocess Management, Diagnostics, Diagnostic Reports By Control).

This page lists, for a single control instance, the most recent diagnostic report for every diagnostic that is associated with the control.

For each diagnostic, the Diagnostic Report grid list the fields of the diagnostic report as the column titles, and the rows contain the data retrieved.

Comparing Diagnostic Run Results

Access the Diagnostics Comparison page (click either the Compare Selected or Compare to Benchmark link on the Diagnostic Manager page.).

This page shows the diagnostic results for the runs selected on the Diagnostic Manager page, for comparison purposes. The results are listed by sequence, which equates to a single row of data from the diagnostic report. For each sequence, the first column of the Diagnostic Reports grid contains the field names that are in the diagnostic report, and the remaining columns list the results for each field for a each selected run, with the run date and time as the column heading. This view provides you with a row by row comparison of the diagnostic report for the selected runs.

Benchmark results appear bold to distinguish them, and changed data appear blue and bold to distinguish them.

If you opt to show only changed sequences, this page displays only the results that differ, which means, in general, that there would be fewer columns listed in each sequence grid (unless the results differ every run). If no changes take place for a particular row of data, then the sequence grid is blank. In the example shown here, only changed sequences appear, and because there were no differences in the results for sequences 1 and 3 for the two selected comparison runs, they are blank. In sequence 2 there were differences between the two diagnostic runs, and the changed value is “N” as indicated by the blue. The oldest run was a benchmark.

Specifying Criteria for Viewing Diagnostic History

Access the Diagnostics History page (Internal Controls Enforcer, Subprocess Management, Diagnostics, Diagnostic Compare – History).

Entity ID, Diagnostic ID, Start Date, and End Date	Specify the diagnostic, entity, and date range for which to view diagnostic results.
Compare	Click to view the Diagnostic History Comparison page, and see the historical results.

Compare Display Option

These options control the information from the diagnostic run results that the system displays when you view the Diagnostic History Comparison page by clicking Compare.

Reviewing Diagnostic History

Access the Diagnostic History Comparison page (click Compare on the Diagnostics History page).

This page shows the results of the criteria that were specified on the Diagnostic History page. The results are listed by sequence, which equates to a single row of data from the diagnostic report. For each sequence, the first column of the Diagnostic Reports grid contains the field names that are in the diagnostic report, and the remaining columns list the results for each field for a particular run, with the run date and time as the column heading. This view provides you with a comparison, over time, of each row of the diagnostic report.

The benchmark results are bold to distinguish them from the other data.

If you opt to show only changed sequences, then the report lists only the results that differ, which means, in general, that there would be fewer columns listed in each sequence grid (unless the results differ every run). If no changes take place over time for a particular row of data, then the sequence grid is blank. The changed data are blue and bold to distinguish them.

Viewing Benchmarks for a Control

Access the Diagnostic Reports By Control - Benchmark page (click the View Benchmarks link on the Diagnostic Reports by Control page).

This page shows the details for the diagnostic report that is the current benchmark. The column titles within the Diagnostic Report grid are the fields that comprise the diagnostic report.