Met

To clear, that was a +1

Hi, It would be good to be able to have a way to transfer all requests from one customer (or request owner) to another. When our instance was created many many years ago, it was decided (with expert advice from Hornbill) that we should use the account name as the unique userID. We now know this was the wrong decision and there is no way to correct it. The account name will change when a person's name changes, resulting in a duplicate account being created (and the original account becoming an inaccessible, "zombie" account - SSO). Our user base is significant and we see quite a few name changes for various reasons each month. Currently there is no way to easily transfer all requests from one customer to another customer, or transfer requests owned by an analyst to another. Proposing that there should be some way to transfer requests in bulk. Appreciate this is a bit of a niche case (and we are looking to implement things better for a second instance we are potentially looking to get), but figured I'd put this out there.

We already do this making by calls to the API (boo, hiss, bad behaviour, etc.), but it really should be a standard function. Also been discussed on

The issue you might then face is that you can only get details of a user using one of the the three following criteria: If you store the email address as one of these (or you can manipulate the email address to produce a login ID) then great, otherwise you might need to rethink. You can't find a user based on their email address, presumably as that field does not enforce unique values.

I think h_msg_status determines whether the email has been read or not. 1 = read, 2 = unread.

Hey @will.good, Your SM report might be the issue here, at least when running it against our data. You need to select which column you are counting against - counting/grouping on either of the two produces the same result. I've attached a definition file which should hopefully match the DB direct. Having said that, the SM Report of yours I uploaded produced vastly different numbers to DBD (57 vs 2000 in total). Have a go and see if it works. Met first-time-fix.report (1).txt

@will.good the tables share the same column names, hence the error. Just need to specify the table name as well as the column e.g. h_itsm_requests.h_description and h_itsm_requests_attachments.h_description. Worth using aliases to make them easier to read like the example below: SELECT att.h_request_id as "Request ID", att.h_pk_id as "ID", att.h_filename as "Filename", att.h_description as "Description", att.h_size as "Size", concat( substring(att.h_timestamp, 9, 2), "/", substring(att.h_timestamp, 6, 2), "/", substring(att.h_timestamp, 1, 4) ) as "Date Added", substring(att.h_timestamp, 12, 8) as "Time Added", att.h_visibility as "Visibility", req.h_status as "Status" 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 order by att.h_size DESC

It would definitely help with dealing with the final 'clean up' operations for those who have recently left. Once someone has been gone for a while then it doesn't matter so much that they no longer appear in the UI for analysts. Unfortunately the LDAP is only really good at performing a one time move from Active to Suspended/Archived, when they are moved to a Leavers OU. There is no way to detect when a user no longer exists in AD and perform an action. As otherwise, we could suspend while they are in the Leaver OU and then archive once deleted.

I guess it depends what you're trying to achieve. For one use case, the customer would be given the option to select from a simple list dropdown in the IC or choose 'Other' if the value isn't displayed. They could then populate a free-text box with the 'Other' value. In the BPM we would then have a task for the analyst to review whether the 'Other' value is appropriate to add to the simple list through a task which would kick off the above described. We do use the API to achieve similar tasks but ideally where things are much easier in a node we will always push and push...

This is something we have also requested. Arguably once you have a lot of simple lists with hundreds of entries, a mistake is more likely to be made through manual UI entry. The issue of duplicate values would just be a case of returning a success/failure outcome. The issue of duplicate display values is different and would require either good workflow management, the option to enforce unique display values in the node (and return a failure if not) or adding a node which can be used to check if an item already exists in a Simple List against the value or display value (which would be preferable but also require good workflow).

This would also be beneficial for us when a new account is created for an employee who has changed their name, so we can transfer their tickets or assigned requests to the new account without it being a manual task.

@SJEaton Try '{{.H_custom_k}}' LIKE '%Basildon%'

@Ben Maddams Join the Supplier Contracts and Suppliers tables together on Supplier ID and that should get you what you're looking for.

