[Enhancement - Display "update custom field" nodes in a different colour]

On 27/03/2024 at 22:50, Steve Giller said:

I was thinking of something like this:

This could be as well as listing the fields in their own Notes separately.

I'm not sure this approach is practical when business processes are large and/or subject to quite a lot change. It feels like a bit of a hack.

I think being able to set a colour for each node is a great idea. 

Please could this be marked as an enhancement?

[Regex giving problems]

2 hours ago, Steve Giller said:

After testing, simply removing the caret worked fine in my Instance.

Also, I noticed on a second look that the "pipe" after the period was redundant  as well - it wasn't breaking anything, it was just pointless.

The problem with this is that it will match regardless of how many characters you have without the anchors (^ $). If you try changing it to {0,5} does it error out when you put in more than 5 characters? Without the anchors it is just checking for 0 or more characters at any point in the string

[Regex giving problems]

I'm not sure removing the ^ and $ will work in this case (could be wrong) but I would suggest using \s\S to match all non-whitespace and whitespace characters to achieve the same (I think this is usually the preferred way with JavaScript)

^[\s\S]{0,255}$

 

[How to generate Working Days on a form]

@Sandip Bhogal

You could use Application -> Utility -> Calculate Working Date/Time to calculate when the request should be on hold until, and populate this in the 'On Hold Until' option.

You'll need to create a working time calendar which only includes Mon-Fri 00:00-24:00 and excludes bank holidays. In the workflow, use get local time to get the current date/time, and then use this along with the working time calendar you made to populate the fields in the Calculate Working Date/Time node. The calculate date/time output can then be used in your node - set on Hold Period to Ignore and On Hold Until to Variable, and select the output.

[How to obtain a list of Active Requests that either do not have any human tasks (activities) or had them but they are closed]

@Berto2002

A left join on the requests table and tasks table will pull all rows from the Requests table, plus any matching rows from the Tasks table based on your join criteria. If there is no data in the Tasks table for a Request (because there are no activities on the request), it will still pull through the row from the Requests table, but the task fields will be Null for that row. So by then applying the filter Tasks->h_obj_ref_urn = NULL you will see all Requests that exist in the Requests table, but don't exist in the Tasks table (the request segment in the diagram below, without the intersetion with tasks). The REPLACE(h_sys_tasks, etc.) criteria is only used to pull the requests where the data exists in both tables. It is a bit counter-intuitive as you're using a criteria which actually you want the reverse of.

For your table joins, I would make sure you are only using a left join for the Requests -> Tasks tables. The rest should all be normal Joins.

Aside from that, the only other thing I can think of is double checking that you've chosen Value equals -> Against Custom Criteria when using the SQL functions. If you just choose 'Equals value' and put the function in the pop-up box then it will return no results. Not possible to tell from your screenshots if you've done this or not so thought it's worth double checking.

Aside from that, it should work from what I can see.

[How to obtain a list of Active Requests that either do not have any human tasks (activities) or had them but they are closed]

Hi @Berto2002

You should use a LEFT JOIN to pull all rows from the h_itsm_requests table and only matching rows from the h_sys_tasks table. You could then look at where h_sys_tasks.h_obj_ref_urn is NULL. This should give you a list of requests which have never had a task associated with them.

