[HOW TO] Automatically create cmn_location entries during Azure/IDP Automatic Provisioning

NicholasDechert
Tera Contributor

A somewhat common question on here has been "How do I automatically create entries in the location/department/company tables in ServiceNow during automatic provisioning from my identity provider?" This was actually subtly answered by a user in early 2022 but did not provide enough detail for most people to follow. I'll detail here how I was able to get my IdP (Azure) to automatically create entries in the tables I needed it to. I'm going to refer to Azure in this guide, but this should be able to be applied to any IdP. YMMV.

 

This probably isn't the best place for this sort of thing, so feel free to move it if there's a better spot

 

Cause

When Azure tries to create or update a user in ServiceNow, it has to follow the same rules as a real person does when creating a new user. If you try to enter a value in a reference field that doesn't contain that value, you'll get an error. There doesn't seem to be any special sauce in either the User Creation page or in any IdP connection to automatically create these values in the tables - it has to be created before being selected. This doesn't look to be a limitation of any provisioning service, but instead a limitation of ServiceNow.

 

Resolution

The workaround for this is pretty simple - store these values from the IdP in a different (string, not reference) field in ServiceNow during insert/update, then use a business rule script to evaluate those values and create any necessary entries in the reference tables, and finally assign them. Let's go over that.

 

Create new fields in ServiceNow

We're going to create two new fields in ServiceNow - one for location and one for department. You can create more or less depending on what you want to sync during provisioning.

 

Open the User "Form Design" menu in ServiceNow by opening a user record, right clicking the name in the upper left, and selecting "Configure > Form Design"

 

NicholasDechert_0-1691687303672.png

 

Switch to the "Field Types" tab at the top left and drag and drop two (or more or less) new "String" fields onto your user page. Click the cog wheel next to each of them and give them a label (what you'll see them displayed as) and a name (how they'll be referenced on the backend/programmatically). The name field normally starts with a "u" and may be added automatically even if you remove it - FYI. 

 

Once they have been setup, hit "Save" in the upper right.

 

NOTE: You don't actually have to keep these fields displayed on any view of the record, but they may be helpful to keep around in the Admin view for diagnostic/debugging purposes. After you hit save, you can remove them if you don't want them.

 

NicholasDechert_2-1691687546039.png

 

NicholasDechert_3-1691687780273.png

 

If you decide to remove the fields from your view, you can always get them again in the "Fields" tab on the left (you may need to refresh your page to get them to show up).

 

NicholasDechert_4-1691687830222.png

 

Now let's create the business rules to use these values.

 

Creating the business rules

Open up the "Business Rules" page in ServiceNow by heading to "All -> Activity Subscriptions -> Administration -> Business Rules". Then clear the filter by selecting "All" so that we can see all of them, including the one we'll make.

 

NicholasDechert_5-1691687936325.png

NicholasDechert_6-1691687993223.png

Select "New" in the upper right, and configure the rule name and table (User [sys_user]). Then select the "Advanced" checkbox, select "Before" in the "When" dropdown, configure the "Order" if needed (when it runs), and select "Insert" and "Update" actions. You can optionally select filter conditions for when the rule should run. I chose to run it when these new fields I created were not empty, otherwise it may overwrite the user's location/department as an empty string instead of the actual value.

 

NicholasDechert_7-1691688093007.png

 

Then select the "Advanced" tab that appeared when you selected the "Advanced" checkbox, and enter the following script.

Be sure to replace the field names (u_azure_*) with YOUR field names! There are only two in my script.

 

This is adapted from the one provided by @Community Alums. Thank you!

 

 