Hi @Berto2002 I think this information is contained within h_bpm_instance: h_name = name of the BPM workflow invoked h_version = which revision of the workflow invoked h_created_on = the datetime the BPM was invoked h_application = com.hornbill.servicemanager Each row is an invocation of a BPM workflow from what I can see. Can then link this to h_bpm_processes on h_name and h_version for both tables in order to pull h_title , which is the name you see in the process designer, and other information from h_bpm_process. I haven't had a chance to actually test this in the reports section, but it makes sense in my head... could be completely wrong.

@JeremyJust realised what the issue might be - if your problem tickets are prefixed with something different. Seen Steve uses PM in which case it would be: SELECT problems.h_entity_id as 'Problem ID', requests.h_summary as 'Problem Summary', requests.h_description as 'Problem Description', requests.h_fk_team_name as 'Team', requests.h_fk_priorityname as 'Priority', ( SELECT h_root_cause FROM h_itsm_problems WHERE h_fk_reference = problems.h_entity_id ) as 'Root Cause', ( SELECT count(*) FROM h_sm_relationship_entities WHERE h_entity_id = problems.h_entity_id ) as 'Total Incidents', problems.h_pk_reference as 'Latest Incident', problems.h_datelogged as 'Date Logged' FROM ( SELECT a.h_datelogged, a.h_entity_id, a.h_pk_reference FROM ( SELECT reqs.h_pk_reference, reqs.h_datelogged, ents.h_entity_id FROM h_itsm_requests reqs JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference where ents.h_entity_id LIKE 'pm%' and ents.h_linked_entity_id like 'in%' ) a LEFT JOIN ( SELECT reqs.h_pk_reference, reqs.h_datelogged, ents.h_entity_id FROM h_itsm_requests reqs JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference where ents.h_entity_id LIKE 'pm%' and ents.h_linked_entity_id like 'in%' ) b ON a.h_entity_id = b.h_entity_id AND a.h_datelogged < b.h_datelogged WHERE b.h_datelogged is null ) problems JOIN h_itsm_requests requests ON problems.h_entity_id = requests.h_pk_reference WHERE requests.h_status NOT IN ( 'status.closed', 'status.cancelled' ) Just need to change the PM% above to whatever prefix you use? Same for incidents too.

@Jeremy That's odd - it seems to work in our instance. Does take a good 10-15 seconds to display results though. Does it give an error or just not display anything? But yeah if you want it to be a scheduled report then bit of a moot point anyway - don't think it's possible to return the total incidents attached to each problem in the normal reporting area as its own column. Would require Hornbill to let people define their own custom/calculated columns rather than only what exists in a table.

Hi @Jeremy I've had a go for you but couldn't think of a simple way of achieving this without using Database Direct. SELECT problems.h_entity_id as 'Problem ID', requests.h_summary as 'Problem Summary', requests.h_description as 'Problem Description', requests.h_fk_team_name as 'Team', requests.h_fk_priorityname as 'Priority', ( SELECT h_root_cause FROM h_itsm_problems WHERE h_fk_reference = problems.h_entity_id ) as 'Root Cause', ( SELECT count(*) FROM h_sm_relationship_entities WHERE h_entity_id = problems.h_entity_id ) as 'Total Incidents', problems.h_pk_reference as 'Latest Incident', problems.h_datelogged as 'Date Logged' FROM ( SELECT a.h_datelogged, a.h_entity_id, a.h_pk_reference FROM ( SELECT reqs.h_pk_reference, reqs.h_datelogged, ents.h_entity_id FROM h_itsm_requests reqs JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference where ents.h_entity_id LIKE 'pr%' and ents.h_linked_entity_id like 'in%' ) a LEFT JOIN ( SELECT reqs.h_pk_reference, reqs.h_datelogged, ents.h_entity_id FROM h_itsm_requests reqs JOIN h_sm_relationship_entities ents ON ents.h_linked_entity_id = reqs.h_pk_reference where ents.h_entity_id LIKE 'pr%' and ents.h_linked_entity_id like 'in%' ) b ON a.h_entity_id = b.h_entity_id AND a.h_datelogged < b.h_datelogged WHERE b.h_datelogged is null ) problems JOIN h_itsm_requests requests ON problems.h_entity_id = requests.h_pk_reference WHERE requests.h_status NOT IN ( 'status.closed', 'status.cancelled' ) Rather than querying h_itsm_problems it instead creates a table (problems) of each problem request and the latest incident (by date logged) linked to it. We then pull information from h_itsm_requests for each of the PRs returned in our dataset where the PR is not closed or cancelled. It would be worth doing some checks to make sure it is returning what you'd expect - not had a chance to completely test it. Let me know if it's not working or you wanted something else. Cheers Met