If you also want to pull requests where all tasks have been completed, then I am not sure if this can be done in the Reporting area (don't currently have access to try it) without using database direct:

SELECT 
  req.h_pk_reference, 
  tasks.h_title, 
  tasks.h_details, 
  tasks.h_completed_on 
FROM 
  h_itsm_requests req 
  LEFT JOIN h_sys_tasks tasks ON CONCAT('urn:sys:entity:com.hornbill.servicemanager:Requests:', req.h_pk_reference) = tasks.h_obj_ref_urn 
WHERE 
  -- Make sure there are no tasks where h_completed_on is empty - i.e. no tasks that are outstanding
  NOT EXISTS (
    SELECT 
      1 
    FROM 
      h_sys_tasks tasks2 
    WHERE 
      CONCAT('urn:sys:entity:com.hornbill.servicemanager:Requests:', req.h_pk_reference) = tasks2.h_obj_ref_urn 
      AND tasks2.h_completed_on is null
  ) 
  -- Or pull all requests where there are no tasks
  OR NOT EXISTS (
    SELECT 
      1 
    FROM 
      h_sys_tasks tasks2 
    WHERE 
      CONCAT('urn:sys:entity:com.hornbill.servicemanager:Requests:', req.h_pk_reference) = tasks2.h_obj_ref_urn
  ) 
  And req.h_status not in ('status.closed', 'status.cancelled')
ORDER BY 
  req.h_pk_reference DESC

I think this should achieve what you want - it will pull all requests plus any tasks details where either the request has no tasks, or the request has all its tasks completed. 

There might be a simpler way to do this that I've completely missed.

 

[Is it possible to report on use of Custom button/Autotask?]

@Estie Unless I've missed something, you could just report on h_bpm_instance. Reporting on the custom field wouldn't technically show the number of times the button was pressed, just the number of requests where the button was pressed at least once.

h_bpm_instance contains a list of all invocations of a workflow or auto-task. You can filter with h_name = the name of your auto task. h_id for autotasks will start with AT and BPM for workflows.

Can provide more detail if you need. There is also some more info here which might help: 

 

 

[Help requested: I would like a report for the Asset history of a User]

Just now, Berto2002 said:

@Met thank you this helps. I got a report started. I have an anomaly though. When I initially ran it, I had 8 rows. I then found an old asset I used to have and re-assigned it back to myself and two rows appeared. One was the new Update row and one was the original Insert row which had not previously been listed. In other words, it only displayed the Insert row after an Update row was added. In other words, the report could be missing Insert Data. I do note that there is a row for an Asset which is Insert only so this is not a cut and dry situation. Just asking if you can see a reason for this in the structure.

No Adobe InDesign showing at first:

Then two rows appear and the Insert row is the date the record was created with me as Used By but which was not previously showing:

I have another example which I have not yet edited. This Adobe Photoshop asset is not showing in the report but it was created with me as Used By (2022-06-16 10:51:32)

These are my filters:

I am using my Name as the search although each entry does also match with the same URN also.

A mystery?

@Berto2002 I would remove the CMDB Assets -> Used By Name filter. This will only return assets that are currently used by the user, not historical ones where someone else now uses it or nobody uses it. 

[Help requested: I would like a report for the Asset history of a User]

On 9/21/2023 at 3:24 PM, Berto2002 said:

We've been asked to provide a report on what devices (assets) a given user had in the past. This is information held as a history item in some assets but it's finding those assets that's hard.

Firstly, can we find this information through the UI? I.e. we can see user X had laptop1 then laptop2?

Or if not, what tables and joins would I need to drill into the asset history to draw-up the asset names/IDs where the Used By was previously User X?

Thanks for any pointers.

Try joining h_sys_audit_trail (ID) on h_cmdb_assets (Asset ID).

Then need to add filters:

h_sys_audit_trail Table = h_cmdb_assets

h_sys_audit_trail New Value = <user input>

h_sys_audit_trail Action Type = Insert or Update

h_sys_audit_trail Column = h_used_by (and use URN for user input), alternatively use h_used_by_name (and use their name for user input - but might include wrong assets for someone with the same name). Or also use h_sys_accounts and set up a join where it builds the URN, and the filter is on h_sys_accounts account name instead.

Should give you a starting point.

[Enhancement: New Hornbill Automation function/task to "Link Request"]

On 8/30/2023 at 12:43 PM, samwoo said:

I have a parent request which adds itself to a Simple List via a HTTP Request Cloud Automation:
 

In the child request, there is a human task that grabs the details from the Simple List. The ability to link the two requests together using Hornbill Automation would be most beneficial. I'm not having any luck setting this up to work in a HTTP Cloud Automation (I copied the request payload from the F12 developer tools, but to no avail) unlike the listAddItem one and listDeleteItem ones which always works.

So I've already +1 this idea above but I'm hoping my update will bring about its attention again.

Give this a go - for experimental purposes only, of course:

Body:

<methodCall service="apps/com.hornbill.servicemanager/Requests" method="linkRequests">
<params>
  <parentRequestId>&[global["inputParams"]["requestId"]]</parentRequestId>
  <childRequestId>&[functions.getTaskAnswers("task-5f7649b8").field_2]</childRequestId>
</params>
</methodCall>

URL is https://mdh-p01-api.hornbill.com/instance/xmlmc/apps/com.hornbill.servicemanager/Requests?op=linkRequests

Make sure the API key has access to apps/com.hornbill.servicemanager/Requests:linkRequests

[Reporting on users who do not have a division in their organisation]

Hi @yelyah.nodrog @AlexOnTheHill

Try the following

SELECT 
  DISTINCT h_user_id 
FROM 
  h_sys_account_groups 
WHERE 
  h_user_id NOT IN (
    SELECT 
      h_user_id 
    FROM 
      h_sys_account_groups 
      JOIN h_sys_groups on h_group_id = h_id 
    WHERE 
      h_type = '<ID>'
  )

The second SELECT statement is yours and returns a list of users who do belong to a specific organisation/team etc.

The first SELECT statement then returns a list of users who do not appear in the output of the second statement.

[Regex Substring automation questions]

10 minutes ago, Andrew McAllister said:

Thanks for the tip on the work around. If I understand correctly the .match (whatever)[1] returns the group contents?

However the regex string I have seems to return a match result without the need for the unsupported group stuff. Hence my other questions about what other parts of regex may or may not be supported and how I can troubleshoot more easily.

.*\(\K[^\)]*

 

This won't work because it's not supported by the regex engine JavaScript uses. You'll need to use capturing groups similar to the regex example I gave.

[LDAP - Office Location]

22 minutes ago, SamS said:

Hi @Met,

Which "Office Location"/"Site" are you using (in the Data Import Config)? The one mentioned in the "User Account"-section? Or the section within "User Options"?

It is the latter (User Options) which you need to have configured - I am pretty sure the other one gets ignored.

Perfect, thank you. It works using the User Options location.

[Regex Substring automation questions]

For the regex side: I don't think you can access the group result using the Hornbill function, but there is a (unsupported) work around.

Rather than using a function, you can put the regex directly into the variable, for example:

&[global["flowcoderefs"]["whatever"]["variable"].match(/REGEX HERE/)[1]]

Be careful with the placement of the square brackets.

Try out the following regex for what you want to achieve (not tested extensively) - it should match the contents of the last brackets in a string:

/\(([^()]+)\)[^()]*$/

 

[LDAP - Office Location]

Hi,

Attempting a few dry-runs to pull in the office location (physicalDeliveryOfficeName) for our users using the LDAP import. I have made sure the name of the office locations in Hornbill match those in AD, however it is not updating the field.

I've tested using a custom attribute and this pulls through the data fine.

I am wondering if I'm missing something, or if potentially when the SItes/Office Locations change was made the tool wasn't updated?

 

Thanks

[Dynamic checkbox group]

Hey,

Just wanted to check if it is intended behaviour for a dynamic checkbox group to be limited to returning just 10 items, whereas one that is based on a simple list is able to return far more.

Trying to create a form to select multiple users from a team, and it is currently limiting it to just 10 users for larger teams.

Thanks

Met

[Archived users are being reactivated (but not by us)]

You could also turn it around and set up a separate LDAP config profile targeting only disabled users to perform actions on them (e.g. to suspend their Hornbill account, update their status so analysts know they might not reply on tickets etc.). To account for someone's account being enabled again, just make sure the main LDAP query does the opposite (e.g. changes status to Active or something).

(userAccountControl:1.2.840.113556.1.4.803:=2)

should target disabled users in the LDAP query.

Or add

(!(userAccountControl:1.2.840.113556.1.4.803:=2))

to ignore disabled users in your main LDAP query.

[Help add extra option on a custom form]

Create a new single line text field and under 'Default flags' untick "this field will be visible on the form".

Under the override flags, create a new condition with "this field will be visible on the form" ticked. For the condition, select the name of the field where the user selects other and have that == "Other". You can also make the field mandatory when it appears too, if you want.

[Data access via different groups only]

I imagine you'll need to separate out the different types of changes and releases into separate services, so you can then restrict visibility to appropriate teams. As far as I know, there isn't a more granular way of restricting visibility of tickets in the same service.

[Regex for character in a multi line text field?]

Hey @LawesD

^[\s\S]{0,800}$

should work across multiple lines.

Met

 

[Data access via different groups only]

Click into the service in the service portfolio then define which teams support it:

Once one or more are added, only those in that team will be able to view the requests.

[Emails not being sent from Horbill]

Should be possible, wonder if it was a syntax issue when adding the IPs in individually? The 10 limit likely refers to the number of DNS lookups that can be performed in one SPF record.  

RFC states:

Quote

The following terms cause DNS queries: the "include", "a", "mx", "ptr", and "exists" mechanisms, and the "redirect" modifier. SPF implementations MUST limit the total number of those terms to 10 during SPF evaluation, to avoid unreasonable load on the DNS.

Individual IP addresses in the SPF record don't count towards the 10 limit. But you would need to make sure that if Hornbill makes any changes to their list of IP addresses sending mail that you update these manually. Normally this change would be captured automatically using spf.hornbill.com.

Your domain should only have one SPF record (otherwise recipient servers will reject mail), but perhaps your DNS system combines them into one when publishing. 

 

[Unable to logon to Hornbill]

They just did ctrl + F5

[Unable to logon to Hornbill]

A hard refresh seems to have worked for some users, but not ideal asking 3500 people to do that. 

[Unable to logon to Hornbill]

We also have the same problem