Met

Thanks @Steve G, that's really helpful. A separate LDAP config just for updating manager details seems to be working in our test environment, so all looking good. Still just the issue of name changes, but that is mainly down to how our instance was originally set up. Just requires manual account edits when it throws an error. Thanks again Met

Just realised auto-tasks are stored in the same table, so think the first SQL statement should be: SELECT bpm.h_title, bpm.h_name FROM h_bpm_processes bpm LEFT JOIN h_itsm_service_catalog services ON services.h_bpm = bpm.h_name where services.h_bpm is null and bpm.h_active and bpm.h_type = 0 GROUP BY bpm.h_name

@Paul Alexander Have a look at the e-mail template and check its source code. Sometimes there can be issues with HTML tags existing in the middle of the variable name. Make sure it displays as {{.H_fixby}} exactly in the source of the email. Would also suggest including | empty so when there is no value in the database it won't display the variable in the e-mail - more info at https://wiki.hornbill.com/index.php/Email_Templates under Variable Visibility.

@Paul Alexander Try this in database direct (remember to alter the default number of returned rows from 25 if you have a lot) For active BPMs which are not associated to a catalog item, this should return the name of the active unused BPM. SELECT bpm.h_title, bpm.h_name FROM h_bpm_processes bpm LEFT JOIN h_itsm_service_catalog services ON services.h_bpm = bpm.h_name where services.h_bpm is null and bpm.h_active GROUP BY bpm.h_name Then in a similar way for IC: SELECT bpm.h_name FROM h_bpm_procapture bpm LEFT JOIN h_itsm_service_catalog services on bpm.h_id = services.h_pro_capture WHERE services.h_pro_capture is null and bpm.h_status Let me know if this isn't what you're looking for. Met

Hi @Daniel Dekel I'm using Chrome Version 104.0.5112.81 and Windows 10. Had a look at the global colour settings for page body - we had Link Colour set to the blue. Removing this colour altogether has fixed the pagination issue (although potentially may have introduced issues elsewhere where we'll need to manually set a link color on each widget). The 'Links Color' in the My Requests widget just updates the background of the buttons and pagination boxes, not the linked ticket items. Although this actually works to my benefit, it is odd that one part of the widget respects the colour overwrite and another part stays with the global settings. This is also similar for the 'Text color' option in the widget - if a global text colour is set then you can't overwrite this using the widgets styling - it just stays the same as the global setting regardless of what you set the colour to. So the only way is to delete the global colour setting and set it manually on every widget, which seems counterintuitive. Sorry if this doesn't make sense - it is bizarrely quite complicated for something so simple ha. Thanks

Thanks @James Ainsworth - it's still appearing the same as before. Also tried accessing from a fresh device with no luck. Obviously not a big issue at all, just odd that it's supposed to have been fixed!

Thanks @Daniel Dekel Have done a hard refresh but still has the same issue.

Hi, Would it be possible to add in extra styling options for pagination - for example, the option to choose the text colour or background colour for the active page pagination box. The issue is that at the moment the background for the active page box shares the same styling options as the other buttons in the widget (Link colour), and the pagination boxes share the same text colour whether active or inactive, so we are quite limited with the colour choices to make everything visible. It would also be good to be able to separately style the buttons at the top of the widget. Thanks

Hi, We had a support call with @Bob Dickinson a few weeks ago to discuss how we currently use LDAP and what we can do to improve things, which was really helpful. As part of this, I have suggested separating out the initial account creation into one LDAP config profile which queries the entire OU sub tree, and then having separate LDAP config profiles for each sub-OU. This would allow us to set account statuses (and other things) dependent on which OU the user sits in (e.g. if they are in a Parental Leave OU we might suspend their account and add a custom attribute, whereas if they are in the Leavers OU then we would archive their account). There would also be separate LDAP config profiles which include suspended accounts which exist in 'active' OU's - e.g. for accounts where we have applied a legal hold or had to manually suspend. This has been implemented in our test environment. As we are querying specific sub-OUs in our separate LDAP update configuration profiles, a problem is arising when a user’s manager exists in a different sub-OU. Hornbill is using the same data set returned from the LDAP query used to look up the user in order to look up the manager’s details. Where Hornbill is unable to link the manager to an existing account (because they don't exist in the returned LDAP data), it is populating the individual's manager with their display name which appears as “[{"id":"Firstname Lastname"}]” on the user's profile. This is also occurring in our live environment as we currently have a combined create & update LDAP config profile which excludes suspended accounts - if a user's manager is suspended, for whatever reason, then their manager is recorded as a display name rather than a user id. A semi-fix for this is to enable “Search for Manager by ID” on each LDAP configuration update profile. As a final fall-back, Hornbill will take the manager’s name returned in the CN and attempt to locate an account with the display name matching this. The problem is we have over 4,000 active employees and 3,000 'recent leavers', so some users in our organisation share the same display name. If my understanding is correct then, mainly out of morbid curiosity, I am wondering if anyone is able to answer the following: Where there is more than one manager with the same display name, how will Hornbill decide which account to choose? Will it return an error? Where the user already has the correct associated manager recorded against their account (because our "first time import" queries the entire sub tree and therefore includes all managers), will Hornbill still error out or will it leave the manager as the one that currently exists? (If it leaves the manager as it is currently set then the risk is significantly reduced – the only problem would be where a user’s manager has changed to a manager who shares the same name with another manager, and who both exist in an OU separate to the user....) Is there enough justification to have a second LDAP query which is used when looking up manager details? A workaround (I think - not entirely tested) is to have yet another LDAP config profile which is solely responsible for updating manager information for a user which queries across the entire OU subtree. It would be interesting to hear if anyone else has experienced something similar. We also have a number of other questions which we discussed in the meeting and which we will raise directly, including around employee name changes and how to deal with this in Hornbill when previous choices were made around what to use as someone's user id (a topic for another day...) Thanks Met.