Hi @Salma Sarwar Assuming that Custom 32 contains data (if it doesn't then it will show as {{.H_custom_32}} rather than blank, unless you use {{.H_custom_32|empty}}) then check the source of the e-mail template to check that there is nothing interfering with the variable. Sometimes where formatting has been applied using the editor there can be issues with HTML appearing in the middle of the variable name. Because they are HTML tags they don't show in the normal editor view, but you can pick them up using the source view.

Hi @will.good Try taking a look at the source of the e-mail. There is probably a <p> </p> either side of the ESP condition or a <br /> in front. You would just need to move these two tags inside the condition so they only display when the condition is met.

Hi @Frank Reay If you want to take someone's name and then search for all assets they've used then it is a little more complicated. I haven't tried to do this in the normal Reports, but something like this in Database Direct might work (I would test it first). You'll need to replace userid with the individual's userid. This looks at h_used_by but you can change it to look at h_owned_by. You can probably join the h_cmdb_asset table to the h_sys_audit_trail table instead, but if an asset has been deleted or its ID has changed then you won't pull through all the results. This looks at the h_sys_audit_trail table to first find all the asset IDs that the user has been assigned, and then again to pull the name of the asset from the same table. You might want to edit this to only include certain asset types too. SELECT DISTINCT asset_name.h_new_value as 'Asset Name' FROM ( SELECT h_id as id FROM h_sys_audit_trail WHERE h_column = 'h_used_by' AND ( h_action_type = 'Insert' OR h_action_type = 'Update' ) AND h_new_value like '%userid%' ) asset_id JOIN h_sys_audit_trail asset_name ON asset_id.id = asset_name.h_id WHERE asset_name.h_column = 'h_name' There is probably a simpler way to achieve this out side of DD. Thanks Met.

Sorry should have said - it wouldn't be the procurement team logging the new request, it would be a user. The procurement team would run the task once the user has logged the updated ticket.

@Steve GillerThanks - yeah I came across that, problem is we need to capture quite a lot of information through the IC on the second request. What we'd probably do is once the second request has been made and the task is run, copy information from the first request to the second request (which already contains information from IC), link them then close the first.

Hey @James Ainsworth We are bringing part of our procurement process into Hornbill and as part of this users can submit an initial pre-engagement ticket before they are in a position to provide all the details of the procurement activity. This logs a ticket which goes to the procurement team who at a later date can run a task to decide whether we are proceeding with the activity. If we decide to proceed, it will instruct them to ensure a new ticket is logged which contains all the details and for that ticket reference to be entered into the task. The ticket format is verified using regex and the BP then checks that the ticket is a valid procurement ticket - if it is, it will update both tickets to say they are connected and then close the original ticket. We would also like it to link the two tickets as well without the analyst having to do a second step. The idea is this would stop them linking wrong tickets or tickets logged incorrectly. There is still some manual input required on this particular use case.

Hi, Unless I've missed something, I'm not sure this is currently possible. It would be useful if we could link requests as part of a business process - we're trying to automate as much as possible and this would really help with that. Thanks Met

This would be really helpful for us too. Having to upload documents individually is really time consuming. Ideally it would let you choose multiple files to upload and then you can fill in the details for each (or choose which options apply to all documents).