Understand and Generate the Data Change Tracker Report

PATH: System Administration > Data Change Tracker > Data Change Tracker Report

Data Change Tracker functionality is available as a Premium Product for all Campus Cloud Choice and Onsite Hosted customers.

The Data Change Tracker Report details all data changes which have occurred in tables tracked by Data Change Tracker functionality.

Using filter criteria, you can view data changes which have occurred by specific users for specific tables during a specific time and even specific modification type(s). You can also view a large amount of data for many tables over a period of time and sort this data using available sorting options.

Image 1: Data Change Tracker Report

Prerequisites

Before generating the Data Change Tracker Report, the following must be completed:

Purpose of the Data Change Tracker Report

The Data Change Tracker tool was designed to help users view and analyze data modified within Campus and its database. This report helps users:

What this report is NOT intended to accomplish

It is important to understand what this report is NOT intended to accomplish, such as:

Tool Rights

PATH: System Administration > User Security > Users > Tool Rights

Users need at least R(ead) tool rights in order to generate the Data Change Tracker Report.

Image 2: Data Change Tracker Report Tool Rights

Generating the Data Change Tracker Report 

The following steps should be followed when generating the Data Change Tracker Report:

  1. Select Affected Tables
  2. Enter Filter Criteria
  3. Select a Print Option
  4. Select Display Options
  5. Generate the Report

Step 1. Select Affected Tables

The first step in generating the report should be to define which tables should be included in report results. Defining specific tables is important for narrowing the large amount of change data and focusing how you might filter this data based on filter criteria options.

Multiple tables can be selected by holding the CTRL key and selecting each table or by holding the SHIFT key and selecting a group of tables. 

You must select at least one table.

Image 3: Affected Tables

If a table appears within the Affected Tables window, this indicates the table contains at least one piece of data change information.

If no tables appear within the Affected Tables window, this often indicates the tool has either not been properly enabled/configured or more time is needed for data changes to occur within Campus before there is data to be reported. The report will also indicate this issue as a warning in the report header (see Image 4).

Image 4: Example of No Data Change Data Existing

Step 2. Enter Filter Criteria

Once affected tables have been selected, you should determine how data will be filtered for inclusion in the report. Because of the vast amount of data change information a school or district may have, this step is crucial in narrowing reported data into only information that is important or relevant. 

When populating multiple values within a single search field, the logical operator "OR" is applied.

When populating values across multiple search fields, the logical operator "AND" is applied.

Image 5: Filter Criteria

The following table explains each available filter option:

FieldDescription
Modification Type
  • Add - If marked, data changes which resulted in data being inserted into the affected table(s) selected is reported.
  • Change - If marked, data changes which resulted in data being updated in the affected table(s) selected is reported.
  • Delete - If marked, data changes which resulted in data being deleted in the affected table(s) selected is reported.
Change Method
  • Batch Queue - If selected, data changes made by Batch Queue functionality are reported.
  • Campus Learning - If selected, data changes made via Campus Learning functionality are reported.
  • DIS - If selected, data changes made via DIS are reported.
  • Quartz Job - If selected, data changes made by a Quartz job are reported.
  • Scheduled Task - If selected, data changes made by a scheduled task are reported.
  • User Interface (UI) - If selected, data changes made by a user while working within Campus are reported.
  • SQL/DB - If selected, data changes made by a user while working directly within the Campus database are reported. This also includes indirect methods that utilize SQL but are not directly related to a user's actions within the UI.
  • System Application - If selected, data changes made by the Campus application with no Campus user involvement or interaction is reported. For example, changes to configuration options in the Campus XML which resulted in a database preference change during startup would be captured as a System Application change.
  • API - If selected, data changes made by a third-party system connected to Campus via an API are reported. Generic changes (including internal) via API calls will also report as an API change method however, they may not indicate the tool used.
Modified Start DateOnly data changes occurring on or after this time and date are reported.
Modified End DateOnly data changes occurring on or before this time and date are reported.
Modified-by UsersAllows you to specify which Campus or database users will report change data. Only data changes made by these users will be included in the report.
AppTransactionGUIDA unique GUID generated for each data change event which occurs within Campus. This field is especially useful in viewing only changes which occurred for a specific event.
Change Tool

The tool(s) within Campus used to make the data change. Change tools are entered by specifying their tool code. Only changes made within the tools specified will be reported.

If you are unsure what a tool's tool code might be, hover the mouse cursor over the tool in the Campus Index.

Record Primary Key

Allows you to only view data for specific records which had data changes (i.e., courseID, enrollmentID, etc).

For example, if you want to only view changes for a specific enrollment and you know the enrollmentID, you can view just this information.

Column Name

Allows you to only view data changes made for a specific column within selected affected table(s).

The column name and field name in the application do not always match. You may need to match corresponding values in columns and fields in order to properly understand a column/field relationship.

Old Value

Allows you to only view data changes which modified or deleted columns with this original specific value. This is most useful when using in tandem with the New Value field to view data changes which changed from one value to another within a specific table or column.

When an entire new record is added or inserted, the old value for each column pertaining to the table will report as null. Likewise, when an entire record is deleted, the new values for each table column will be null.

Some areas of Campus delete records and insert new records instead of updating existing records.

New Value

 Allows you to only view data changes which modified or added this specific value within the affected table(s) specified. This is most useful when using in tandem with the Old Value field to view data changes which changed from one value to another within a specific table or column.

When an entire new record is added or inserted, the old value for each column pertaining to the table will report as null. Likewise, when an entire record is deleted, the new values for each table column will be null.

