LDAP Integration - Load Locations and Departments along with user records.

Deepak Ingale1 — Mon, 03 Aug 2015 11:39:13 GMT

Many a times we integrate LDAP with servicenow and does not get all the information required for reference fields like 'location' and 'department' on 'sys_user' table from LDAP itself.

We are then left with the option to load these information from another spreadsheet and then associate it with user records which is sometime a painful task.

Recently we happened to work on LDAP integration with one of the customer. Good thing we noticed while doing this integration is that their user object attributes were really defined nicely and contained a lot of information rather than the basic attributes like samAccountName, firstName etc.

We found user object was containing information related to locations (attributes like 'c' for country, 'st' for state, 'physicaldeliveryofficename' for city and 'streetaddress' for street address. Similar thing we noticed about department information also. So we thought to leverage those attributes, import location and department details to their respective tables and associate them to user record along with loading user information from LDAP.

I had to write certain amount of code but it paid well and we could load locations and department details with no issue. So sharing the same with you, so that you can use it with little bit modification if required after analyzing location attributes completely.

You will require to paste this code into 'onBefore' transform map of LDAP. Code will take care to import locations and department details.

   
//gs.log('Deepak: xform User ' + source.u_givenname + ' ' + source.u_sn + ' in country:   ' + 
       //source.u_c + ' With complete address ' + source.u_streetaddress + ', ' + source.u_physicaldeliveryofficename + ', ' + source.u_st + ', ' + source.u_c);
if(source.u_c != '')
{
 var country = new GlideRecord('cmn_location');
 country.addQuery('name',source.u_c); //look for country assoicated with current user record being imported
 country.addQuery('company','a4e3e06a37014200cb18341643990e39');// query a record in domain separated environment associated with company
 country.query();
 var countryCount = 0;
 if(country.next())
 {
   var countrySysID = country.sys_id; // if exists, then store sys_id to variable. Lateron, it will be used to set as parent for state
   countryCount++;
 //gs.log('Deepak: Already exist country' + ' '   + countryCount + ' ' + source.u_c );
 }
 else if(countryCount == 0)
 {
 //gs.log('Deepak: ' + 'We need to insert new Country ' + source.u_c);
 var countryNew = new GlideRecord('cmn_location');
 countryNew.initialize();
 countryNew.name = source.u_c;
       countryNew.company = 'a4e3e06a37014200cb18341643990e39'; // associate location to company in domain separated environment. simply comment it out if you dont have domain separation setup
 var countrySysID = countryNew.insert(); // if does not exists, then insert new contry and store sys_id to variable, use it to set as parent for state
 //gs.log('Deepak: new country inserted ' + countryNew.name + ' ' + countrySysID);
 }
}
if(source.u_st != '')
{
 var state = new GlideRecord('cmn_location');
 state.addQuery('name',source.u_st); 
 state.addQuery('company','a4e3e06a37014200cb18341643990e39');
 state.query();
 var stateCount = 0;
 if(state.next())
 {
   var stateSysID = state.sys_id;
   stateCount++;
 //gs.log('Deepak: Already exist state ' + stateCount + ' ' + source.u_st);
 }
 else if(stateCount == 0)
 {
   gs.log('Deepak: ' + 'We need to insert new state');
   var stateNew = new GlideRecord('cmn_location');
   stateNew.initialize();
   stateNew.name = source.u_st;
   stateNew.state = source.u_st;
   stateNew.parent = countrySysID;
   stateNew.company = 'a4e3e06a37014200cb18341643990e39';
   var stateSysID = stateNew.insert();
 //gs.log('Deepak: new state inserted ' + stateNew.name + ' ' + stateSysID);
 }
}
if(source.u_physicaldeliveryofficename != '')
{
 var city = new GlideRecord('cmn_location');
 city.addQuery('name',source.u_physicaldeliveryofficename);
 city.addQuery('company','a4e3e06a37014200cb18341643990e39');
 city.query();
 var cityCount = 0;
 if(city.next())
 {
   var citySysID = city.sys_id;
   cityCount++;
 //gs.log('Deepak: Already exist city ' + cityCount + ' ' + source.u_physicaldeliveryofficename);
 }
 else if(cityCount == 0)
 {
   gs.log('Deepak: ' + 'We need to insert new city');
   var cityNew = new GlideRecord('cmn_location');
   cityNew.initialize();
   cityNew.name = source.u_physicaldeliveryofficename;
   cityNew.city = source.u_physicaldeliveryofficename;
   cityNew.state = source.u_st;
   cityNew.parent = stateSysID;
   cityNew.company = 'a4e3e06a37014200cb18341643990e39';
   var citySysID = cityNew.insert();
 //gs.log('Deepak: new city inserted ' + cityNew.name + ' ' + citySysID);
 }
}
if(!(source.u_streetaddress == '' && source.u_physicaldeliveryofficename == '' && source.u_st == '')) // if all address related attributes are not blank, then evaluate other things.
{
 var address = new GlideRecord('cmn_location');
 if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename != '' && source.u_st != '')
 {
   address.addQuery('name',source.u_streetaddress + ', ' + source.u_physicaldeliveryofficename + ', ' + source.u_st);
 }
 else if(source.u_streetaddress == '' && source.u_physicaldeliveryofficename != '' && source.u_st != '')
 {
   address.addQuery('name',source.u_physicaldeliveryofficename + ', ' + source.u_st);
 }
 else if(source.u_streetaddress == '' && source.u_physicaldeliveryofficename == '' && source.u_st != '')
 {
 address.addQuery('name',source.u_st);
 }
 else if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename == '' && source.u_st != '')
 {
   address.addQuery('name',source.u_streetaddress + ', ' + source.u_st);
 }
 else if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename == '' && source.u_st == '')
 {
   address.addQuery('name',source.u_streetaddress );
 }
 else if(source.u_streetaddress == '' && source.u_physicaldeliveryofficename != '' && source.u_st == '')
 {
   address.addQuery('name',source.u_physicaldeliveryofficename );
 }
 else if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename != '' && source.u_st == '')
 {
   address.addQuery('name',source.u_streetaddress + ', ' + source.u_physicaldeliveryofficename );
 }
 address.addQuery('company','a4e3e06a37014200cb18341643990e39');
 address.query();
 var addressCount = 0;
 if(address.next())
 {
   var addressSysID = address.sys_id;
   target.location = addressSysID;
   target.company = 'a4e3e06a37014200cb18341643990e39' ;
   addressCount++;
 //gs.log('Deepak: Already exist address' + addressCount + ' ' + source.u_streetaddress + ', ' + source.u_physicaldeliveryofficename + ', ' + source.u_st);
 }
 else if(addressCount == 0)
 {
 //gs.log('Deepak: ' + 'We need to insert new address');
 var addressNew = new GlideRecord('cmn_location');
 addressNew.initialize();
 if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename != '' && source.u_st != '')
 {
   addressNew.name = source.u_streetaddress + ', ' + source.u_physicaldeliveryofficename + ', ' + source.u_st ;
 }
 else if(source.u_streetaddress == '' && source.u_physicaldeliveryofficename != '' && source.u_st != '')
 {
   addressNew.name = source.u_physicaldeliveryofficename + ', ' + source.u_st;
 }
 else if(source.u_streetaddress == '' && source.u_physicaldeliveryofficename == '' && source.u_st != '')
 {
   addressNew.name = source.u_st;
 }
 else if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename == '' && source.u_st != '')
 {
   addressNew.name = source.u_streetaddress + ', ' + source.u_st;
 }
 else if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename == '' && source.u_st == '')
 {
   addressNew.name = source.u_streetaddress ;
 }
 else if(source.u_streetaddress == '' && source.u_physicaldeliveryofficename != '' && source.u_st == '')
 {
   addressNew.name = source.u_physicaldeliveryofficename;
 }
 else if(source.u_streetaddress != '' && source.u_physicaldeliveryofficename != '' && source.u_st == '')
 {
   addressNew.name = source.u_streetaddress + ', ' + source.u_physicaldeliveryofficename;
 }
 addressNew.street = source.u_streetaddress;
       addressNew.parent = citySysID;
 addressNew.city = source.u_physicaldeliveryofficename;
 addressNew.state = source.u_st;
 addressNew.zip = source.u_postalcode;
 addressNew.company = 'a4e3e06a37014200cb18341643990e39';
 var addressSysID = addressNew.insert();
 target.location = addressSysID; // location field visisble in user record in combination of streetaddress, physicaldeliery address(city) and state
 target.company = 'a4e3e06a37014200cb18341643990e39';
 //gs.log('Deepak: new address inserted ' + addressNew.name + ' ' + addressSysID);
 }
}
//Load Department details also along with user records.
if(source.u_department != '') //if department is not null
{
 var depart = new GlideRecord('cmn_department');
 depart.addQuery('name',source.u_department);
 depart.addQuery('company','a4e3e06a37014200cb18341643990e39');
 depart.query();
 var departCount = 0;
 if(depart.next())
 {
   var departSysID = depart.sys_id;
   target.department = departSysID;
   departCount++;
 }
 else if(departCount == 0)
 {
 //gs.log('Deepak: ' + 'We need to insert new depart');
   var departNew = new GlideRecord('cmn_department');
   departNew.initialize();
   departNew.name = source.u_department;
   departNew.company = 'a4e3e06a37014200cb18341643990e39';
   var departSysID = departNew.insert();
   target.department = departSysID;
 }
}

