How To
Summary
This document describes how to use the Content Platform Engine administrative console, ACCE, to run the OOTB reports provided with IBM Enterprise Records and export the results in a comma separated value (CSV) format.
Objective
Steps
Seven report templates are provided with IBM Enterprise Records:
- Actions performed by a user
- Entities placed on hold
- Containers without an associated disposition schedule
- Items associated with a disposition schedule
- File plan structure
- Electronic records content viewed by a user
- Vital records due for vital review
These templates are automatically added to the File Plan Object Store in the Records Management > Report Definitions folder. The templates are formatted for use in the IBM Enterprise Records Administration desktop and the Cognos Reporting tools. However, embedded in the templates are the SQL queries that with some minor editing can be used in the ACCE search tool.
Notes
- Some of the templates contain more than one SQL query.
- Some of the queries, for example, actions performed by a user, query information in the event log. To use these queries, auditing must be configured and actions performed that put data into the event log.
- Some of the queries include optional features, such as vital records, that might not be used in all records management implementations.
This document explains how to tailor the SQL queries for use in ACCE and provides tips on using ACCE effectively to generate the reports. Consider using the queries as examples for developing custom reports that include features relevant to your implementation of IBM Enterprse Records.
The following procedure explains how to process a report query in ACCE and export the results to a CSV file. The OOTB report queries are provided later in this technote.
- Log in to ACCE as an object store administrator or with an account that has sufficient permissions to view data in the appropriate File Plan and Records object stores.
- Navigate to the Search function in a File Plan object store (FPOS).
- Select the New Object Store Search option.
- Click the SQL View tab.
- Enter the appropriate query using the information provided later in this technote, then click Run.
- Once the results have been returned, select all the records of interest in the Search Results tab, then select 'Export to a File' from the Actions drop-down menu.
Tip: Check the box to the side of Object Name to select all the results.
- CSV is the only supported export file format.
Known Issue: Prior to Content Platform Engine 5.5.6, if the data returned by the search includes commas, the commas are treated as column breaks.
- The first 20 results are likely to return quickly, but wait until the complete result set returns before using the Export to a File option. The information about the number of objects retrieved is displayed below the Actions drop-down menu.
- To prevent your reports impacting other users of the environment, limit the number of items a search can return and how long a search can run.
Maximum Result limit defines the maximum number of results that the query can return. By default the value is set to 500. Set this limit using the option on the search screen.
COUNT_LIMIT requests that the Content Platform Engine count the number of search results up to the specified limit for a paged search. Set this limit in the query syntax. By default, COUNT_LIMIT is set to 1000.
TIMELIMIT defines the maximum time in seconds for a client-to-server RPC. Set this option as part of the query syntax, or using the option on the search screen.
For more information on valid query syntax, refer to the following topic in the FileNet Content Manager Knowledge Center: https://www.ibm.com/support/knowledgecenter/SSNW2F_5.5.0/com.ibm.p8.ce.dev.ce.doc/query_sql_syntax_ref.htm
- Consider using the background search capability in ACCE if the result set is likely to be large or if you want to schedule when the searches are to run. For more information on using background search, refer to the following topic in the FileNet Content Manager Knowledge Center: https://www.ibm.com/support/knowledgecenter/SSNW2F_5.5.0/com.ibm.p8.ce.dev.ce.doc/query_concepts.htm#query_concepts__background_searches. This technote demonstrates how to set up a background search: https://www.ibm.com/support/pages/node/6434429.
Each of the OOTB report queries is described in this section along with the edits required to process the queries in ACCE.
General Edits
The following information applies to all queries:
- When entering a query in the SQL View all queries start with <variable>.this, where <variable> identifies the type of object being queried. For example, for Record Category queries, the SQL syntax starts with RC.this, for Record Info Object queries, the SQL syntax start with RI.this.
The SQL statements in the source report files do not contain this component; however, the queries shown in this technote have been updated to include this information.
- Enter any dates in the following format: YYYY-MM-DDTHH:MM:SSZ
A four digit year, two-digit month, two-digit day, followed by the letter T, then, optionally, the time as a 2-digit hour, 2-digit minutes, two-digit seconds followed by the letter Z.
For example, enter 2021-02-24T08:00:00Z for 24 February 2021 at 08:00 Zulu (GMT) time.
-
The date components in the queries are optional.
-
Use single quotation marks (') around user names, folder paths, and other string variables.
-
All strings are case-sensitive, including user names and folder paths.
- The File Plan paths typically start with /Records Management/<file plan name>. The File Plan path always starts with a forward slash (/) and can be extended beyond the File Plan name to limit the query results to a specific node in the File Plan.
- In the SQL statements that follow, variables are shown between {? and }. In your queries, replace these characters and the variable name with a value that applies to your environment.
Actions Performed by the user report
For a record category
SELECT RC.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType,RC.RecordCategoryName AS EntityName, RMF.PathName, RC.AggregationFROM (Event EV INNER JOIN RecordCategory RC ON EV.SourceObjectID = RC.ID)INNER JOIN RMFOLDER RMF on RC.parent=RMF.This WHERE EV.DateCreated >= {?start_date} AND EV.DateCreated <= {?end_date} AND EV.LastModifier = '{?user_name}' AND RC.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- user_name is p8admin
- fileplan_name is Records Management/File Plan
- start_date and end_date have been removed as they are optional components
SELECT RC.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType,RC.RecordCategoryName AS EntityName, RMF.PathName, RC.Aggregation FROM (Event EV INNER JOIN RecordCategory RC ON EV.SourceObjectID = RC.ID)INNER JOIN RMFOLDER RMF on RC.parent=RMF.This WHERE EV.LastModifier = 'p8admin' AND RC.this INSUBFOLDER '/Records Management/File Plan'
For a record folder
SELECT RF.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType,RF.RecordFolderName AS EntityName,RMF.PathName, RF.AggregationFROM (Event EV INNER JOIN RecordFolder RF ON EV.SourceObjectID = RF.ID)INNER JOIN RMFOLDER RMF on RF.parent=RMF.This WHERE EV.DateCreated >= {?start_date} AND EV.DateCreated <= {?end_date} AND EV.LastModifier = '{?user_name}' AND RF.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- user_name is p8admin
- fileplan_name is Records Management/File Plan
- start_date is 24 February 2020 and no end date is defined
SELECT RF.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType,RF.RecordFolderName AS EntityName,RMF.PathName, RF.Aggregation FROM (Event EV INNER JOIN RecordFolder RF ON EV.SourceObjectID = RF.ID)INNER JOIN RMFOLDER RMF on RF.parent=RMF.This WHERE EV.DateCreated >= 2020-02-24 AND EV.LastModifier = 'p8admin' AND RF.this INSUBFOLDER '/Records Management/File Plan'
For a record
SELECT RI.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType,RCR.ContainmentName AS EntityName, F.PathName, RI.Aggregation FROM ((Event EV INNER JOIN RecordInfo RI ON EV.SourceObjectID = RI.ID ) INNER JOIN ReferentialContainmentRelationship RCR ON RCR.head = RI.this ) INNER JOIN Folder F ON F.this = RCR.tail WHERE EV.DateCreated >= {?start_date} AND EV.DateCreated <= {?end_date} AND EV.LastModifier = '{?user_name}' AND RI.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- user_name is p8admin
- fileplan_name is Records Management/File Plan/Insurance Category. so the search is limited to this specific category and all its subcategories
- start_date is 24 February 2020 and the end_date is 30 June 2021
SELECT RI.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType,RCR.ContainmentName AS EntityName, F.PathName, RI.Aggregation FROM ((Event EV INNER JOIN RecordInfo RI ON EV.SourceObjectID = RI.ID ) INNER JOIN ReferentialContainmentRelationship RCR ON RCR.head = RI.this ) INNER JOIN Folder F ON F.this = RCR.tail WHERE EV.DateCreated >= 2020-02-24 AND EV.DateCreated < 2021-06-30 AND EV.LastModifier = 'p8admin' AND RI.this INSUBFOLDER '/Records Management/File Plan/Insurance Category'
For a record volume
SELECT VL.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType, VL.VolumeName AS EntityName,RMF.PathName, VL.Aggregation FROM (Event EV INNER JOIN Volume VL ON EV.SourceObjectID = VL.ID)INNER JOIN RMFOLDER RMF on VL.parent=RMF.This WHERE EV.DateCreated >= {?start_date} AND EV.DateCreated <= {?end_date} AND EV.LastModifier = '{?user_name}' AND VL.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- user_name is p8admin
- fileplan_name is Records Management/File Plan/Home Mortgage Category. so the search is limited to this specific category and all its subcategories
- The start and end dates are not defined, so all record volumes in the specified category tree are returned
SELECT VL.this, EV.LastModifier, EV.DateCreated, EV.ClassDescription, EV.AuditActionType, VL.VolumeName AS EntityName,RMF.PathName, VL.Aggregation FROM (Event EV INNER JOIN Volume VL ON EV.SourceObjectID = VL.ID)INNER JOIN RMFOLDER RMF on VL.parent=RMF.This WHERE EV.LastModifier = 'p8admin' AND VL.this INSUBFOLDER '/Records Management/File Plan/Home Mortgage Category'
Entities Placed on Hold report
For containers on hold
SELECT RF.FolderName AS EntityName, RHL.Datecreated AS HoldDate, RMF.PathName AS PathName, RF.Aggregation AS Aggregation, RH.HoldName AS HoldNameFROM ((Recordhold RH INNER JOIN RMFolderHoldlink RHL ON RH.This = RHL.Tail)INNER JOIN RMFolder RF ON RF.This = RHL.Head)INNER JOIN RMFolder RMF on RF.parent=RMF.This WHERE RF.IsDeleted = FALSE AND RH.HoldName = '{?hold_name}'AND RHL.Datecreated >= {?start_date} AND RHL.Datecreated <= {?end_date} AND RF.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- The Record Management entity type is Record Folder
- hold_name is TestHold
- fileplan_name is Records Management/File Plan
- start_date and end_date have been removed as they are optional components
SELECT RF.This, RF.FolderName AS EntityName, RHL.Datecreated AS HoldDate, RMF.PathName AS PathName, RF.Aggregation AS Aggregation, RH.HoldName AS HoldName FROM ((Recordhold RH INNER JOIN RMFolderHoldlink RHL ON RH.This = RHL.Tail) INNER JOIN RMFolder RF ON RF.This = RHL.Head) INNER JOIN RMFolder RMF on RF.parent=RMF.This WHERE RF.IsDeleted = FALSE AND RH.HoldName = 'TestHold' AND RF.this INSUBFOLDER '/Records Management/File Plan'
For records on hold
SELECT RI.This, DocumentTitle AS EntityName, RHL.DateCreated AS HoldDate, F.PathName AS PathName, RI.Aggregation AS Aggregation, RH.HoldName AS HoldName FROM (((Recordhold RH INNER JOIN RecordHoldlink RHL ON RH.This = RHL.Tail)INNER JOIN RecordInfo RI ON RI.This = RHL.Head)INNER JOIN ReferentialContainmentRelationship RCR ON RI.this = RCR.Head)INNER JOIN RMFolder F ON F.this = RCR.Tail WHERE RI.IsDeleted = FALSE AND RH.HoldName = '{?hold_name}' AND RHL.DateCreated >= {?start_date} AND RHL.DateCreated <= {?end_date} AND RI.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- The Record Management entity type is Record
- hold_name is TestHold
- fileplan_name is Records Management/File Plan
- start_date and end_date have been removed as they are optional components
SELECT RI.This, DocumentTitle AS EntityName, RHL.DateCreated AS HoldDate, F.PathName AS PathName, RI.Aggregation AS Aggregation, RH.HoldName AS HoldName FROM (((Recordhold RH INNER JOIN RecordHoldlink RHL ON RH.This = RHL.Tail)INNER JOIN RecordInfo RI ON RI.This = RHL.Head)INNER JOIN ReferentialContainmentRelationship RCR ON RI.this = RCR.Head)INNER JOIN RMFolder F ON F.this = RCR.Tail WHERE RI.IsDeleted = FALSE AND RH.HoldName = 'TestHold' AND RI.this INSUBFOLDER '/Records Management/File Plan'
Containers without an associated disposition schedule report
For a record folder
SELECT RF.This, RF.FolderName, RF.Aggregation, RMF.PathName FROM RecordFolder RF INNER JOIN RMFOLDER RMF on RF.parent=RMF.This WHERE RF.DisposalSchedule IS NULL AND RF.IsDeleted = FALSE AND RF.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- The Record Management entity type is Record Folder
- fileplan_name is Records Management/File Plan
For a record category
SELECT RC.This, RC.FolderName, RC.Aggregation, RMF.PathName FROM RecordCategory RC INNER JOIN RMFOLDER RMF on RC.parent=RMF.This WHERE RC.DisposalSchedule IS NULL AND RC.RMRetentionTriggerPropertyName IS NULL AND RC.RMRetentionPeriod IS NULL AND RC.IsDeleted = FALSE AND RC.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example
- The Record Management entity type is Record Category
- fileplan_name is Records Management/File Plan
SELECT RC.This, RC.FolderName, RC.Aggregation, RMF.PathName FROM RecordCategory RC INNER JOIN RMFOLDER RMF on RC.parent=RMF.This WHERE RC.DisposalSchedule IS NULL AND RC.RMRetentionTriggerPropertyName IS NULL AND RC.RMRetentionPeriod IS NULL AND RC.IsDeleted = FALSE AND RC.this INSUBFOLDER '/Records Management/File Plan'
Items associated with a disposition schedule report
For record containers
SELECT RF.This, RF.FolderName AS EntityName, RF.Aggregation AS Aggregation, DS.DisposalScheduleName, RMF.PathName FROM (RMFOLDER RF INNER JOIN DisposalSchedule DS ON RF.DisposalSchedule = DS.this) INNER JOIN RMFOLDER RMF on RF.parent=RMF.This WHERE RF.IsDeleted = FALSE AND DS.DisposalscheduleName = '{?disposal_schedule}'
Example:
In the following example
- The Record Management entity type is Record Folder
- disposal_schedule is an advanced disposition schedule called Destroy Category Schedule
SELECT RF.This, RF.FolderName AS EntityName, RF.Aggregation AS Aggregation, DS.DisposalScheduleName, RMF.PathName FROM (RMFOLDER RF INNER JOIN DisposalSchedule DS ON RF.DisposalSchedule = DS.this) INNER JOIN RMFOLDER RMF on RF.parent=RMF.This WHERE RF.IsDeleted = FALSE AND DS.DisposalscheduleName = 'Destroy Category Schedule'
For records
SELECT RT.This, RT.RecordTypeName AS EntityName, RT.ClassDescription as Aggregation, DS.DisposalScheduleName FROM RecordType RT INNER JOIN DisposalSchedule DS ON RT.DisposalSchedule = DS.this WHERE DS.DisposalscheduleName = '{?disposal_schedule}'
Example:
In the following example
- The Record Management entity type is Record
- disposal_schedule is an advanced disposition schedule called AutoDestroySchedule
SELECT RT.This, RT.RecordTypeName AS EntityName, RT.ClassDescription as Aggregation, DS.DisposalScheduleName FROM RecordType RT INNER JOIN DisposalSchedule DS ON RT.DisposalSchedule = DS.this WHERE DS.DisposalscheduleName = 'AutoDestroySchedule'
File Plan Structure report
For record category
SELECT RecordCategory.This, RecordCategory.PathName, RecordCategory.AGGREGATION FROM RecordCategory WHERE RecordCategory.IsDeleted = FALSE AND RecordCategory.This INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example, the query returns all the categories in the designated file plan that have not been deleted. If your environment is configured to save information about deleted items, you can change the RecordCategory.IsDeleted setting from FALSE to TRUE:
- The aggregation type is Record Category
- fileplan_name is Records Management/File Plan
SELECT RecordCategory.This, RecordCategory.PathName, RecordCategory.AGGREGATION FROM RecordCategory WHERE RecordCategory.IsDeleted = FALSE AND RecordCategory.This INSUBFOLDER '/Records Management/File Plan'
For record folder
SELECT RecordFolder.This, RecordFolder.PathName, RecordFolder.AGGREGATION FROM RecordFolder WHERE RecordFolder.IsDeleted = FALSE AND RecordFolder.This INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example, the query returns all the record folders in the designated file plan that have not been deleted. If your environment is configured to save information about deleted items, you can change the RecordFolder.IsDeleted setting from FALSE to TRUE:
- The aggregation type is Record Folder
- fileplan_browse is Records Management/File Plan/Home Mortgage to limit the scope of the search to the folders in the category called Home Mortgage
SELECT RecordFolder.This, RecordFolder.PathName, RecordFolder.AGGREGATION FROM RecordFolder WHERE RecordFolder.IsDeleted = FALSE AND RecordFolder.This INSUBFOLDER '/Records Management/File Plan/Home Mortgage'
For record volume
SELECT Volume.This, Volume.PathName, Volume.AGGREGATION FROM Volume WHERE Volume.IsDeleted = FALSE AND Volume.This INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example, the query returns all the volumes in the Home Mortgage category that have not been deleted.
- The aggregation type is Volume
- fileplan_browse is Records Management/File Plan/Home Mortgage to limit the scope of the search to the folders in the category called Home Mortgage
Electronic records content viewed by a user report
This query is run against the Records Object Store (ROS) and has the following prerequisites:
- Auditing must be configured on the File Plan object store (FPOS).
- Auditing of the GetContent event must be configured on the records-enabled document classes of the ROS.
- Use ACCE to update the default instance security of the Get Content Event class in the ROS as follows:
- In the ROS navigate to Other Classes > Event > Retrieval Event > Get Content Event.
- Select the Default Instance Security tab.
- Click Add Permissions.
- Select Add user/group permissions, then select the users who will be running the Electronic records content viewed by a user report.
- For Permission Group, select View properties <Default>.
- Save your changes.
Once the configuration changes have been made, ensure users perform some updates on the records prior to running the report.
SELECT GC.This, GC.DateLastModified, DC.DocumentTitle, F.PathName, GC.LastModifier FROM ((Document DC INNER JOIN GetContentEvent GC ON DC.id = GC.SourceObjectId)INNER JOIN ReferentialContainmentRelationship RCR ON DC.This = RCR.Head)INNER JOIN Folder F ON F.This = RCR.Tail WHERE DC.RecordInformation IS NOT NULL AND GC.DateCreated >= {?start_date} AND GC.DateCreated <= {?end_date} AND GC.LastModifier = '{?user_name}'
Example:
In the following example, the query returns the records that have been accessed by the user called p8admin.
SELECT GC.This, GC.DateLastModified, DC.DocumentTitle, F.PathName, GC.LastModifier FROM ((Document DC INNER JOIN GetContentEvent GC ON DC.id = GC.SourceObjectId) INNER JOIN ReferentialContainmentRelationship RCR ON DC.This = RCR.Head) INNER JOIN Folder F ON F.This = RCR.Tail WHERE DC.RecordInformation IS NOT NULL AND GC.LastModifier = 'p8admin'
Vital Records due for Vital Review report
SELECT RI.This, RI.VitalRecordNextReviewDate, F.PathName, RCR.ContainmentName AS EntityName FROM (RecordInfo RI INNER JOIN ReferentialContainmentRelationship RCR ON RI.This = RCR.Head) INNER JOIN Folder F ON F.This = RCR.Tail WHERE RI.IsDeleted = FALSE AND RI.IsVitalRecord = TRUE AND RI.VitalRecordNextReviewDate >= {?start_date} AND RI.VitalRecordNextReviewDate <= {?end_date} AND RI.this INSUBFOLDER '/{?fileplan_name}'
Example:
In the following example, the query returns the vital records that
- Have review dates between 24 February 2020 and 30 June 2021.
- Are in a category called Home Mortgage in the file plan called Loan File Plan.
Downloading the report engine queries
The queries shown in this document might change in the future. Use the following procedure to download the source queries that are added to the File Plan object store as part of an install or upgrade of IBM Enterprise Records:
- Log in to the IBM Administrative Console for Content Platform Engine (ACCE).
- Go to the FilePlan Object Store and navigate to Records Management.
- Go to Report Definitions and select any report you see in the Contents tab.
- Go to the Content Elements tab and select the option to download the query from the Content Element drop-down list.
- Save the report query.
- Repeat the steps to download the remaining report queries.
Related Information
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
13 August 2021
UID
ibm16412209