Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Anchor
Top of page
Top of page

Table Compare (found under DataSync > Tools) allows you to compare tables between two ServiceNow instances, or one ServiceNow instance and another integrated database. This is useful because you can see each table's record count, as well as a list of record discrepancies by sys_id between the two tables you're comparing—in other words, you can see which records exist in one table but not the other. 

Explore the various contexts and instructions for using this feature. 


Panel
titleWhat's on this page?

Table of Contents
maxLevel2
absoluteUrltrue



Info
titleSOME NOTES:
  • Table compare checks for the most recent update of the recordIf the most recent record is not found, the record will be re-shared.

  • The table compare messages have a 300s delay by default. We suggest that you configure this property for cases where the compare messages come before all shared messages are received, causing discrepancies. To modify the delay length, go to u_psp_properties.list in the filter navigator, search com.perspectium.table_compare.message_delay in the Name field.


Below are instructions for setting up table compare in different situations:




ServiceNow to ServiceNow table compare

There are two options when it comes to comparing tables between two instances of ServiceNow:

  • Audit Shares looks at the records that have been bulk shared and compare them to the records in another instance. This is useful for ensuring that bulk shares are captured in the subscribing instance properly. 
  • Compare Records by Table looks at records that satisfy specified conditions and compares them with the same records in another instance. This is useful if you have multiple dynamic/bulk shares on the same table and want to verify that the cumulative sharing of records is captured properly in the database.



↑ Go to top of page




Audit shares

The Audit Shares option compares the records shared by bulk shares with the records in another ServiceNow instance. This is useful for ensuring that bulk shares are captured in the database properly.

  1. Follow the steps to create a bulk share or go to a bulk share that you want to compare at Perspectium > Perspectium Core > Shares Bulk Share.

  2. On the bulk share page, in the Miscellaneous tab, check the Advanced box, which will reveal an Advanced tab (if not already there).

  3. In the Advanced tab, in the Runtime Settings section, check the Enable confirmation box. 

  4. Click Update, and the table compare will automatically occur once the the bulk share has been executed. Now that the bulk share is ready, you can go back into the Tools module.

  5. Go to Perspectium > Perspectium Core > Tools, and click Table Compare: ServiceNow to ServiceNow, and then Audit Shares.

  6. In the resulting list of all your bulk shares, select the ones that you want to use in the table compare. Make use of the Select All and Deselect All options to easily select or deselect many bulk shares at once. 

  7. Click Run Table Compare to start executing the table compare. Or, click Schedule Table Compare to have the table compare configuration run on a scheduled basis.


↑ Go to top of page




Compare records by table

The Compare Records by Table option compares the records that satisfy specified conditions with the same records in your database. This is useful if you have multiple dynamic/bulk shares on the same table and want to verify that the cumulative sharing of records is captured properly in the database. 

  1. Go to Perspectium > Perspectium Core > Tools > Table Compare: ServiceNow to ServiceNow > Compare Records by Table/ConditionOn the resulting page, you'll use the Condition Builder to build the conditions for the records that should exist in the subscribing database. 

  2. Select a table from the Choose Table dropdown.

  3. In the Re-share records with this bulk share's configuration field, select a bulk share that will mimic the configurations for re-sharing missing records. 

  4. Create a condition with the condition builder or leave it blank to select all records from the chosen table. If the bulk share selected contains conditions, the condition builder will be pre-filled with the same conditions. 

  5. (Optional!) Click Preview to see how many records will be compared.

  6. Click Add to add the table and condition to the active table compare configuration.

  7. Click Run Table Compare to start executing the table compare. Or, click Schedule Table Compare to have the table compare configuration run on a scheduled basis.


↑ Go to top of page




ServiceNow to database table compare

This module allows you to compare records shared via bulk or dynamic shares with tables in another database. With bulk shares, a list of sys_ids will be sent from ServiceNow to the DataSync Agent for the Agent to check if the records with the corresponding sys_ids exist. If there are discrepancies, a message will be sent back to ServiceNow and the missing records will be re-shared to the Agent. This process will repeat until all records have made it to the target database.

Table Compare can also compare deleted records that dynamic shares share out to delete in the database. The Agent will check to make sure that the sys ids in the list do not exist in the database. The records that should have been removed but was not previously removed will be deleted immediately.

Here are the supported databases for table compare:

Database

Version*

SQL Server (MSSQL)2017, 2019
MySQL5.X.X, 8.X.X
Oracle11g - 12c (12.2.0.1.0), 19c
PostgreSQL13
SAP Hana2.0

*These versions of the associated databases have been tested at this time but in general these databases will work with Table Compare.  Testing is ongoing to support more databases.


There are three options when it comes to comparing tables between a ServiceNow instance and another database:

  • Audit Shares looks at the records that have been bulk shared and compare them to the records in the database. This is useful for ensuring that bulk shares are captured in the subscribing instance properly. 
  • Compare Records by Table looks at records that satisfy specified conditions and compares them with the same records in the database. This is useful if you have multiple dynamic/bulk shares on the same table and want to verify that the cumulative sharing of records is captured properly in the database.
  • Synchronize Delete Records looks at records that no longer exist in your instance and remove them from your database.


