Service Desk Practitioners Forum
cancel

How to clear unused attachments stored in FTP folder?

Highlighted
Milan Karban
Respected Contributor.

How to clear unused attachments stored in FTP folder?

Hi,

we have many attachments in the FTP server that are not already related to the existing records in SD database (when the attachment is deleted in ServiceCall or other SD record the relation only is removed and the physical file on FTP server is still there and occupy the disk space). Exists any way how to clear such attachments from the FTP attachment folder?

I know how to clear relations to the nonexisting attachments in ServiceCall or other records in Attachments configuration in System panel, but this is inverse situation - we have attachment but no relation to the record.

Any idea? Thanks.
6 REPLIES
Mihai Tita
Outstanding Contributor.

Re: How to clear unused attachments stored in FTP folder?

Hi,

I think that you need to run some scripts on your db server to find out the attachments and their references and delete the folders and files on the server that had not references.
For Service Call you can use the script presented at http://forums1.itrc.hp.com/service/forums/bizsupport/questionanswer.do?threadId=635179. In that example, ser_oid = folder name, and ahs_oid = file name in Servicecall folder on your server.

Mihai Tita
Outstanding Contributor.

Re: How to clear unused attachments stored in FTP folder?

Hi,

You need to find out all files list existing on your ftp server, for every folder, such as Change, Problem, Servicecall, etc.
By using some scripts on your DB server, you can obtain another list that contains the records that have attachments and the corresponding file and folder names.
From the two lists you can get the files that you have to manually or automated delete.

For your DB server, try the following scripts:
Changes:
SELECT DISTINCT T1.ENT_NAME
, T3.CHA_ID
, T3.CHA_OID
, T2.AHS_ATT_OID
, T2.AHS_OID
, T2.AHS_BASENAME
, T2.AHS_FILENAME
FROM REP_ATTACHMENTS T2 INNER JOIN
IFC_ENTITIES T1 ON T2.AHS_ENT_OID = T1.ENT_OID INNER JOIN
ITSM_CHANGES T3 ON T2.AHS_ATT_OID = T3.CHA_OID
WHERE (T3.CHA_ATTACHMENT_EXISTS = 1)
ORDER BY T3.CHA_ID, T1.ENT_NAME;

Problems
SELECT DISTINCT T1.ENT_NAME
, T3.PRO_ID
, T3.PRO_OID
, T2.AHS_ATT_OID
, T2.AHS_OID
, T2.AHS_BASENAME
, T2.AHS_FILENAME
FROM REP_ATTACHMENTS T2 INNER JOIN
IFC_ENTITIES T1 ON T2.AHS_ENT_OID = T1.ENT_OID INNER JOIN
ITSM_PROBLEMS T3 ON T2.AHS_ATT_OID = T3.PRO_OID
WHERE (T3.PRO_ATTACHMENT_EXISTS = 1)
ORDER BY T3.PRO_ID, T1.ENT_NAME;

cha_oid = folder name, and ahs_oid = file name in the folder named Change on your ftp server

pro_oid = folder name, and ahs_oid = file name in the folder named Problem on your ftp server

Milan Karban
Respected Contributor.

Re: How to clear unused attachments stored in FTP folder?

Thanks, I'll try this way. Do you thing this will be usable for attachment folder structure in SP17 that is different from previous service packs? The new structure of this folder is more complicated to understand then the older structure.
Mihai Tita
Outstanding Contributor.

Re: How to clear unused attachments stored in FTP folder?

Hi Milan,

We only use SP15.
Please attach a screenshot showing the new folder structure.

Regards,
Mihai
Milan Karban
Respected Contributor.

Re: How to clear unused attachments stored in FTP folder?

Hi,

the description of migration process to the new attachment folder structure and new structure itself (part of the SP17 documentation) is attached.

Good thing is, that there isn't limit in number of stored attachment in the attachment folder. Worse one is, that this structure is more complicated.

Thanks.
Mihai Tita
Outstanding Contributor.

Re: How to clear unused attachments stored in FTP folder?

Hi Milan,

Thank you.
I saw that only entitytype-name is same.
For every entitytype-name you need to identify the two file lists - from your database and from your folder structure.
Try to find entity-uuid-fragments1...11 and attachment-uuid fields in your DB and to modify the scripts to work properly.

Mihai