Merging Attachments in the Database

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.

For edge encrypted attachments, ServiceNow will encrypt the attachment byte data when the attachment is in one piece and then split up the encrypted content into chunks for saving in the sys_attachment_doc table. As a result, this content is not usable since each sys_attachment_doc record is only one portion of the encrypted content.

By including a SysAttachmentHandler in the DataSync Agent configuration, the Agent will query the sys_attachment and sys_attachment_doc tables and merge the attachment records back together into one record. The records are removed from these two tables and the merged attachment records can then be stored in whole in the database (in a new table called attachments) or shared out as messages back to the Integration Mesh.

NOTE: Storing the attachments as complete records in the database will lead to large table rows. Ensure your database has the necessary storage space to save these large records.

When saving attachments into the database, an attachments table gets created to store the fully built attachments and has the following structure:

Column	SQL Data Type*	Description
sys_id	varchar(40)	sys_id of the sys_attachment record
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	Will have a value of 0 to represent a full attachment starting at the beginning (0) position
attachment	varchar(max)	Attachment's complete byte data. This column's data can be used to get the whole attachment as one viewable file.

*Data types shown are for MySQL/SQL Server. Other databases will create columns in their equivalent data types (for example, Oracle will create the position field as a number field data type, its equivalent of bigint).

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

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.

The Agent will need access to the keystore containing the encryption key used for Edge Encryption. This keystore can be stored in an Azure Key Vault cloud key management or stored locally on a filesystem the Agent has access to.

For Azure Key Vault, add the following:

Parameter	Description
keystore	Specifies where the keystore is located. Value: azure
vault_tenant	tenant_id for the Azure Key Vault containing the keystore*
vault_url	URL to the Azure Key Vault*
vault_principal	principal_id for the Azure Key Vault*
principal_secret	Password for the Azure Key Vault*
secret_name	Name of the keystore*
keystore_password	Password for the keystore^
keystore_alias	Name of the key alias^
alias_password	Password for the key^

*See Authentication in Azure Key Vault for more information on these configurations.

^See Edge Encryption properties for more information on these configurations.

NOTE: The Azure KeyVault uses its own HTTP client, which means Azure is making network connections separate from Perspectium messages and instance connections. Thus, to access Azure KeyVault through a proxy, add the following:

Parameter	Description
proxy	The URL of the proxy server to connect to. proxy="http://example.com/"
proxy_port	Port number this client will use. proxy_port=”8080”
proxy_user	The username to authenticate with the proxy server proxy_user=“proxyConfiguredUser”
proxy_password	The password to authenticate with the proxy server proxy_password=“proxyConfiguredUserPassword”

<config>
	<agent>
		<share>
			<task> 
				...
				<edge_encryption keystore="azure"
					proxy="http://example.com"
					proxy_port="8080"
                 	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>

For a keystore stored in the local file system, add the following:

Parameter	Description
keystore	Specifies where the keystore is located. Value: local
keystore_path	File path to the keystore^
keystore_password	Password for the keystore^
keystore_alias	Name of the key alias^
alias_password	Password for the key^

^See Edge Encryption properties for more information on these configurations.

<config>
	<agent>
		<share>
			<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>
		</share>
	</agent>
</config>

Yes, if the attachments are edge encrypted.

Otherwise, NOT required.

true

<database_*>

Since the Agent will need to connect to the database to read records from the sys_attachment and sys_attachment_doc tables to merge attachments, you will need to configure all <database_> directives. See Database configurations for more information.

Yes

<attachment_retries>

Specifies how many times an attachment will be tried to be built until it's skipped and removed from the sys_attachment table. If tag is not include, the default value is 3.

<config>
	<agent>
		<share>
			<task> 
				...
				<attachment_retries>3</attachment_retries>
			</task>
		</share>
	</agent>
</config>

No

3

If you are using <message_connection> so the merged attachments are shared to a queue in the Integration Mesh, messages will be created using the directives configured in the share task:

Directive	Description
message_connection	The <message_connection> is used to configure the Integration Mesh queue where messages will be shared to. This configuration is not needed if using <skip_queue/>.
encryption_key	A 24+ character key to encrypt the merged attachment's byte data. This is the same approach as entering an encryption key in the Perspectium ServiceNow app for encrypting data shared out of ServiceNow.
topic	The topic for each message shared. If no value specified, replicator will be used.
type	The type for each message shared. If no value specified, sql will be used.
key	The key for each message shared. If no value specified, the task's name will be used.
name	The action for each message shared to append to sys_attachment. For example, if the value entered is .insert, messages will have a name of sys_attachment.insert. If no value specified, the name field will be sys_attachment without an action name at the end.
value	<Encrypted Merged Attachment Byte Data> The data will be encrypted using the encryption key specified in the <encryption_key> directive in the share task

For example, a configuration like this

<config>
	<agent>
		<share>
			<task> 
				...
				<encryption_key>some_encryption_key_here</encryption_key>
				<message_connection user="USER" password="PASSWORD" queue="psp.out.replicator.dev1234">https://URL.perspectium.net</message_connection>
				<topic>replicator</topic>
            	<type>agent</type>
            	<key>dev1234</key>
            	<name>.insert</name>
			</task>
		</share>
	</agent>
</config>

Will create messages shared to the Integration Mesh that are as follows:

{
        "topic": "replicator",
        "type": "agent",
        "key": "dev1234",
        "name": "sys_attachment.insert",
        "value": "<Encrypted Merged Attachment Data>"   
}

Click here to see an example of a complete agent.xml configuration with edge encryption and merging attachments to share back to an Integration Mesh Queue...

<?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>
				</plugins>
        </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>replicator</topic>
            <type>agent</type>
            <key>dev1234</key>
            <name>.insert</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>

Optionally, you can encrypt the attachments using Voltage API. See Encryption using Voltage for more information on the plugin.

In the agent.xml, add the <voltage> directive within <share>:

<voltage url="https://example.com/api"
	trace_id="TRACE01"
	app_name="PAgent"
	host_name="psp"
	identity=""
	shared_secret="secret"
	encryption_type="3"
	encrypt_all="true">true</voltage>

Directive	Description	Value	Attributes
<voltage>	Encrypts the fully built attachments using Voltage.	true/false	Attribute	Description	Required?
			url	REST url endpoint	Yes
			trace_id	Identifier for each request made to the API	No
			app_name	Name of the application, i.e. Perspectium Agent	Yes
			host_name	Name of the machine running the application	Yes
			identity	Identifier for application using the API	No
			shared_secret	Secret phrase shared between clients using API	Yes
			encryption_type	Encryption method, i.e. AES = 3	Yes
			encrypt_all	If set to true, all attachments will be encrypted with Voltage. Otherwise only those previously encrypted by edge will be encrypted with Voltage	No

Click here to see an example of a complete 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>
				</plugins>
        </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>replicator</topic>
            <type>agent</type>
            <key>dev1234</key>
            <name>.insert</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>
			<voltage url="https://example.com/api"
				trace_id="TRACENUM"
				app_name="PAgent"
				host_name="psp"
				identity=""
				shared_secret="secret"
				encryption_type="3"
				encrypt_all="true">true
			</voltage>
        </task>
    </share>
</agent>
</config>

After configuring your agent.xml, start running your DataSync Agent again.

Content

Space Tools

Feature available in Helium 6.0.1 release

Prerequisites

Procedure