Re: LDAP Integration - Load Locations and Departments along with user records.

tantony — Thu, 03 Mar 2016 22:15:06 GMT

I am wondering if LDAP 'Departments' could be done separately in a 'Scheduled Import/Load' like 'LDAP User Import' and LDAP Group Import'. I know they have 'OU's defined. Is it possible or easy task to define an 'OU' for 'Department' in LDAP?.

Re: LDAP Integration - Load Locations and Departments along with user records.

sivaranjani3 — Fri, 04 Mar 2016 07:32:09 GMT

Hi ,

Im trying to map the country field directly form the ldap source table to user (target table) through transform maps. But the country is not been set in the location table there by users have their country field empty.

Can some one please help.

thanks,

Sivaranjani.

Re: LDAP Integration - Load Locations and Departments along with user records.

Deepak Ingale1 — Fri, 04 Mar 2016 10:11:29 GMT

Hi Tessy,

I dont see any reason why you cannot load like users and groups if deparments are stored in other OU.

Solution posted here is applicable if "department" is listed as one of the attribute against User object.

Re: LDAP Integration - Load Locations and Departments along with user records.

Deepak Ingale1 — Fri, 04 Mar 2016 10:12:38 GMT

If I remember, country is presented by "c" attribute.

Check if the attribute name is same in your case or it is different.

Re: LDAP Integration - Load Locations and Departments along with user records.

MGanon — Fri, 26 Apr 2019 18:39:39 GMT

Can this be written as a series of Script Include scripts?

Re: LDAP Integration - Load Locations and Departments along with user records.

tantony — Fri, 26 Apr 2019 19:45:42 GMT

You can do it in LDAPUser import transform map scripts(like onBefore,onAfter etc)or in field map scripts for individual fields like Department or Location

post LDAP Integration - Load Locations and Departments along with user records. in ServiceNow AI Platform blog

LDAP Integration - Load Locations and Departments along with user records.

Re: LDAP Integration - Load Locations and Departments along with user records.

Re: LDAP Integration - Load Locations and Departments along with user records.

Re: LDAP Integration - Load Locations and Departments along with user records.

Re: LDAP Integration - Load Locations and Departments along with user records.

Re: LDAP Integration - Load Locations and Departments along with user records.

Re: LDAP Integration - Load Locations and Departments along with user records.