Some areas of Campus delete records and insert new records instead of updating existing records.

Affected Person

Allows you to report data for a specific person or set of people who were affected by changes made to selected tables.

Affected CalendarAllows you to report data for a specific calendar(s) affected by changes made to selected tables.
Affected SchoolAllows you to report data for a specific school(s) affected by changes made to selected tables.
Affected DistrictAllows you to report data for a specific district(s) affected by changes made to selected tables.
Rows Affected

Allows you to return changes that affected a certain number or threshold of records. 

SQL StatementAllows you to report changes made to records caused by a specific SQL statement.

 

Step 3. Select a Print Option

Once tables have been selected and filter criteria has been defined, a print option should be selected. The following options are available:

HTML

You can generate the report in HTML format. This format is useful for clearly and easily reviewing each individual change among iterative searches or large amounts of data. 

Image 6: HTML Print Option

Click image to enlarge

CSV

You can generate the report in CSV format. This format is useful for utilizing spreadsheet software to use advanced Excel functionality, customize print formats, extract the data into another system, etc.

Selecting the User the report was generated by and/or Date the report was generated checkboxes means this information is included in the name of the generated report file (see Image 7).

Image 7: CSV Print Option

Click image to enlarge

XML

You can generate the report in XML format. This format is useful for feeding this information into other programs or reporting services.

Selecting the User the report was generated by and/or Date the report was generated checkboxes means this information is included in the name of the generated report file (see Image 8).

Image 8: XML Print Option

Click image to enlarge
PDF

You can generate the report in PDF format. This format is useful for reviewing data and sharing this data with other users.

Selecting the User the report was generated by and/or Date the report was generated checkboxes means this information is included in the name of the generated report file (see Image 9).

Image 9: PDF Print Option

Click image to enlarge

Step 4. Select Display Options

Now that tables have been selected, filter criteria has been defined and a print option has been selected, display options should be defined.

Display options (Output, Seq, Sort, Direction) determine which reported fields (listed below Field) are included and how this data is sorted/displayed within the report. 

Image 10: Display Options

The table below explains each display option.

FieldDescription
Record output limitThis indicates the maximum amount of records that will be included within the report.
Old ValueThis allows you to decide whether to display all old columns and values or only those columns and values that were changed as a result of an update. 
OutputIf marked, this column will report data within the report.
Seq

This field determines the order in which these 13 fields are displayed within the report.

For example, if Modified Timestamp has a Seq of 1 and Modified Type has a Seq of 2, the first two columns in the report will be data for Modified Timestamp and then Modified Type.

Sort

This field determines the order in which data is sorted on the report.

For example, if Table Name has a Sort of 1 and Column Name has a Sort of 2, the report will first sort data by table name and then sort by column name.

Direction

This field determines how reported data in each field is sorted. This field is defaulted to a value of Ascending.

For example, if New Value has a Direction of Ascending, all reported data for New Value will be sorted in ascending order.

 

Step 5. Generate the Report

To generate the report, click the Generate Report button at the top or bottom of the report editor.

Image 11: Generating the Report

Understanding the Data Change Tracker Report

This section will help you better understand the purpose of each column on the report. 

There is an affected change record limit that prevents a single change from overloading the Data Change Tracker process job and database storage. Currently, this limit is set to 10,000 records from a single modification.

In these instances, no affected record details before or after values will be tracked.

If the change was a result of a SQL statement, then that statement will be logged along with the Database User and Timestamp.

Image 12: Example of the Data Change Tracker Report - CSV Format
ColumnDescription
appTransactionGUID

A unique GUID generated for each data change event which occurs within Campus.

Because one data change event can cause multiple changes within Campus, all changes caused by the event are given the same TransactionGUID. This helps users better understand the entire scope of changes which occurred with the event.

TimestampThe date and time in which the change event occurred.
Mod By Username

The user who initiated the data change within Campus or the database.

Change records created via a tool interacting with the Data Service Broker will always report the username configured by Campus Data Services and not the Campus user who was logged in and initiated the change record.

Mod by Last Name

The last name of the user who initiated the data change within Campus or the database.

Change records created via a tool interacting with the Data Service Broker will always report the name of the user configured by Campus Data Services and not the Campus user who was logged in and initiated the change record.

Mod by First Name

The first name of the user who initiated the data change within Campus or the database.

Change records created via a tool interacting with the Data Service Broker will always report the name of the user configured by Campus Data Services and not the Campus user who was logged in and initiated the change record.

Modification TypeThe type of modification which occurred (Add, Change, Delete).
Change Method

Indicates whether the data change was made within Campus (UI) or the database (DB).

The report defauls to a value of DB is no Change Method was explicitly set during the transaction.

Change Tool

Indicates what tool was used to make the data change.

This column will report blank if the change was made directly via the database.

Table NameThe name of the table which had data modified by the data change event.
Primary KeyThe unique identifier for the record that was modified, which corresponds to the table.
Old ValueThe column value prior to the data change event.
New ValueThe column value after the data change event.
Affected PersonThe specific person affected by the record change.
Affected Calendar NameThe name of the calendar affected by the record change.
Affected School NameThe name of the school affected by the record change.
Affected District NameThe name of the district affected by the record change.
Rows Affected

The number of change records created by the data change.

"-1" if the number of rows affected is at or exceeds the threshold which defaults at 10,000.

SQL Statement

The SQL statement used to change data and produce the change record.

This column will report blank unless the change was made directly within the database or the limit for the max number of affected records was reached for that table.

Feedback