This article present a detailed overview on how attachments are stored in underlying database table and how it can be extracted via Remedy AR System Server. We'll start with how to interact with attachments via those workflow functions and talk briefly about the specific ways AR System stores attachment data in the underlying database.
Sample workflow to extract attachment from Remedy
In a common use case – we have data in an attachment field on a “work info” (Work Log) type form in ITSM. For example, Change Management related attachment get stored in CHG:WorkLog. The form CHG:WorkLog has an attachment pool, with three attachment fields (see https://docs.bmc.com/docs/display/public/ars91/Attachment+pools for more information on how attachment pools and attachment fields work together), and we’d like to download an attachment from one of those fields (z2AF Work Log01) to a file on the server’s file system. We use a special workflow command to do this:
PERFORM-ACTION-SAVE-ATTACHMENT 1000000351 “C:\tmp\”$Infrastructure Change ID$”_”$z2AF Work Log01”
Let’s focus on each element and discuss them further.
PERFORM-ACTION-SAVE-ATTACHMENT is one of four “commands” that can be used to interact with attachments directly (https://docs.bmc.com/docs/display/public/ars91/Process+commands provides a list of these and other process commands that can be used and is a good reference).
The second element of the command is a field ID. This is the field ID of the Attachment Field itself that we’ll be working with. Be careful that you pick the right attachment field, and also not the field ID pool itself.
The third element is the path to where the file should be saved. This will generally need to be a location that’s accessible to the AR System Server. Remember that if you’re in a server group environment, or the AR System process runs as a non-root user, that may need to be taken into account when determining file location.
Note* For this demo, we choose to design an escalation as it is more convenient to test ( One can run it via Developer Studio). In addition, it executes on time/interval and doesn't really trigger SET API calls for any record on Workinfo/Worklog form. Unlike a filter which needs submit/modify/merge/delete operation to trigger. Service action is an exception though but not much useful for demo purpose.
Exported attachment file name will have change id prefix followed by _ and then file name ( this includes actual size, compressed size and full file name ). For example, if there are two separate CRQ which holds two different attachment ( CHG:Worklog form) in attachment field z2AFWork Log, then extracted attachment would look as per below screen.
To extract attachments from all other attachment field on Wroklog form, you may add two more run process commands and provide attachment id and attachment field name for second and third field respectively.
Note* Ensure that you have folder tmp already created in C:\ drive ( you can give any path. This is just an example). There is one issue though. If any of your attachment file names have any space then only first word will appear on file name ( for example if file name is "install notes.txt"then extracted attachment will have file name C:\tmp\CRQ101_7270Install ). It may sound little inconvenient however one can look into attachment table for file extension based on request id search which still appears as prefix on exported file name.
How attachments are stored in database table:
In below example, we will walk through two attachment [on two separate CRQ, created/saved via Change Request form] and how it is stored in database.
In database, we search for CHG:WorkLog schema id
The attachment detail table is named with B followed by main T table schema id. In current example, attachment data table will be B1394
Note that for every attachment field added to any attachment pool on the form, three columns are added. Each column is named with C, CO, or CC, followed by the attachment field ID. For example, in above attachment data table three columns added for one attachment are C1000000351, CO1000000351, and CC1000000351, where 1000000351 is the attachment field ID
Next, For each attachment field on a form, an attachment data table is created. The attachment data table is named with a B followed by the unique ID for the form, followed by C, followed by the attachment field ID. For example, the attachment data table might be called B1394C1000000351, where 1394 is the schemaID, and 1000000351 is the attachment field ID. the attachment data table is labeled BnC fID.
The Attachment data table has two columns: one that holds the Request ID (entryId ) and one that holds the data from the file. The column holding the data is named with a C followed by the attachment field ID. For example, the data column might be named C1000000351, where 1000000351 is the attachment field ID.
See The attachment tables for a form - BMC Remedy Action Request System 9.1 - BMC Documentation for more details on Attachment table in database.
Note* Extracting the data from API is easy and above mentioned database details is not meant fore extraction.
I hope this example in this blog was helpful to understand how to extract attachment data using workflow. To see more like this, see BMC Remedy Support Blogs.