The Table Compare feature allows you to compare a table on your ServiceNow instance with another ServiceNow instance so you can see the table's record counts on both instances as well as a list of record discrepancies by sys_id between the two (i.e. which records exist only on one instance and not the other).
Please contact firstname.lastname@example.org to request this feature as it requires installing a separate update set into your instances. This update set will need to be installed on both instances you plan to compare tables.
You will then be presented a list view of any previous table compares you have done. For example, the above screenshot table compares have already been done on the ticket and incident tables.
Click the “New” button next to PSP Table Compares to create a new entry for the table you want to compare.
The local instance refers to the instance that you are running the Table Compare feature on.
The target instance is the remote instance you are comparing records with.
Next fill in the following fields:
1) Table Name: The table you want to compare.
2) Target Instance URL: The URL of the remote (target) ServiceNow instance you to compare the table with.
3) Target Instance User: A user who has access to the instance specified in “Target Instance URL”. This user requires the admin role in the target instance in order to be able to properly access the table and its data.
4) Target Instance Password: The password for the target instance user.
5) Conditions (optional): Any filter conditions to use when comparing the table. This feature is similar to adding filter conditions on a table's list view in ServiceNow or in the Replicator Dynamic Share and Bulk Share options. For example, if you want to see how many records are in the table in both instances created in the last three months, you can add the filter condition here.
All other fields on this form are used by the Table Compare feature to display results when running its options. After you have filled in the above information, you can then select the options located in the “Related Links” section at the bottom (choosing any of the options will automatically save this table compare so you don't have to click “Submit” to save first).
Update Row Counts will run a query to see the number of records for this table in the local instance as well as the number of records in the target instance. After the query has completed, the Local Record Count and Target Record Count fields will be updated with the respective number of rows found in each instance for this table.
This option allows you to see if the tables match up in terms of the number of records. If they do not, you can use the Compare Rows option for more detail on the discrepancies.
Compare Rows will go through and see which sys_ids exist in the local instance but do not exist in the target instance and vice versa. Because some tables can be rather large, this feature will run in the background as a scheduled job and the “Compare Rows Status” field will show the status from when the job is “scheduled” (after clicking “Compare Rows” to start it) to when it is “running” in the background to “completed” when it has finished processing all the rows of the table both on the local instance and the target instance.
The “Compare Rows Started” and “Compare Rows Completed” fields will provide the date and time when the scheduled job starts and completes. You will need to reload this form in order to see changes in the Compare Rows fields with its results and as mentioned for large tables, it is recommend you check back later to give it to time to run the comparison (for example, a test of 150,000 records took about 25 minutes to complete).
For cases where there are discrepancies the Compare Rows Status field will show a status of “Completed with Discrepancies” and the records that are missing in either instance will be listed in the “PSP Row Discrepancies” table found at the bottom of the page. For those sys_ids that exist in the local instance and do not exist in the target instance, the sys_id will be listed with a state of “Missing in target”. The sys_ids that do not exist in the local instance but exist in the target instance will be listed with a state of “Missing locally”.
Once you have this list of sys_ids at the bottom, you can then research why these sys_ids are missing and handle appropriately, including manually exporting the XML of a missing record from one instance and importing the XML into another instance, running Replicator bulk share on the missing sys_ids, etc.
As of v3.2.7, in order to improve performance when the Table Compare feature is accessing the PSP Row Discrepancies table, a daily background job will run against the table to delete records that are older than a week and show as “Record Imported” as well as delete all records older than a month.
For sys ids “missing locally”, you can click into the PSP row discrepancy record where you will find a “Get Record XML from Target” option on the bottom left: Clicking this option will import the record's XML from the target instance into the “Record XML” field, allowing you to view the XML to verify this is a valid record you want to import into the instance. Note that once this option has been clicked and the XML has been imported into the Record XML field, the “Get Record XML from Target” option will then change to be “Create Record from XML”:
Clicking the “Create Record from XML” option will then create the record in the table being compared by importing the XML from the Record XML field into the instance. The “Disposition” field here will update with the appropriate status as to whether the record was successfully imported or if there was an error along with an error message if available.
If you prefer to have all records missing locally records imported into the table without reviewing the XML of each missing record, you can go back to the table compare page and select the “Import Records Missing Locally” option on the bottom left:
This will then schedule a job to run in the background to go through all records missing locally in the PSP row discrepancy table at the bottom and import each record's XML from the target instance into the local instance. The “Import Records Status” field will show the status from when the job is “scheduled” (after clicking the option to start it) to when it is “running” in the background to “completed” when it has finished processing all records missing locally. Like the Compare Rows feature, since there can be many records to process, this may take some time and you will need to reload this form in order to see changes.
For the cases where Compare Rows completes with a “Completed with Discrepancies” status and there are many records missing locally, having the target instance bulk share the records to this instance may be preferable to using the “Import Records Missing Locally” option. Bulk sharing will help to avoid connection limitations/issues that may occur with importing records due to ServiceNow or your network limiting the number of connections made to an instance.
Selecting this option will make a web service call to the target instance to create a bulk share that includes the list of sys ids that are missing locally. You can then run the bulk share on the target instance using the Share only Sys Ids listed option to bulk share only these missing locally records to this instance.
Note that this option will only create the bulk share on the target instance. You will have to login to the target instance to actually execute the bulk share. This is done so you can go to the target instance to verify and configure it as necessary (i.e. specify a target queue, decide if you want to share child class only, etc.).
Since the table compare feature will more than likely be used on tables you are subscribing to on this instance, there is a “Create Table Compare” option available on the Perspectium > Replicator > Subscribe list view with all your subscribe configurations.
This way you can quickly create table compare records for these tables without having to manually create them one by one.
1) Checkmark the subscribed tables you want to create table compares for and then select the “Create Table Compare” option:
3) After filling in this information and pressing “OK” you will then be sent to the Perspectium > Replicator > SNC Table Compares list view with your newly created table compares listed so you can then run them as desired.