Info
titleSOME NOTES:
  • Records that no longer exist in ServiceNow from the initial list will be disregarded in subsequent comparisons.
  • Comparing records with Fanout exchange enabled is currently not supported. 
  • Records will be compared by sys_id and sys_mod_count to verify they exist (content is not compared to match or to compare if the table has the latest record.
  • Database views are NOT supported with table compare.



↑ Go to top of page

Audit shares

The Audit Shares option compares the records bulk shared by a ServiceNow instance with the records in your database. This is useful for ensuring that bulk shares are captured in the database properly.

  1. Follow the steps to create a bulk share or go to a bulk share that you want to compare at Perspectium > Perspectium Core > Shares Bulk Share.

  2. On the bulk share page, in the Miscellaneous tab, check the Advanced box, which will reveal an Advanced tab (if not already there).

  3. In the Advanced tab, in the Runtime Settings section, check the Enable confirmation box. 

  4. Click Update, and the table compare will automatically occur once the the bulk share has been executed. Now that the bulk share is ready, you can go back into the Tools module.

  5. Go to Perspectium > Perspectium Core > Tools, and click Table Compare: ServiceNow to Database, and then Audit Shares.

  6. In the resulting list of all your bulk shares, select the ones that you want to use in the table compare. Make use of the Select All and Deselect All options to easily select or deselect many bulk shares at once. 

  7. Click Run Table Compare to start executing the table compare. Or, click Schedule Table Compare to have the table compare configuration run on a scheduled basis.


↑ Go to top of page




Compare records by table

The Compare Records by Table option compares the records that satisfy specified conditions with the same records in your database. This is useful if you have multiple dynamic/bulk shares on the same table and want to verify that the cumulative sharing of records is captured properly in the database. 

  1. Go to Perspectium > Perspectium Core > Tools > Table Compare: ServiceNow to Database > Compare Records by Table/ConditionOn the resulting page, you'll use the Condition Builder to build the conditions for the records that should exist in the subscribing database. 

  2. Select a table from the Choose Table dropdown.

  3. Use the Comparing Setup dropdown to select the records that you want to compare. This is where you can specify whether you want to compare the base table records only, or include the child records of that table as well. You can find information on child tables and the general table hierarchy here.

  4. In the Re-share records with this bulk share's configuration field, select a bulk share that will mimic the configurations for re-sharing missing records. 

  5. Create a condition with the condition builder or leave it blank to select all records from the chosen table. If the bulk share selected contains conditions, the condition builder will be pre-filled with the same conditions. 

  6. (Optional!) Click Preview to see how many records will be compared.

  7. Click Add to add the table and condition to the active table compare configuration.

  8. Click Run Table Compare to start executing the table compare. Or, click Schedule Table Compare to have the table compare configuration run on a scheduled basis.


↑ Go to top of page




Synchronize delete records

Enabling this feature will send a message to your agent database to start comparing which records were deleted. Once the deleted records are detected, a series of delete messages will be sent. 

Here's how:

  1. Go to Perspectium > Perspectium Core > Tools > Table Compare: ServiceNow to Database > Synchronize Delete Records

  2. On the resulting page, use the Choose Table dropdown to select the table that you want to use.

  3. Use the Bulk Share field (and the magnifying glass) to select the bulk share that your Synchronize Delete can mimic for configurations. 

  4. Click Add.

  5. Click Run Table Compare to start executing the table compare. Or, click Schedule Table Compare to have the table compare configuration run on a scheduled basis.


UI Text Box
typetip

If the agent starts seeing these errors "java.lang.OutOfMemoryError: Java heap space" then the wrapper.conf file can be edited to increase the max heap size.

See What if I'm seeing the following error: java.lang.OutOfMemoryError: Java heap space?


↑ Go to top of page




View and report table compare results

After running a ServiceNow to Database table compare, you can view your results, including the total records compared, records not found in the target database that were shared by the ServiceNow instance or the records not deleted in the target database that were shared out as deleted. A quick summary of previous table compares that have run will also be available with the table provided in the Previous Table Compare Executions section. 

Additionally, you can run a report with your table compare results. 

Here's how to view your results:

  1. Go to Perspectium > Perspectium Core > Tools. Click Table Compare: ServiceNow to Database.

  2. Then, click the table compare that you want to view (either Audit SharesCompare Records by Table, or Synchronize Deleted Records).

  3. Scroll to the Previous Table Compare Executions table. This table will provide you with a quick summary of previous tables compares that have run. Click the timestamp in one of the entries to view more information about that specific table compare execution such as the bulk shares or dynamic shares involved, the table and conditions use, the time frame, and the individual discrepancies in the target database. 

  4. (Optional!) If you want to export the results as a .csv or .pdf file, click the available buttons at the bottom of the table. Want to run your own reporting? You can do so by going to the Table Compares (u_psp_table_compares) table. Then, click into the record you want to view.


↑ Go to top of page




Performance Statistics 

To give you an idea of the table compare performance, tests were ran on a ServiceNow instance with two nodes, bulk sharing incidents with 4 MultiOutput processors jobs and records that averaged 5 KB in size. The total time for table compare to complete from ServiceNow to a database was as follows: 

Number of incident recordsTotal Duration
500,000 1:13:16
750,0001:57:12





...