Orphaned Attachments Cleanup Tool

The following document contains the steps to install, configure and run the Smart Integration Server Orphaned Attachments Cleanup Tool or oactool, a command line utility to diagnose missing attachment files, missing attachments and empty attachment files in Smart Suite and the associated Attachment Server.
Its main purpose is to detect and remove orphaned attachments, so the Attachment Server does not contain files that are no longer linked to an actual item in Smart Suite.
Attachment metadata and file properties are recorded in a SQLite database, and a report is generated.
The report is timestamped and can be used for verification.
The utility will not remove files unless instructed to do so using the –remove-orphans command line parameter.
The –keep-database command line parameter instructs the utility to keep the database instead of recreating it, which might save a lot of time.

Prerequisites.

The oactool uses ODBC to connect to the Smart Suite database.
You need to follow the instructions in chapters 2 paragraph 1, chapter 3 and chapter 4 at http://www.prolin.com/dokuwiki/smartsuite_integration to install ODBC drivers and prerequisites for your specific platform.
During installation you will need to provide database credentials to access the Smart Suite database. The account requires read access to retrieve settings and attachment details.

Installation.

The oactool distribution is available for download at http://products.prolin.com as a customer download upon request. Send an email to support@prolin.com if you want to be able to download the utility.

Navigate to where you have downloaded the oactool distribution.
The name of the zip file contains the build date and the version number.

Extract the contents to C:\Program Files\PROLIN\Integration Server\.

You should find this creates a sub-directory called “oactool” with the oactool executable, libraries and files.

Open the sub-directory, and locate the oactool.py.config.dist file and create a copy.

Open the context menu on the copy of the file, and select Rename.
The new name should be oactool.py.config.

You should now have a oactool.py.config. The file is read by oactool.exe on startup. It contains commented out default configuration values.

Open the file and check that the values are appropriate.
The “_/_” separators are replaced at runtime by the platform dependent separator, and the un-comment the lines that were changed, or the modifications are not picked up.
Make sure you have privileges to create directories and files in the selected location.

Open a command line in the oactool subdirectory, or navigate to the pertaining directory.
Verify the version using the –version command line parameter.
This should be identical to the zip file distribution version.
When the default directories or the directories listed in the configuration file do not exist, you will receive an error message, as they will not be created without consent.
Use the –create-directories command line parameter to instruct the utility to create the directories.
After the directories are created, the utility will ask for database related configuration settings. The credentials are stored encrypted.

After completing this step, one can use the –test-connection parameter to verify the connection to the database.
If all is configured and running correctly, it should report success and show the service desk system details and the startup notification as shown below.

If an error is reported it should contain an indication what is wrong.
One of the reasons might be an incorrectly spelled or missing DSN.

You need to have a 64-bit ODBC driver installed for either Oracle or SQL Server and the required Visual C++ Redistributable files.
For more details see the Prerequisites paragraph at the top.
In the example we have used the following files for SQL Server.

To configure a System DSN that the oactool or another SIS utility can use, you need the 64-bit ODBC Administration utility.
Steps below were captured for SQL Server. Steps for Oracle are similar, albeit using a different driver.

Open the System DSN tab.

Click Add.

Select the ODBC Driver 17

Fill in the DSN name, description and the hostname of the server the SQL Server instance is located on.

Select the “With SQL Server authentication…” option and fill in the Login ID and Password of the login account for the Smart Suite database. The credentials are used at the end when testing the connection.

Click Next, and accept the default values in the following two forms.

A summary is shown.

Click the Test Data Source… button.

Click OK several times to confirm the settings and exit the ODBC Administration utility.
If earlier connection tests failed, repeat the oactool invocation with the -test-connection command line parameter.

Use the –help command line parameter to show the built-in usage and short explanation of the command line parameters.

You do not necessarily need to use any command line parameters on the first pass.
The example below shows the main flow.
It connects to Smart Suite database using the configured credentials and retrieves attachment details.
It then connects to the Attachment server and recursively visits all the directories below the starting directory or FTPROOT as configured in the Smart Suite Attachment settings.
It then compares the attachment metadata and the file properties and produces a timestamped report.
Notice that the second invocation with the –keep-database skips retrieving metadata.

The report has three sections.
– Attachments in the application database (REP_ATTACHMENTS) without a corresponding file on the Attachment Server.
– Files on the Attachment Server without an attachment record in the application database (the orphaned attachments).
– empty attachments – an attachment record in the application database and an existing file on the Attachment Server with file size 0.

When the result are as expected, one can run the oactool again, with the –keep-database and –remove-orphans command line parameters.

A more interesting example taken from another server, first run.

Second run, removing files from the attachment server.

The report has a fourth section, detailing the removal process.

Final remarks.

While the has been tested with an industry standard FTP server, we would still recommend testing locally.

With the two-phase approach one can run the utility against a production instance to see the outcome before actually deleting any files.

Installation of the oactool program on a Windows server already running Power Server is possible and would likely mean that connectivity to the application database server and the attachment server will not be an issue.

When properly configured the oactool can be run as a Windows Scheduled Task. The timestamped report from phase 1 can be retrieved and examined, before manually running phase 2.

For questions and remarks please write to support@prolin.com