BMC Export Attachments aka COVID-19 made my laziness better!

BMC Export Attachments aka COVID-19 made my laziness better!

You have probably experienced what’s happening in the whole world now and it’s better to stay at home. But what can we do at home? Yes, learn. In recent days I was asked to retrieve files from a database where BMC ARSystem Remedy is running, I said to myself a “How hard can it be?”, we already have an app using ARAPIlibraries, so yes, no problem!

Customers thought that their configuration could be a showstopper because of MSSQL 2000 and Remedy 7, but no way! This 20 years old grandpa system doesn’t support ARAPI, but we still have the SQL where data are stored. For sure, attachments are stored in binary so How hard can it be?

After google search for “How to decrypt BMC files in DB for beginners” with no relevant results this got my attention, so I’ve decided to develop my own solution.

What is the case?

  • SQL DB as a data source
  • Files are encrypted by BMC
  • Export files and save to disk or load into another DWH
  • Unknown size and count of stored attachments

What do we need?

  • JDK8
  • ARAPI71.jar or newer – this depends on the version, but BMC’s algorithm was not changed so much since 2003
  • JDBC driver for SQL Server
  • DB Server with stored DB
  • Knowledge of schema like fieldid. schemaid etc..

What is the plan?

  1. Connect and extract Files via JDBC from MSSQL
  2. Decrypt each file via the ARDecompressor method.
  3. Save files to the folder

Here we have a schema:

Simple schema of Export process from BMC's DB
Simple schema of Export process from BMC’s DB

Do you need this app? Contact me. 🙂