Exporting asset attachments from SolarWinds Web Help Desk

We recently migrated from SolarWinds Web Help Desk (WHD) to a different system. One of the challenges we faced was exporting our assets. While WHD has some functionality that facilitates this, it does not export any corresponding attachments, which is a major shortcoming.

SolarWinds' REST API isn't much help either; while there is an endpoint for ticket attachments, there is no such endpoint for asset attachments.

So I dug into the MSSQL database that backs our instance, expecting to find a table with pointers to files on a filesystem somewhere. What I found instead was a table that holds the actual raw binary content of all uploaded attachments.

Yuck.

Fetching the data I wanted was straightforward once I connected all the dots:

SELECT
    a.ASSET_ID,
    a.SERIAL_NUMBER,
    m.MODEL_NAME,
    t.ASSET_TYPE,
    CAST('' as xml).value('xs:base64Binary(sql:column("ad.FILE_DATA"))', 'nvarchar(max)') as BASE_64_FILE_DATA,
    ad.ID as attachment_data_id,
    ga.FILE_NAME,
    a.ASSET_NUMBER
FROM
    whd.dbo.ASSET_TYPE t
        INNER JOIN whd.dbo.MODEL m
                   ON m.ASSET_TYPE_ID = t.ASSET_TYPE_ID
        INNER JOIN whd.dbo.ASSET a
                   ON a.MODEL_ID = m.MODEL_ID
        INNER JOIN whd.dbo.ASSET_GENERIC_ATTACHMENT aga
                   ON aga.ASSET_ID = a.ASSET_ID
        INNER JOIN whd.dbo.GENERIC_ATTACHMENT ga
                   ON ga.ID = aga.ID
        INNER JOIN whd.dbo.ATTACHMENT_DATA ad
                   ON ad.ID = ga.ATTACHMENT_DATA_ID
WHERE
        t.ASSET_TYPE_ID in (9,15,31,3,14)

The only tricky bit if you're using MSSQL is that you may need to bypass the Server Management Studio (SSMS) GUI. This is because versions prior to 18.2 will happily (and silently) truncate your data if your columns have payloads larger than 64 K (such as the binary blobs in the FILE_DATA column).

Once I figured out what was going on, I used the SQL Server Import and Export Wizard to export my SQL query results to a flat file, being sure to:

1. Base64 encode the binary data so it doesn't break the CSV export file (this is covered in the SQL query)
2. Unicode encode the export file (this must be set using a checkbox during the export process)

With your data now liberated, you can use the language of your choice to go through each line in the export file, Base64 decode the data, and save it to a file. 

For instance, a quick and dirty Python implementation:

from sys import maxsize
from csv import reader, field_size_limit
from base64 import b64decode
from os.path import join


def main():
    field_size_limit(maxsize)  # allows reader to handle massive blob columns
    with open('./asset_attachments.csv', encoding="utf-16") as csv_file:  # load the CSV export as UTF
        csv_reader = reader(csv_file)
        next(csv_reader, None)  # skip the header line
        for row in csv_reader:
            file_name = row[6]  # get the file name from the name column
            exported_file = open(join('./', file_name), "wb")  # create and open a new file using the name above
            exported_file.write(b64decode(row[4]))  # decode the base64 payload and write the bytes straight to the file above
            exported_file.close()

exit(main())

And that's all there is to it, the script above will look for the export you generated (asset_attachments.csv in my case) and save the blobs using their corresponding names.

Really, binary blobs?

Really, silently truncating data?

Yeesh.