Service Desk Practitioners Forum
cancel

Attachment FTP file path

SOLVED
Go to solution
Highlighted
James Townson
Super Contributor.

Attachment FTP file path

Hi All,

 

In the HPOV SD4.5 database - where can i get the file path of an attachment?  I am migrating them to another system.  The folder structure of the attachments are:

 

Attachmentroot\Servicecall\000\000\000\000\000\000\010\000\c31\100\fa\0
0000000-0000-0000-0001-0000c6280140

 

Thanks

JT

2 REPLIES
DrFoul
Honored Contributor.
Solution

Re: Attachment FTP file path

Hi James,

 

The file path you quote is the "new" storage structure, used from sp16 onwards.

 

If you extract any servicepack distribution SDSK_#####.EXE archive from sp16 to now (latest sp39) in a temp folder, then you can find a doc subfolder with extra documentation for selected features and enhancements, such as this:

...\doc\itsm007474\itsm007474.htm

 

Depending on what you want to find in the FTP folder tree or in the rep_attachments table in the database, you can find corresponding records in the DB and relate them to specific files in the FTP tree.

 

Here's what I can tell you about rep_attachments's columns:

 

AHS_OID is unique (primary key), but it’s not consecutive.

 

AHS_ENT_OID is a reference to entity, for example

563019801 = the servicecall "object" > see table ifc_entities columns ent_oid and ent_name.

633318790267031 = the Email Servicecall "object" (Yes, SD can be used to construct emails, complete with attachments)

Other entity/object numbers for Workorders, Changes, Problem, etc. can be found with this query:

column ent_oid format 99999999999999999999

column ent_name format a30

select distinct ifc_entities.ent_oid, ifc_entities.ent_name from ifc_entities, rep_attachments where ahs_ent_oid=ent_oid;

 

AHS_BASENAME is the real name of the attachment, rather than the weird 0000000-0000-0000-0004-blablabla.

The rep_attachment table is the only place where this filename is stored, so this is what users see in the GUI.

 

AHS_ATT_OID is the reference to a specific [Servicecall | Email Sc | Historyline | Change | Problem| …] OID record which has (an) attachment(s).

It’s not unique, because Servicecall 1234 can have multiple attachments

Servicecall entity OID = 563019801 = AHS_ENT_OID = IFC_ENTITIES.ENT_OID

Servicecall ID = 1234 = ITSM_SERVICECALLS.SER_ID

Servicecall OID = 281486025949185 = AHS_ATT_OID = ITSM_SERVICECALLS.SER_OID <= primary key

This column holds the FTP file path, convert to HEX.

 

AHS_FILENAME used to store the drive:path/file on the FTP server, but not anymore. These days it records a tmp file location where attachments are received in emails.


AHS_LOCKSEQ is just to keep track of people opening it and modifying it, but as attachment records are only uploaded or deleted and not modified, it has value 1.

 

AHS_TEM_OID > empty

 

AHS_VERSION = 1 or 2. I even have a 3 in my DB, but there’s no logic behind it.

Use 1 for Servicecalls, Changes, Problems, and 2 for Email Servicecall.

But this field is not important.

 

 

Summary:

The actual filename on the ftp site is found using the formula

\Attachment-root\Entity name\AHS_ATT_OID (hex)\AHS_OID (hex)

 

The key is to understand that SD uses UUIDs, which are nothing more than a hexadecimal representation with 0's padded on the left of the same decimal values that we know as OIDs.

 

HPE Software Support

If you find that this or any post resolves your issue, please mark it as an accepted solution. Kudos are welcome.
James Townson
Super Contributor.

Re: Attachment FTP file path

Thanks that worked a treat!!

 

If anyone is interested this is how i did it

 

I used the following function to convert value to HEX -

 

http://dpatrickcaldwell.blogspot.co.uk/2009/05/converting-decimal-to-hexadecimal-with.html

 

'\\SDSERVER\C$\Inetpub\ftproot\sd_attachments\ConfigurationItem\000\000\000\000\000\000\010' +  '\' + SUBSTRING ((dbo.ConvertToBase(AHS_ATT_OID, 16)), 3 , 3 ) + '\' + SUBSTRING ((dbo.ConvertToBase(AHS_ATT_OID, 16)), 6 , 3 ) + '\' + SUBSTRING ((dbo.ConvertToBase(AHS_ATT_OID, 16)), 9 , 3 )  + '\' + SUBSTRING ((dbo.ConvertToBase(AHS_ATT_OID, 16)), 12 , 2 ) + '\' + '00000000-0000-0000-000' + SUBSTRING ((dbo.ConvertToBase(AHS_OID, 16)), 1 , 1 ) + '-' + SUBSTRING ((dbo.ConvertToBase(AHS_OID, 16)), 2 , 12 )  [FullPath]