Ability to Specify Request Status in Archiver

[samwoo]

Good afternoon Hornbill,

I just found out that the Request Attachments Archiver only archives Request Attachments for tickets not update for X weeks. It doesn't take into account the status of the Requests. We have requests that might not be updated for over a year but could be on-hold or remaining open for whatever reason, and we don't want these attachments to be archived until they've been resolved/closed.

As per the ticket I logged this morning- IN00182817 - please could I raise an enhancement request for the Request Attachments Archiver to take into account the specified Request Status.

I think we should be able to specify each of the Request Statuses inside the config that will enable the archiving to take place. It should still be based on the last updated date though.

Thanks,

Samuel

[samwoo]

Good afternoon,

I wonder if any progress has been made on this enhancement request? We would like to schedule the request attachments archiving process but not being able to specify the Request Status(es) to look for is preventing us from doing so.

Thanks,

Samuel

[will.good]

Hi,

We'd like a way to specify the status of requests to be picked up by the attachment archiver

e.g. only run it for requests with a status of closed

[Steve Giller]

This is not a trivial change and will require a lot more work than a simple change to the archive tool.

It is on the list, but there are currently no timescales attached to this, nor any promises for its inclusion.

[samwoo]

Thanks for the update @Steve Giller.

I hope it gains some traction.

I am currently not running this process due to this limitation, which is preventing us from being able to reduce the amount of storage being used. We have some long-standing requests open for various reasons (for example our P5 Service Requests for BAU Projects, which can reside from any Service and be assigned to any Team).

[Steve Giller]

The -call parameter allows you to specify an individual Request.

You could export a list of requests based on whatever criteria you want into a plain text file, and use PowerShell or similar to fire the Tool one Request at a time.

[samwoo]

Ooh now that's an idea actually - I think the only caveat is (and I did try to write a report on this) is to identify which tickets have request attachments physically attached to a ticket, where it hasn't already been archived. I got stuck on this a while back and gave up.

Might you know of a way to check?

[Steve Giller]

13 minutes ago, samwoo said:

Might you know of a way to check?

Not off the top of my head, no.

It's not a tool I have used in anger, but if you run it on a single test Request, check the files are archived as required, then run it again on the same Request you'll see if there are any issues with processing a Request a second time.
I wouldn't expect there to be any, at worst I would expect the Tool to return a "Nothing to Archive" response.

If you're scripting in [Insert scripting language here] you could (if you have the knowledge/time/desire) use the API to update a Custom Field in each Request you archive, and check for that value when you pull the next batch to avoid processing twice, but that's not something I'm going to have time to look into, I'm afraid.

[will.good]

@samwoo

Not sure if this is any help but this sql identifies requests where the requests have already been archived...may be a starting point

It's using the activities table however, which I understand isn't optimised for reporting so may time out. But has so far worked for us okay

SELECT
    h_itsm_requests.h_pk_reference,
    (SELECT COUNT(*) 
     FROM h_itsm_requests_attachments 
     WHERE h_itsm_requests_attachments.h_request_id = h_itsm_requests.h_pk_reference) AS attachment_count,
    DATE_FORMAT(h_buz_activities.h_updated, '%d/%m/%Y %H:%i:%s') AS "Files Archived Timestamp"
FROM h_itsm_requests
LEFT JOIN h_buz_activities ON h_itsm_requests.h_activity_stream_id = h_buz_activities.h_target
WHERE h_buz_activities.h_type = 'Archiver'

 

I think what would be good (may have mentioned this on another forum post somewhere but I can't find it now, so maybe not) is to add a column to the h_itsm_requests_attachments table to indicate whether an attachment was still present or not (potentially just a 0 or 1 indicator). That way you could just use this table to identify which requests still have their attachments and target those ones

[samwoo]

Thanks for that SQL, I appreciate you sharing that with me - very useful!

 

18 minutes ago, will.good said:

is to add a column to the h_itsm_requests_attachments table to indicate whether an attachment was still present or not (potentially just a 0 or 1 indicator

+1 - this would give me what I would need to be able to successfully achieve this using Hornbill Reporting + PowerShell in the interim whilst waiting (and hoping) that the enhancement makes it's way into the Request Attachments Archiver tool. 

[samwoo]

23 minutes ago, Steve Giller said:

Not off the top of my head, no.

It's not a tool I have used in anger, but if you run it on a single test Request, check the files are archived as required, then run it again on the same Request you'll see if there are any issues with processing a Request a second time.
I wouldn't expect there to be any, at worst I would expect the Tool to return a "Nothing to Archive" response.

If you're scripting in [Insert scripting language here] you could (if you have the knowledge/time/desire) use the API to update a Custom Field in each Request you archive, and check for that value when you pull the next batch to avoid processing twice, but that's not something I'm going to have time to look into, I'm afraid.

Thanks Steve, appreciate the response and the ideas - definitely will take it all into account.

[will.good]

13 hours ago, samwoo said:

Might you know of a way to check?

@samwoo this may work for what i think we are after

SELECT
    h_itsm_requests.h_pk_reference,
    (SELECT COUNT(*) 
     FROM h_itsm_requests_attachments 
     WHERE h_itsm_requests_attachments.h_request_id = h_itsm_requests.h_pk_reference) AS attachment_count,
    COUNT(CASE WHEN h_buz_activities.h_type = 'Archiver' THEN 1 END) AS archiver_count,
    h_itsm_requests.h_status
FROM h_itsm_requests
LEFT JOIN h_buz_activities 
    ON h_itsm_requests.h_activity_stream_id = h_buz_activities.h_target
GROUP BY h_itsm_requests.h_pk_reference
HAVING COUNT(CASE WHEN h_buz_activities.h_type = 'Archiver' THEN 1 END) = 0 
   AND (SELECT COUNT(*) 
        FROM h_itsm_requests_attachments 
        WHERE h_itsm_requests_attachments.h_request_id = h_itsm_requests.h_pk_reference) > 0
ORDER BY h_itsm_requests.h_pk_reference ASC

 

I've *think* I've also had some luck with this - can share the PowerShell script I have if you like

13 hours ago, Steve Giller said:

The -call parameter allows you to specify an individual Request.

You could export a list of requests based on whatever criteria you want into a plain text file, and use PowerShell or similar to fire the Tool one Request at a time.

 

[Steve Giller]

Just a caveat here - this uses the "buz" tables, so if your query returns no results it will be timing out, and there will be no point contacting Support!