Attachment File Size when using Clean Utility

[will.good]

It's become apparent to us that when using the clean utility to remove requests from Hornbill, the files in Hornbill keep their file size in the attachments table. HB have confirmed this is by design.

If an attachment is cleaned from hornbill, then it is deleted so no longer there and has no file size. It makes managing disc storage effectively impossible if it still shows a file size.

We use the below SQL to track requests that have a large amount of attachments and clear up these manually when needed, but when we have cleaned them already, there is no way to know whether these attachments are even still on hornbill.

 

SELECT
att.h_request_id as "Request ID",
sum(att.h_size)/1048576 as "Size",
req.h_fk_servicename as "Service",
req.h_catalog as "Catalog Item",
req.h_status as "Status",
DATE_FORMAT(req.h_dateclosed, '%d/%m/%Y') as "Date Closed"
FROM
h_itsm_requests_attachments att
JOIN h_itsm_requests req on att.h_request_id = req.h_pk_reference
Where
att.h_size is not null
group by
att.h_request_id
order by
sum(att.h_size) DESC

How are we meant to identify requests with an accurate file size, excluding those that have already been cleaned?

 

A simple solution I think would be to add a column to the request attachments table which would indicate whether this attachment was still stored in Hornbill or not.

Additionally, on the attachment's pane in a request, where an attachment has been cleaned, it would be good to have the attachment striked through as a visual indicator that it was not available anymore, as the attachments still appear as a blue hyperlink.

[SamS]

Hi @will.good,

As described in the first "Important" notification box within this wiki article, the way disk usage is optimized by Hornbill does not work as one might initially expect. In your calculations, 50 files of 2MB would take up 100MB of disk space. However, within Hornbill's model, only 2MB of disk space is used.

In your searches through our data, you have accessed the table used to show file information on attachments listed against the requests, not the table that tracks actual disk space usage (i.e., the one that keeps track of how many references there are to one file). The latter table is modified when the archiver utility possibly removes the file (i.e., the file is not removed if there is still something linking to it).

The "Your Usage" section under Hornbill Solution Center has recently been overhauled. This interface now provides a better look into where the disk space is used, and I would refer you to that instead of Database Direct.

Regarding your suggestion to disable the hyperlink, there is some merit. However, the file might still exist (i.e., in another request), and it would require modifications to both Service Manager and the utility. The timelines would be quite extended on this, as Service Manager would need the update first before the utility can be updated to contain code to set the new field.

[will.good]

14 minutes ago, SamS said:

The "Your Usage" section under Hornbill Solution Center has recently been overhauled. This interface now provides a better look into where the disk space is used, and I would refer you to that instead of Database Direct.

We aren't seeing any improvements to our Your Usage area yet... It shows the same as it usually does with just the line graph.

Which other table can we use to track the actual size of attachments per requests? This is important to us as we are trying to monitor disk space via our request attachments as this should be the easiest and most effective area to control…but is proving to be extremely difficult.

 

A question on the important notification box within the wiki link, how does Hornbill know if two attachments are the same? If there is a zip file on two different requests with the same name and file size but different contents, does it know that these are different?

21 minutes ago, SamS said:

In your calculations, 50 files of 2MB would take up 100MB of disk space. However, within Hornbill's model, only 2MB of disk space is used.

I'm also really not following this, if there are 50 different attachments on a request that are all 2MB in size, how would Hornbill only treat them as 2MB? 

[SamS]

Two files with the same name, creation date and file size does not necessarily make them the same file. Out of those three, filesize is probably the most useful, but still extremely unreliable. There are a few other ways to determine whether two files are the exactly same.

Within Hornbill, two files with different names but the same content will be recognised as the same file and thus only stored once (though you would still see multiple entries in your itsm_requests_attachments table).

With my "50 files" example, I did actually mean 50 "exactly the same contents" files. Whether they are all attached to the same request or spread among multiple requests, make no difference to the diskspace usage.