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.
The attachments table gets created to store the fully built attachments has the following structure:
Column | Data Type | Description |
|---|---|---|
sys_id | varchar(40) | sys_id of the sys_attachment record If the attachment needs to be broken into chunks before storing into the database, this column will have the following format: <sys_id>_<position> |
table_name | varchar(128) | Name of the table that the file is attached to |
file_name | varchar(128) | Name of the file |
content_type | varchar(128) | Type of file |
table_sys_id | varchar(32) | sys_id of the record the file is attached to |
position | bigint | Order of the attachment chunk if the attachment is broken into chunks before inserting into the database |
attachment | varchar(max) | Attachment byte data |
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/> | By default merged attachment files will be published as messages into the Integration Mesh queue specified in the message_connection directive in this sharing task. To insert the merged attachment into the attachments table in the database instead, include the <skip_queue/> (no value required). If using <skip_queue/>, then <message_connection> is not required since records will not be published into a queue. | No If using <message_connection>, then <skip_queue/> is 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 |
If you are using <message_connection> rather than <skip_queue/>, the message built will look like the following:
{
"topic": "replicator",
"type": "agent",
"key": "dev1234",
"name": "sys_attachment.insert",
"value": "<encrypted attachment data>"
}
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.