--- title: "Table Compare" canonical: "https://docs.perspectium.com/space/Lithium/2326810/Table%20Compare" format: markdown --- 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.  NOTES: If you are using the Schedule Table Compare option for ServiceNow to Database Table Compare Audit Shares or Compare Records by Table and are upgrading from a version prior to Krypton 8.4.2 to Lithium 9.2.0 and newer, you will want to delete the Table Rotation record for u_psp_table_compares . This will ensure your scheduled table compares are not rotated as in Lithium 9.2.0 we have enhanced how we delete previous table compare executions. Table compare checks for the  most recent update of the record . If 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. When executing a table compare, the .ack messages that are sent in by the DataSync Agent or a meshlet will first be saved to the u_psp_data table and linked to the Table Compare Execution record (u_psp_table_compares) so as to avoid race conditions with multiple .ack messages coming in and updating the same Table Compare Execution record at the same time. The Perspectium Table Compare Monitor scheduled job (default set to run every 15 minutes) will go through to evaluate these ack messages in u_psp_data to properly update the Received Count field in the Table Compare Execution record. Because this scheduled job by default runs every 15 minutes, the refreshing to validate all records are received by the Table Compare Execution may be slightly delayed. 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. 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 . On the bulk share page, switch to the Standard view. In the  Advanced  tab, in the Runtime Settings  section, check the  Enable confirmation  box.  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. Go to  Perspectium  >  Perspectium Core  >  Tools , and click Table Compare: ServiceNow to ServiceNow , and then   Audit Shares . 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.  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.  Go to  Perspectium >  Perspectium Core  > Tools  >  Table Compare: ServiceNow to ServiceNow  >  Compare Records by Table .  On the resulting page, select a table from the Choose Table  dropdown. 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 . In the  Bulk share field, select a bulk share that will mimic the configurations for re-sharing missing records.  (Optional) 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.  Click  Add   to add the table and condition to the active table compare configuration. 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 MySQL 5.X.X, 8.X.X Oracle 11g - 12c (12.2.0.1.0), 19c PostgreSQL 13 SAP Hana 2.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. If using a table map with your bulk share, you will need to do the following for reshared records (records the Table Compare feature finds as missing and is resharing to the Agent to update in the database) to work properly: The sys_id column must be a field that exists in your table map The  Generate schema  option must be selected on your table map In your DataSync Agent's databases.xml configuration file located in the Agent's conf , folder you must update the for your database so it only queries for sys_id . For example, if your target database is MySQL, under the MySQL section, you would change SELECT sys_id FROM %s WHERE sys_id = "%s" AND %s = "%s" to SELECT sys_id FROM %s WHERE sys_id = "%s" If you do not have a databases.xml configuration file (and this configuration file is only needed if you have advanced Agent configurations like the above), you can acquire it from  Perspectium Support . Once you have the file and made the above change, move databases.xml  into the Agent's  conf  folder i.e. /conf 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. 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. 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 . On the bulk share page, switch to the Standard view .  In the  Advanced  tab, in the Runtime Settings  section, check the  Enable confirmation  box.  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. Go to  Perspectium  >  Perspectium Core  >  Tools , and click Table Compare: ServiceNow to Database , and then  Audit Shares . 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.  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. When choosing the Schedule Table Compare option, a scheduled job will be created with the name Perspectium Scheduled Table Compare and you can go to Perspectium > Control and Configuration > All Scheduled Jobs to view and modify this job. Each time you select the Schedule Table Compare option, it will create a new scheduled job with the Perspectium Scheduled Table Compare name. ↑ Go to top of page Compare records by table If using Table Maps or Conditional Shares , the  Comparing Setup will be set to  Compare the table records only as otherwise you may end up with duplicate records being compared. 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.  Go to  Perspectium >  Perspectium Core  > Tools  >  Table Compare: ServiceNow to Database  >  Compare Records by Table .  On the resulting page, select a table from the Choose Table  dropdown. 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 . In the  Bulk share field, select a bulk share that will mimic the configurations for re-sharing missing records.  (Optional) 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.  Click  Add  to add the table and condition to the active table compare configuration. (Optional) A table will appear for you to edit or remove the table you just added. In this table, check the  Sync Deletes checkbox to remove records that no longer exist in your instance from your agent database.   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. When choosing the Schedule Table Compare option, a scheduled job will be created with the name Perspectium Scheduled Table Compare and you can go to Perspectium > Control and Configuration > All Scheduled Jobs to view and modify this job. Each time you select the Schedule Table Compare option, it will create a new scheduled job with the Perspectium Scheduled Table Compare name.  NOTE:  Table compare results will be shown in the Previous Table Compare Executions  table at the bottom. You can filter the results displayed in this table by the target type (Agent, Meshlet, ServiceNow, etc.). However, the target type isn't populated until the target responds back to ServiceNow with results. Prior to the results returning, the table compare result will show as Pending Response  to indicate we're pending a response from the target and you can filter the results for those  Pending Response as well. Further details on Previous Table Compare Executions can be found in the  View and report table compare results  section below. ↑ Go to top of page Synchronize deleted records In Lithium 9.7.0 and newer , the conditions and the Include attachments, Include journal fields and Include audit log options will be honored to query for related records in the target database. Lithium 9.5.0 and newer versions of the DataSync Agent or Snowflake meshlet are required to support the conditions and related (sys_attachment, sys_attachment_doc, sys_audit and sys_journal_field) 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: Go to  Perspectium >  Perspectium Core  > Tools  >  Table Compare: ServiceNow to Database  >  Synchronize Deleted Records .  On the resulting page, use the  Choose Table  dropdown to select the table that you want to use. Use the  Bulk Share  field (and the magnifying glass) to select the bulk share that your Synchronize Delete can mimic for configurations.   NOTE: In Lithium 9.7.0 and newer , the conditions and the Include attachments, Include journal fields and Include audit log options will be honored to query for related records in the target database. Because the bulk share is used to mimic for configurations as mentioned above (which includes the main table to query for along with which target queue), we use these specific options to query for records in the target database that match the specific conditions along with related attachments (sys_attachment and sys_attachment_doc), journal fields (sys_journal_field) and audit log (sys_audit) records. For example, you can use this to make sure all incidents with a priority of 1 (Critical) are the same between ServiceNow and the database along with those incidents' related attachments, journal fields and audit logs, deleting any records from the database that exist there but don’t exist in ServiceNow any longer. A message is indicated about this in Lithium 9.7.0 and newer: If you do not want to use any of these configurations, you can create a bulk share without these configurations and select it instead. The bulk share does not need to be executed to be selected here. Click  Add . Click  Run Table Compare  to start executing the table compare.   NOTE: Since the deleted records will not be able to be queried once they've been deleted from the database, the Previous Table Compare Executions  table for deleted records will show a state of Completed once the Perspectium application in ServiceNow has received the list of records in the database back from the Agent/Meshlet and processed this comparison and sent out any delete messages.  ↑ 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: Go to  Perspectium > Perspectium Core  > Tools . Click  Table Compare: ServiceNow to Database . Then, click the table compare that you want to view (either  Audit Shares ,  Compare Records by Table , or  Synchronize Deleted Records ). Scroll to the  Previous Table Compare Executions  table. This table will provide you with a quick summary of previous table compare executions. 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.  When looking at the results, numbers (such as the  Records Reshared to Target to indicate discrepancies between ServiceNow and the database that had to be reshared) will have a background color to indicate as follows:   A Status of Reshared and a Target Discrepancy Type of Not Found indicates the record was not found in the target database and has been reshared. Once the Agent has processed both the reshared message and the compare message to validate the record is in the database, this will update in here to Synced in Database . Note, for each time a record is not found and reshared (even if the same record has to be reshared multiple times because it can't process successfully), a new row will be created in the results with a Status of Reshared and a Target Discrepancy Type of Not Found. (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. The table the compare results are for along with the ability to delete the table compare results record (deleting it from the u_psp_table_compares table) was added. Note the table etnry will show as blank if it's not able to find the table the table compare was configured against, such as when the bulk share related to it is deleted.  NOTE:  As of Lithium 9.2.0 and newer , previous table compare executions that were run more than 60 days ago will be deleted to improve performance and minimize size on the u_psp_table_compares table. If you are upgrading to Lithium 9.2.0+ from a version prior to Lithium 9.2.0, you will have a Table Rotation record for u_psp_table_compares from previous versions as this record is no longer included in Lithium 9.2.0+. You can delete this record as it is no longer needed with this enhancement. ↑ 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 records Total Duration 500,000  1:13:16 750,000 1:57:12 ↑ Go to top of page Click to explore the rest of the features available in the Tools module.