UserQuery, GroupQuery and Create request on behalf

Csaba_Rozinyak

Hello Everyone!

I have found several threads here related to the configuration of the Create request on behalf field of Request Offerings, however they do not seem to answer some of my questions and I cannot seem to find anything else that could be relevant.

My customer has experienced a case recently where a user with privileges to see the Create request on behalf field specified a distribution group instead of an individual user account. As the Affected User of the ticket, once the Service Request was submitted, all the members of the distribution group had received a personal e-mail confirmation one by one regarding a new ticket they have successfully registered, which of course they had not. Reactions were flooding in...

This got us thinking about what we may have missed for several years - and what gap users have not used before. Apparently, none of our AD connectors in Service Manager seems to have been set up to synchronize any distribution groups to the CMDB, except for two that only synchronize two particular objects, yet the undesired distribution groups are still synchronized into the CMDB. Our suspicion is that they may have slipped in through the ConfigMgr connector.

With that in mind I thought there must be another alternative. The goal now is to limit which users are available for selection in the Create request on behalf field of request offerings. With that said, my customer is currently running the 5.0.10 version of the portal.

There is an overview of Setting Items configuration options on Cireson's support portal which does not give enough information either on how the UserQuery and GroupQuery settings can be manipulated to influence the Create request on behalf field. But so far the most valuable I have found is this thread:

https://community.cireson.com/discussion/1890/create-request-on-behalf-of-remove-group-from-picker

What I have found out is that my customer has always had their portal set up with the default out-of-the-box queries for both the UserQuery and the GroupQuery options:

DisplayName LIKE '%' + @Query + '%' OR UserName LIKE '%' + @Query + '%'

If I am not mistaken this will retrieve everything: users, shared users, security groups, distribution groups... Which it does "nicely" when I try to search for different types of accounts/groups.

This customer of mine has different types of users, internal ones whose user name starts with - let's say - the letter I, and external users whose user name starts with the letter X. To our knowledge it may only be an exception that user accounts are created without an e-mail address, so the example above might work so that the on behalf of field would retrieve all users that have an e-mail address. However, I am not sure this is the right way to go for us.

I have received the following query from an SQL colleague, which is supposed to retrieve all users whose user names (SAM account name or User Name property) start with the letter "i" or the latter "x":

UserName LIKE 'i%' OR UserName LIKE 'x%'

We want exclusively users to be available in On Behalf, but when I use this query in the UserQuery setting (while GroupQuery is the default query), there will be no groups available any more in the on behalf field (which is good!), however I cannot get more than a handful of users, and no matter how I search (by first name, last name, username), I cannot seem to get more results than about the same 10 at the top of the list. I have restarted the Cache Builder and have not tested this query yet in GroupQuery.

And that, finally, leads me to my questions:

1. Can you confirm: when filtering the Create request on behalf field, is it the UserQuery or the GroupQuery setting that must be changed or both?

2. Can you give me a more detailed explanation as to what UserQuery and GroupQuery do, and what they normally impact?

3. Specifically, is there any other field throughout the portal, other than the Create request on behalf field, that will behave differently if either of these two (UserQuery, GroupQuery) settings is changed?

 4. After submitting changes to either of these two settings, is a restart of the Cache Builder sufficient or should the portal application also be restarted in IIS?

5. What could be the reason for this query not having retrieved all users whose name starts with either "i" or "x"? Not really sure whether I should use GroupQuery for cutting out unwanted groups or if I should use UserQuery for specifying which users I do want...

UserName LIKE 'i%' OR UserName LIKE 'x%'

Thanks for any help in advance!

Csaba_Rozinyak

To share with you guys, now I have received a better explanation from Cireson Support directly.

We were using this query to include all users whose username starts with the letter "I" or "X" and whose usernames do not start with ADM (for admin):

(UserName LIKE 'i%' + @Query + '%' OR UserName LIKE 'x%' + @Query + '%') AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

According to Support: "The trouble with this query is that if the users are looking for a username like i100, they will naturally type i100 into the search. So the value of @query is i100 and your SQL script is now looking for usernames which match 'i%i100%'. You need to separate the condition that the first letter be either 'i' or 'x', and that the user name matches the string entered by the user. This should work:"