Hey, Struggling a bit to understand what you are actually trying to achieve, but using Database Direct might be easier. SELECT h_ownername as Owner, h_fk_servicename as Service, COUNT(*) as Total FROM h_itsm_requests WHERE h_fk_servicename = 'Out of Hours' and h_fk_team_name IN ('Team 1', 'Team 2') GROUP BY h_ownername ORDER BY total DESC This will display a league table of the number of "Out of hours" requests assigned to users as part of Team 1 or Team 2. You can also be a bit more granular and separate out the team assignment too: SELECT h_ownername as Owner, h_fk_servicename as Service, sum( if( h_fk_team_name = 'Team 1', 1, 0 ) ) as "Team 1", sum( if( h_fk_team_name = 'Team 2', 1, 0 ) ) as "Team 2", COUNT(*) as Total FROM h_itsm_requests WHERE h_fk_servicename = 'Out of Hours' and h_fk_team_name IN ( 'Team 1', 'Team 2' ) GROUP BY h_ownername ORDER BY total DESC it would be helpful if you could provide an example of what you expect the output to look like.

Hey, At the moment when adding a dynamic dropdown to the Details section it only seems possible to select a Simple List as the data source. Would it be possible to extend this to allow choosing a Data Query as the source? We have a situation where the user is asked to choose an employee from a dynamic dropdown in the progressive capture, but sometimes this needs to be changed by the analyst if the wrong person was chosen. The problem is analysts sometimes make mistakes when filling in the free text field which then causes problems when we pull through the data into PowerBI to apply row-level security I've put in a bit of a hack job where they have to supply the person's sAMAccountName and then use an autotask to pull in the full name, team etc. to verify the person exists, but being able to pull from the Hornbill user database would be a lot easier. Thanks Met

Hey, Have a look to see if this works for you. Use the h_itsm_requests table as the data source and then query the h_sys_accounts table in the series. Create a series for each department you want to report on. You have to choose a field from the requests table to group on - you could group on source type if you want to see the number of tickets logged through each channel by each department, otherwise here I've grouped on request type. But as Bob has said, these figures are point in time when the report is run, not when the request was logged. For each department series you can use the following filter (there might be a better way to do this but it seemed to work for me). Replace the text in quotes with your department names:

Hey, Something like this might work based on what you've said above? Then add your filters against h_sys_accounts as you see fit. It just splits h_used_by into substrings using : as the delimiter (since it's a URN), and then returns the first substring from the right (the negative means start from the right, 1 being the first match).

Hi, A lot of reports (but not all) have started returning an error when attempting to modify and save the report or preview the data. Running the report still works. I can't seem to see any correlation between the reports that work and those that don't. Not sure if this could be related to the ESP update earlier today? Thanks

Hey Bit of an edge case. Have a situation where as part of a BP it would be useful to be able to check for existing tickets matching certain criteria (e.g. tickets logged in the last hour with a custom field matching a value in the current BP that's running). The idea being that we have security alerts from an external system automatically raising incidents in Hornbill, but the trigger can sometimes generate multiple alerts in a short period of time. So ideally, rather than analysts having to waste time looking at multiple 'duplicate' tickets, the BP would check if a similar ticket has been logged recently and if so, link the request to the 'master' ticket and close it off without an analyst having to intervene. The GetRequestDetails function only allows you to specify a request ID (which I get, since it's the primary key and will only return one result), but it would be useful if there was a way to construct more complicated searches (or just the ability to run SQL statements) to retrieve information and act on it. I guess it would also require additional array utilities to process multiple rows, so might be too complicated. Unless anyone can think of a different way to achieve a similar result? Thanks!

Hey, Trying to create a new routing rule template which raises a SR within a specific Service, but when selecting Service Request as the Request Type, the Service dropdown doesn't contain any data. Leaving the Request Type blank lists all Services (but obviously can't progress as Request Type is mandatory). The same issue happens when editing an existing routing rule template that raises a service request. Any help would be appreciated. Thanks

You can join the Object Reference URN with the Social Object Ref in the Requests table: Or a different way (not needed but might help with other situations):