Feature available in Helium 6.0.1 release
Attachments are stored in ServiceNow in two separate tables, sys_attachment and sys_attachment_doc. The sys_attachment record holds information about the attachment (such as the file name and file size), while the sys_attachment_doc table contains the actual byte data of the attachment separated into different chunks so as to not have one record take up too much space in a table row. Thus, sending attachment(s) to your database with the DataSync Agent will store the attachment(s) in the two tables just as they are stored in ServiceNow.
However in this state, you're not able to view the attachments (i.e. if the attachment is a JPG, you cannot see the actual image itself) since the attachments are separated as indicated above. By including a SysAttachmentHandler in the DataSync Agent configuration, the attachment(s) will no longer be in the sys_attachment and sys_attachment_doc, but instead will be merged together and stored in a new table called attachments. This allows you to have the actual full complete attachments as if you downloaded them from your ServiceNow instance through the UI.
NOTE: Storing the attachments as complete records will lead to large table rows. Ensure your database has the space to save these large records.
Prerequisites
First, you will need to set up one of the Perspectium DataSync Agents.
You should also stop running your DataSync Agent before making any Agent configuration changes.
Procedure
To set up merging of attachments, follow these steps:
Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
Open your agent.xml file in a text editing application. Then, add the following directives under <share><task> :
| Directive | Description | Required? | Example Value | ||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| <handler> | Name of the Java handler class. In this case, the value for this directive will be com.perspectium.replicator.sql.subscriber.edge.SysAttachmentHandler. <config> <agent> <share> <task> ... <handler>com.perspectium.replicator.sql.subscriber.edge.SysAttachmentHandler</handler> </task> </share> </agent> </config> | Yes | com.perspectium.replicator.sql.subscriber.edge.SysAttachmentHandler | ||||||||||||||||||||||||||||||||
| <skip_queue/> | Including skip_queue will ensure that the attachments are not posted to the Integration Mesh but inserted into the attachments table | Yes, if message_connection is not included. Otherwise, NOT required. | |||||||||||||||||||||||||||||||||
| <edge_encryption> | Decrypts messages that have been published from a ServiceNow instance that is leveraging Edge Encryption. See Edge Encryption Support for more information. If the keystore containing the encryption key is saved in an Azure Key Vault, add the following:
<config>
<agent>
<share>
<task>
...
<edge_encryption keystore="azure"
vault_tenant="VAULT_TENANT_GOES_HERE"
vault_url="VAULT_URL_GOES_HERE"
vault_principal="VAULT_PRINCIPAL_GOES_HERE"
principal_secret="PRINCIPAL_SECRET_GOES_HERE"
secret_name="SECRET_NAME_GOES_HERE"
keystore_password="KEYSTORE_PASSWORD_GOES_HERE"
keystore_alias="KEYSTORE_ALIAS_GOES_HERE"
alias_password="ALIAS_PASSWORD_GOES_HERE">true</edge_encryption>
</task>
</share>
</agent>
</config>
If the keystore containing the encryption key is saved locally, add the following:
<config>
<agent>
<subscribe>
<task>
...
<edge_encryption keystore="local"
keystore_path="KEYSTORE_PATH_GOES_HERE"
keystore_password="KEYSTORE_PASSWORD_GOES_HERE"
keystore_alias="KEYSTORE_ALIAS_GOES_HERE">true</edge_encryption>
</task>
</subscribe>
</agent>
</config>
| Yes, if the attachments are edge encrypted. Otherwise, NOT required. | true |
Example of agent.xml:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<config>
<agent>
<max_reads_per_connect>1</max_reads_per_connect>
<polling_interval>5</polling_interval>
<test_mode/>
<subscribe>
<task>
<polling_interval>5</polling_interval>
<task_name>oracle_subscriber_automated_test</task_name>
<handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
<decryption_key>some_decryption_key_here</decryption_key>
<message_connection user="USER" password="PASSWORD" queue="psp.out.replicator.dev1234">https://URL.perspectium.net</message_connection>
<use_cache/>
<instance_connection user="USER" password="PASSWORD">https://dev1234.service-now.com</instance_connection>
<database_type>sqlserver</database_type>
<database_port>1234</database_port>
<database_server>SERVER_URL</database_server>
<database_user>USER</database_user>
<database_password>PASSWORD</database_password>
<database_parms>lockTimeout=15000;queryTimeout=15</database_parms>
<database>DATABASE_NAME</database>
<skip_columns_log_interval>200</skip_columns_log_interval>
<plugins>
<plugin keystore="azure"
vault_tenant="12345678-ab12-ab12-ab12-123456789ab"
vault_url="https://url.vault.azure.net/"
vault_principal="12345678-ab12-ab12-ab12-123456789ab"
principal_secret="3213156165-adasdasd_a1s5d6a5s1d6a"
secret_name="some_secret_name"
keystore_password="efg123"
keystore_alias="128bitkey"
alias_password="abc123">com.perspectium.replicator.sql.plugin.SQLSubscriberDecryptColumnPlugin</plugin>
</task>
</subscribe>
<share>
<task>
<task_name>attachment_processor</task_name>
<handler>com.perspectium.replicator.sql.subscriber.edge.SysAttachmentHandler</handler>
<encryption_key>some_encryption_key_here</encryption_key>
<message_connection user="USER" password="PASSWORD" queue="psp.out.replicator.dev5678">https://URL.perspectium.net</message_connection>
<polling_interval>60</polling_interval>
<max_writes_per_connect>1</max_writes_per_connect>
<skip_queue/>
<skip_report/>
<topic>topic_here</topic>
<type>type_here</type>
<key>key_here</key>
<name/>
<database_type>sqlserver</database_type>
<database_port>1234</database_port>
<database_server>SERVER_URL</database_server>
<database_user>USER</database_user>
<database_password>PASSWORD</database_password>
<database_parms>lockTimeout=15000;queryTimeout=15</database_parms>
<database>DATABASE_NAME</database>
<edge_encryption
keystore="azure"
vault_tenant="12345678-ab12-ab12-ab12-123456789ab"
vault_url="https://url.vault.azure.net/"
vault_principal="12345678-ab12-ab12-ab12-123456789ab"
principal_secret="3213156165-adasdasd_a1s5d6a5s1d6a"
secret_name="some_secret_name"
keystore_password="efg123"
keystore_alias="128bitkey"
alias_password="abc123">true</edge_encryption>
</task>
</share>
</agent>
</config>
After configuring your agent.xml, start running your DataSync Agent again.