(LEFT(UserName,1) IN ('i','x') AND UserName LIKE '%' + @Query + '%') AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

Having tested this query, we were able to search by the username and filter out everything else, but we were unable to search by first name or last name. This was then fixed by the following query:

(LEFT(UserName,1) IN ('i','x') AND (UserName LIKE '%' + @Query + '%' OR DisplayName LIKE '%' + @Query + '%')) AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

Furthermore from Support:

"This query limits which users can be selected in any of the user pickers in the portal."

Finally, a Cache Builder restart seems to be sufficient in each case after updating these queries. Hope this will give you all a better understanding of how UserQuery, GroupQuery and the on behalf field as well as user pickers work together.

Thanks very much, Cireson Support!

Csaba_Rozinyak

Some update since my previous post.

We have now found out that the UserQuery setting is used to specifically filter and query users while the GroupQuery filters out groups, and that the SQL query behind uses a UNION to merge the results from both SELECT queries, and merge into the results shown in the Create request on behalf field.

For GroupQuery we have simply configured a query that retrieves groups with names that are unlikely (in pre-production), so that it WON'T retrieve any that are actual and real:

DisplayName LIKE 'zzz' + @Query + '%' OR UserName LIKE 'zzz' + @Query + '%'

UserQuery is still the tricky part. We ran a trace while restarting the Cache Builder service in order to see the whole query which the UserQuery part is baked into. Using the whole query we managed to get the exact results as expected. Alongside the afore mentioned GroupQuery, the following UserQuery retrieves all users whose UserName starts with I or X, and it also excludes administrator accounts whose DisplayName starts with ADM.

(UserName LIKE 'i%' + @Query + '%' OR UserName LIKE 'x%' + @Query + '%') AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

Interestingly, while this will get the expected results both via SQL Management Studio and the Create request on behalf of field, it is not possible any more to actually search the results within the on behalf field, not by First name, not by Last name and not by user ID. When simplified, the following makes no difference either:

UserName LIKE 'i%' + @Query + '%' OR UserName LIKE 'x%' + @Query + '%'

When I again switch back to the default query (see below), it will be possible again to search in the results (but of course the query won't be correct for our purposes).

DisplayName LIKE '%' + @Query + '%' OR UserName LIKE '%' + @Query + '%'

Can anybody tell why this is? Perhaps there is still something to tweak on this one below for it to become searchable - otherwise it is getting the right results:

(UserName LIKE 'i%' + @Query + '%' OR UserName LIKE 'x%' + @Query + '%') AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

Furthermore, my other questions still stand:

3. Specifically, is there any other field throughout the portal, other than the Create request on behalf field, that will behave differently if either of these two (UserQuery, GroupQuery) settings is changed?

4. After submitting changes to either of these two settings, is a restart of the Cache Builder sufficient or should the portal application also be restarted in IIS? It seems like a restart of Cache Builder should do the job.

Csaba_Rozinyak

To share with you guys, now I have received a better explanation from Cireson Support directly.

We were using this query to include all users whose username starts with the letter "I" or "X" and whose usernames do not start with ADM (for admin):

(UserName LIKE 'i%' + @Query + '%' OR UserName LIKE 'x%' + @Query + '%') AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

According to Support: "The trouble with this query is that if the users are looking for a username like i100, they will naturally type i100 into the search. So the value of @query is i100 and your SQL script is now looking for usernames which match 'i%i100%'. You need to separate the condition that the first letter be either 'i' or 'x', and that the user name matches the string entered by the user. This should work:"

(LEFT(UserName,1) IN ('i','x') AND UserName LIKE '%' + @Query + '%') AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

Having tested this query, we were able to search by the username and filter out everything else, but we were unable to search by first name or last name. This was then fixed by the following query:

(LEFT(UserName,1) IN ('i','x') AND (UserName LIKE '%' + @Query + '%' OR DisplayName LIKE '%' + @Query + '%')) AND DisplayName NOT LIKE 'ADM%' + @Query + '%'

Furthermore from Support:

"This query limits which users can be selected in any of the user pickers in the portal."

Finally, a Cache Builder restart seems to be sufficient in each case after updating these queries. Hope this will give you all a better understanding of how UserQuery, GroupQuery and the on behalf field as well as user pickers work together.

Thanks very much, Cireson Support!