(function executeRule(current, previous /*null when async*/) {

	// !!! Replace Me !!!
	var location = current.u_azure_location; // custom created field
	var locTable = new GlideRecord("cmn_location"); // load table
	
	// !!! Replace Me !!!
	var department = current.u_azure_department; // custom created field
	var depTable = new GlideRecord("cmn_department"); // load table
	
	if(!locTable.get("name",location)) { // see if the location exists in the table
		locTable.initialize(); // if not, create a new entry
		locTable.name = location;
		var newLocation = locTable.insert(); // insert the new entry
		current.location = newLocation; // assign to user
	} else {
		current.location = locTable.getUniqueValue(location); // assign to user
	}
	
	if(!depTable.get("name",department)) { // see if the location exists in the table
		depTable.initialize(); // if not, create a new entry
		depTable.name = department;
		var newDepartment = depTable.insert(); // insert the new entry
		current.department = newDepartment; // assign to user
	} else {
		current.department = depTable.getUniqueValue(department); // assign to user
	}
})(current, previous);

 

 

It should look like this:

 

NicholasDechert_8-1691688395453.png

 

Save this rule, then head over to Azure and open your provisioning app.

 

Mapping references in Azure

You're on your own here if you don't use Azure, but you should be able to do something similar most likely. Head to your user mapping section, scroll down to the bottom, and check the "Show advanced options" checkbox. Then hit "Edit attribute list for ServiceNow".

 

NicholasDechert_9-1691688902721.png

 

On this page, you can inform Azure about non-standard/non-officially-supported fields that exist in your instance. We will add the fields we created earlier in here and select the "String" data type. 

 

I've also added the "Source" attribute here which is a standard field, so that I can write "Azure" to it. If you used LDAP previously, this field will contain "ldap:", and then the OU that they were found in. This again can be useful to know that this user was provisioned from Azure, and allow you to run filters/reports and such against this field. 

 

NicholasDechert_10-1691689041497.png

Save the attribute list with your added attributes, and head back to the mappings section. Select "Add new mapping" at the bottom, and add your two new attributes. Know that it may take a few minutes for them to show up in Azure after making them.

 

NicholasDechert_11-1691689314346.png

 

Optional: For the "source" attribute, I used a "constant"  mapping type instead of "Direct". I filled in "Azure" to always fill this attribute with the word "Azure" to know that this information came from, you guessed it, Azure.

 

Hit "Save" in the upper right, and allow your system to reprovision your users.

 

You're done!

 

tl;dr

  • Create new "placeholder" fields in your sys_user table in ServiceNow
  • Create a new business rule (above) with a script to utilize these fields, and make new entries in your reference tables
  • Create the new attributes in your provisioning provider
  • Map the values you want to those newly created attributes in your provisioning provider
  • Let ServiceNow read those new attributes and dynamically create new entries for you before it automatically assigns those values to the correct fields.

I am far from a ServiceNow expert, so please let me know if anything in this guide isn't clear or if any terminology is wrong. I'll do my best to update this and assist people, but I do have a day job.

 

I hope this helps!

4 REPLIES 4

Ivan Betev
Mega Sage
Mega Sage

Great explanation! Exactly what I was looking for.

I'm glad this could help!

Just to add my 50 cents, here is my version of the script which also includes company:

 

(function executeRule(current, previous /*null when async*/) {
  
  /* Function to get or create record in a table */
  function getOrCreateRecord(table, field, value) {
    var record = new GlideRecord(table);
    record.addQuery(field, value)
	  record.query();

    /* If record is found return for assignment, 
if not create first and then return for assignment */
    return record.next() ? record : (record.initialize(), record.setValue(field, value), record.insert(), record);
  }

  /* Check if Entra ID (Azure AD) returned value 
then process it for company, location, and department */
  if (current.u_entra_id_company) { current.company = getOrCreateRecord("core_company", "name", current.u_entra_id_company).getUniqueValue(); } 
  if (current.u_entra_id_location) { current.location = getOrCreateRecord("cmn_location", "name", current.u_entra_id_location).getUniqueValue(); }
  if (current.u_entra_id_department) { current.department = getOrCreateRecord("cmn_department", "name", current.u_entra_id_department).getUniqueValue(); }

})(current, previous);

 

Nisha2706
Tera Contributor

It really helped. 😀

 

Thanks a ton.