To compile timestamped "snapshots" of your ServiceNow data, you can configure your DataSync Agent with the <temporal> directive. <temporal> will allow you to indicate times when your record's data is/was valid from and when the data is/was valid to.
NOTE: To set up a temporal data, the table you are syncing data to must not contain any records (i.e., Temporal data cannot be captured for tables that are already being synced to a database with a DataSync Agent).
WARNING! If you have already configured an integration with a DataSync Agent and have been saving records in a database, but you would now like to enable a temporal database, it is recommended to save temporal data into a new database by updating the value within the agent.xml's <database> directive. You can update this directive as well if you have been saving temporal data into a database, but you would now like to disable temporal data replication.
Prerequisites
First, you will need to install a DataSync Agent and create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.
Procedure
To set up a temporal data in a local database, follow these steps:
Access your agent.xml configuration file
Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
Add the <temporal> directive
Open your agent.xml file in a text editing application. To use the default configurations for syncing temporal data (columns will be named psp_valid_from and psp_valid_to and the maximum psp_valid_to value will be 9999-01-01 01:01:01), add the <temporal/> self-closing tag anywhere within the <task> tag. An example of this agent.xml is shown below. Otherwise, to create custom configurations for your temporal database, see Step #3.
Example agent.xml for default temporal data configurations:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share/>
<subscribe>
<task instances="4">
<task_name>test_subscribe</task_name>
<message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.test" use_basic_consume="true" user="admin">amqps://test.perspectium.net</message_connection>
<instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="admin">https://mycompany.service-now.com</instance_connection>
<handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
<decryption_key>This is my decryption key for testing</decryption_key>
<database_type>mysql</database_type>
<database_server>localhost</database_server>
<database_port>3306</database_port>
<database_user>testuser</database_user>
<database_password>testpassword</database_password>
<database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms>
<database_max_column_size>251</database_max_column_size>
<database>psp_repl</database>
<temporal>
<columns>
<column column_type="93" column_size="32" type="from">psp_from</column>
<column column_type="93" column_size="32" type="to">psp_to</column>
</columns>
<max_datetime>2030-12-31 00:00:00</max_datetime>
</temporal>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>5</polling_interval>
<skip_message_set_processing/>
</agent>
</config>
Create custom configurations (optional)
To customize your temporal database table's maximum date & time stamp in the psp_valid_to column, add the directive shown below within the <temporal> tag, replacing the red text with any value you would like to assign for custom configuration.
<max_datetime>2030-12-31 00:00:00</max_datetime>
NOTE: If records already exist in your temporal database table and then the <maxdatetime> value is changed, any records that were previously timestamped in the psp_valid_to column will not be updated to reflect the newly entered <max_datetime> value.
You can customize the column names for the psp_valid_from and psp_valid_to columns by modifying the values within the <column type="from"> and <column type="to"> directives, respectively.
Save your agent.xml file
Save the changes you made to your agent.xml file and close the file.
Confirm temporal data sharing
Log into the database that your DataSync Agent is sharing data to, and confirm that psp_valid_from and psp_valid_to timestamps are being created and that the psp_valid_to timestamp is updated whenever a change is made to the same record.
NOTE: By default, the primary keys for the subscribed table your database will be a composite key comprised of the sys_id value, from column value, and to column value.
