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

[Deepak Ingale1]

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;
 }
}