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:

11-570x221

Here is the required config:

1. Create/update some UI Pages/UI Macros

The following UI Pages/UI Macros are concerned:

UI Page/UI MacroStatusDescription
import_statusExistingUI 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_setNewUI 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_tableNewUI 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_choiceNewUI 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_fileNewUI 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:

PropertyDescription
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_linkString type property that defines the role that has access to the link “Import Log”.
asp.mass_upload.transformed_data_linkString type property that defines the role that has access to the link “Transformed Data”.
asp.mass_upload.data_source_linkString type property that defines the role that has access to the link “Data Source”.
asp.mass_upload.create_transform_map_linkString type property that defines the role that has access to the link “Create Transform Map”.
asp.mass_upload.import_set_linkString type property that defines the role that has access to the link “Import Set”.
asp.mass_upload.loaded_data_linkString type property that defines the role that has access to the link “Loaded Data”.
asp.mass_upload.transform_history_linkString type property that defines the role that has access to the link “Transform History”.
asp.mass_upload.run_transform_linkString 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”

2-570x291

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.

4-570x263

3. Create messages (sys_ui_message) used in the new UI Pages/UI Macros

 

MessageDescription
mass_upload_choose_fileSet the text that is seen in the first screenshot “Select and Excel spreadsheet”.
mass_upload_import_set_tableSet the text that is seen in the first screenshot “Import Set”.
mass_upload_choose_ci_import_setSet 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:

To access the UI Page, type in the URL:

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:

ObjectTypeDescription
asp_create_import_setUI PageNew Mass Upload UI Page. Only the user that should be able to mass upload should have access to it.
run_importUI PageThis 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_setTableGrant access to this table to the role that has been defined in the property asp.mass_upload.import_set_link”.
sys_import_set_runTableGrant access to this table to the role that has been defined in the property asp.mass_upload.transform_history_link”.
sys_import_set_row_errorTableGrant access to this table to the role that has been defined in the property asp.mass_upload.import_log_link”.
import_logTableGrant access to this table to the role that has been defined in the property asp.mass_upload.import_log_link”.
sys_transform_mapTableGrant access to this table to the role that has been defined in the property asp.mass_upload_create_transform_map_link”.
sys_data_sourceTableGrant 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:

Admins:

5

Non-Admin role that can perform mass upload:

6

Conclusion:

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.

mm
Loic joined the company in 2010 and works as Principal Consultant in our Nyon office. He is certified ServiceNow System Administrator, Implementation Specialist and ServiceNow Instructor. With a strong technical background, he is focusing on Solution Consulting since 2011 and is specialised in the areas of CMDB, Asset and ITOM.
Recent Posts
Showing 6 comments
  • ram
    Reply

    Need the code for all changes of file

  • Sapna
    Reply

    Hi,

    Didnt find create_import_set in UI page/UI Macro??

  • Sapna
    Reply

    Hi,
    We didnt find any UI Pages/UI Macro except “import_status”

    • mm
      Stève Cattin
      Reply

      Dear Sapna, thanks for your comment. This article dates back to 2011 and as of the ServiceNow Fuji release (2015) this method no longer apply. There is now an import functionality that relies on Excel templates, we let you check this article for more information: http://wiki.servicenow.com/index.php?title=Easy_Import#gsc.tab=0

  • Uday
    Reply

    Hi Stève

    Easy import is rather complicated. Non admin user has to separate the records for insert and update.
    And for update, he has to include sysid of the existing record which for non admin users may not understand easily

    Regards
    Uday

  • Ben Collyer
    Reply

    Great article. Parts of this were very useful.

Leave a Comment

Start typing and press Enter to search