ServiceNow: allowing non-administrator users to perform mass data load
When it comes to feeding the CMDB, not all customers have discovery/inventory systems such as SCCM or Altiris to integrate with ServiceNow. Which doesn’t mean an inventory does not exist; many practitioners use the old good Excel file to keep up to date their inventory. It also regularly happens that customers have purchased an important number of new assets that must be quickly uploaded in the CMDB.
As an example, a configuration administrator or asset manager would very often need to upload a subsequent quantity of CIs as he is receiving a pallet of brand new Dell monitors.
Such migration/mass load in ServiceNow can be achieved with the Out-of-the-box System Import Set application. It is an integrated and native ETL (Extract-Transform-Load) system where data sources, import sets, transform maps and frequency can be defined. It is usually the system administrator that configures these components.
But once these elements are defined, how to manage the decentralization of regular mass CI data load?
We recently got the requirement from a customer that wanted to allow roles such as configuration administrators spread around the World to be able to upload by themselves this data.
The solution I designed here is based on the standard OOTB module “Load Data”. I copied it and simplified it in order to keep only two controls on the form:
1. Select the type of import (monitors, printers…etc.)
2. Select the file and then click on “Go”.
Of course, this solution does not cover that a well-predefined Excel spreadsheet has to be provided and a transform map associated to it has to be developed.
The final result:
Here is the required config:
1. Create/update some UI Pages/UI Macros
The following UI Pages/UI Macros are concerned:
|UI Page/UI Macro||Status||Description|
|import_status||Existing||UI Page that shows the import/transform status. Rename the existing one and suffix it with “_BAK” in order to differentiate it more easily with the new one.|
|asp_create_import_set||New||UI Page based on “create_import_set”. It has been simplified, as we wanted to get rid of options that the end-user should not have access to.|
|asp_import_set_use_table||New||UI Macro based on “import_set_use_table”. It is the full controller, label and dropdown, which allow a user to select the import set to be used.|
|asp_mass_upload_choice||New||UI Macro used by “asp_import_set_use_table”. It simply draws a dropdown that allows a selection of table defined in a system property.|
|asp_import_set_file||New||UI Macro based on “import_set_file”. It is the one that allow the selection of the file being uploaded. It has been simplified, as we just wanted the user to be able to select a file (no header row number or sheet number choices).|
2. Create the system properties used by the new mass upload pages.
A full bench of system properties has been created in order to allow very specific configuration of the new mass upload system:
|asp.mass_upload.tables|| Property defining the tables/import types that will show up in the dropdown on the first UI Page (“asp_create_import_set”).String type property with following format:Generic=u_mass_ci_upload_generic,|
Monitors=u_mass_ci_upload_monitorEvery choice that need to show up in the list is a couple “<Display name>=<tablename>”
|asp.mass_upload.import_log_link||String type property that defines the role that has access to the link “Import Log”.|
|asp.mass_upload.transformed_data_link||String type property that defines the role that has access to the link “Transformed Data”.|
|asp.mass_upload.data_source_link||String type property that defines the role that has access to the link “Data Source”.|
|asp.mass_upload.create_transform_map_link||String type property that defines the role that has access to the link “Create Transform Map”.|
|asp.mass_upload.import_set_link||String type property that defines the role that has access to the link “Import Set”.|
|asp.mass_upload.loaded_data_link||String type property that defines the role that has access to the link “Loaded Data”.|
|asp.mass_upload.transform_history_link||String type property that defines the role that has access to the link “Transform History”.|
|asp.mass_upload.run_transform_link||String type property that defines the role that has access to the link “Run Transform”.|
OOTB Admin view:
As you can see, administrators have access to “Create transform map”, “Import Log”
Non-Admin view defined using the properties described above:
The user does not have the role “admin” and therefore does not see the links “Create transform map”, “Import log”…etc.
3. Create messages (sys_ui_message) used in the new UI Pages/UI Macros
|mass_upload_choose_file||Set the text that is seen in the first screenshot “Select and Excel spreadsheet”.|
|mass_upload_import_set_table||Set the text that is seen in the first screenshot “Import Set”.|
|mass_upload_choose_ci_import_set||Set the text that is seen in the first screenshot “Import type”|
At this stage, it is possible to access the new UI Page and see the final result. However, in order to have everything working properly, you probably need to flush the application server cache:
For example: https://demo.service-now.com/cache.do
To access the UI Page, type in the URL:
For example: https://demo.service-now.com/asp_create_import_set.do
The next points (4 to 6) are mainly cosmetic and security finalizations.
4. Secure tables according to what has been set in the properties
In the system properties described above, roles that have access to the different hyperlinks showing up on import and transform status have been been set. In addition to that, you’re granting access to a lower role than administrator to the new “Mass Upload” UI Page.
Therefore, you need to clearly define who has the right to do what (write, read, create, delete) on the following tables/UI Pages via ACLs:
|asp_create_import_set||UI Page||New Mass Upload UI Page. Only the user that should be able to mass upload should have access to it.|
|run_import||UI Page||This UI Page is invoked when the user click on “Run Transform”, if the user does not have the role “import_transformer”, he will not be able access it and would therefore get an error message. In order to fix this, it is important to grant access to this UI Page, the role that will have the new Mass Upload capability and not grant the role “import_transformer” to the “mass uploader”. Giving this role to users also give them access to the menu “Import Set” which we don’t want.|
|sys_import_set||Table||Grant access to this table to the role that has been defined in the property “asp.mass_upload.import_set_link”.|
|sys_import_set_run||Table||Grant access to this table to the role that has been defined in the property “asp.mass_upload.transform_history_link”.|
|sys_import_set_row_error||Table||Grant access to this table to the role that has been defined in the property “asp.mass_upload.import_log_link”.|
|import_log||Table||Grant access to this table to the role that has been defined in the property “asp.mass_upload.import_log_link”.|
|sys_transform_map||Table||Grant access to this table to the role that has been defined in the property “asp.mass_upload_create_transform_map_link”.|
|sys_data_source||Table||Grant access to this table to the role that has been defined in the property “asp.mass_upload.date_source_link”.|
When I mentioned in the table above, “grant access”, it means that the read, write, delete and create operations should be defined (as it should be done for these tables in an OOTB instance).
5. [Optional] Build a system properties category in order to access them easily from the left-hand side menu
To access such a page from the menu, define a module of type “URL (from Arguments)” and set the argument as:
- “system_properties_ui.do?sysparm_title=<name_of_the_page> &sysparm_category=<name_of_the_category>”
Replace “<name_of_the_page>” and “<name_of_the_category” accordingly. Do not forget to encode spaces if you have some in any of the two values (using the set of characters “%20”).
6. Build the left-hand side menu
Build a left-hand side application that could look like the following one:
Non-Admin role that can perform mass upload:
Many things could be tweaked here and there to improve the solution describe above. For example, ACLs could be used in order to show/hide links on the import/transform status pages.
Nevertheless, the goals that had to be achieved for the customer with this solution were the following ones:
- Do not alter as much as possible OOTB features.
- Spent as less time as possible implementing this feature that gives access to our IT users.
Both goals are achieved as it can be easily implemented in less that 2 hours and OOTB features are lightly altered (only “import_status” UI Page is altered in order to conditionally show the links).
Finally, this solution reduces the risks compared to a solution that would consist in giving full access to the system import set application to non-administrator roles.