When we began creating our internal service request catalog one of the biggest challenges we encountered was deciding how to allow requesters to select ‘users’ for various request offerings. For example, in our request for creating new Active Directory Groups, we wanted the requester to be able to choose who the ‘Managed By’ user would be for the request. The challenge here is that we have around 30,000 users that are valid for this selection and the default query result object will only display the first 2,000 results.
The first idea floated around the office was to simply overwrite the max number of results returned for this control to 30,000. This is doable and explained at http://blogs.technet.com/b/servicemanager/archive/2011/11/08/advanced-query-results-customization-for-request-offerings.aspx but puts a large load on the SM database. It also pulls back the full dataset (30,000 user CI items) to the end users web page; this means that the control takes a long time to load. What we really wanted was a people picker type control like users are used to from SharePoint that does server side filtering based on their inputs and returns smaller datasets.
After a fair amount of reading / searching the blogosphere I came across a post by Nathan Lasnoski about using tokens from previous selection in query results to lower the number of results loaded by the query result object http://blog.concurrency.com/infrastructure/service-manager-request-query-result-filtering/ which got us really close. We no longer needed to increase the number of query results returned to 30,000 so the control loaded a bit faster (still, loading 3,000 results initially takes a while so it wasn’t optimal) but we still had a rather large User Experience problem results initially loaded into the control. This tempted our users to believe that all results were being loaded and to just use the ‘search for instances’ functionality of the Query Result Control instead of specifying any filter criteria (ie the user name). My initial thought was to just set the default values of the text controls to something invalid, but, as far as I know this isn’t possible with text controls.
At this point a whiteboard became involved. I wrote up the properties that we wanted from the control and information we knew and tried to figure out how to accomplish it
- A single text input box that an end user places the name (ID or friendly) into.
- A query result control that only display’s results based on the input in the above box
- The query result control should be blank if the input box is blank
The ultimate solution designed was based on Nathan’s idea and an extension of the AD User class. The class was extended to include a property that is always blank in our environment. This allows us to add an additional filter criteria to our query results control, only display AD Users who AD User Property [Blank] does not equal the token from the text box (ie only display AD Users when the text box is not blank). Our requests now load quickly (no data is loaded into the Query Result initially) and our users are not confused by seeing ‘partial’ datasets not based on their filter criterion.
The one problem with this model is that by default blank properties are defined in the SM database as ‘null’ not the SQL empty string. This means that doing any comparisons between them and a text field (ie, the token from Filter Users) will result in NO results being returned. To work around this we have a nightly job that updates our extension database class field ‘blank’ and sets it to the SQL empty string. For us this query is below, it will be different in your environment but if you dig around in your SQL DB I am sure you can figure it out. As always, direct modification of the SQL tables is a ‘at your own risk’ operation but, for our use case it seems to work without adverse effect.
update MT_ClassExtension_378d3dfe_23d5_4f39_8218_5335167ad8c9 set Blank_60EC6C94_0BE6_2C63_90B7_4FB40CB9255D = '' where Blank_60EC6C94_0BE6_2C63_90B7_4FB40CB9255D is null
To do this refresh we have an Orchestrator Runbook running the above query 1 time per day after our ‘AD Sync’ connector has finished