Adding a reference Location to a User Record and Geo-locating as you go

[yjaques]

Where I work we have a terrible old LDAP directory full of obscenely bad location data. However, that's what we use to create our sys_user table in Service Now. So many of the users were missing location information that I decided to programatically start fixing that. The script below iterates through the sys_user table looking for users that have an empty Location field but have something in their Department field. Department for us is typically a geographic location with a city and/or country in the field. So where location is empty but department full the script then searches the cmn_location table looking for a match. If it finds one then problem solved, the sys_id of the matched location is placed in the user record and updated. If there's no match (usually the case) the script hands the problem off to Google who usually comes up with something. If so, the location is created and inserted in the cmn_location table and the returned sys_id is then used to populate the location field of the user table.

The script can be run as a background script. (And of course you could slightly modify it and have run as a business rule on insert/update to sys_user table). To see how to create the REST service look at my other post on geolocation.

var users = new GlideRecord('sys_user');

// Issue the query to the database to get all records

users.query();

var j=0;    

while (users.next()) {

        if(users.location=="" & users.department !="") {

                  if(updateUser(users)) {

                            gs.log("Updated: "+users.name);

                  } else {

                            gs.log("Failed to update: "+users.name);

                  }

                  //set this number for however many iterations you want to try to fix. since it takes about 15 seconds per fix due to Google REST API you can seriously freeze up your instance if you run this as a background script. I recommend doing a 100 at a time.

                  if (j<1)

                            j++;

                  else

                            break;

        }

}

function updateUser(users) {

        //find the location if it exists

        var locations = new GlideRecord('cmn_location');

        while (locations.next()) {

                  if(users.department.name == locations.name) {

                            users.location=locations.sys_id;

                            users.update();

                            return true;

                  }

        }

        //we didn't find a match so create the location based on the department        

        //create a new location record                  

        var gr = new GlideRecord('cmn_location');

        gr.initialize();

        gr.name = users.department.name;

        //geolocate

        gs.log("requesting address:"+users.department.name);

        //get the Rest Message service

        var r = new RESTMessage('Google GeoLocate', 'get');

        r.setStringParameter('address',users.department.name);

        var response = r.execute();

        // put in a wait or it will return undefined

        var k = 1;

        while ( response == null ) {

                  response = r.getResponse( 1000 );

                  k++;

                  if ( k > 30 ) {

                            gs.log( 'service time-out' );

                            break;

                  }

        }

        gs.log( "response took ... " + k + " seconds");

        var mess = "";

        //parse response

        var parser = new JSONParser();

        var parsed = parser.parse(response.getBody());

        if(parsed != null && typeof(parsed) != undefined) {

                  gr.latitude = parsed.results[0].geometry.location.lat;

                  gr.longitude = parsed.results[0].geometry.location.lng;

                  var g_address = parsed.results[0].address_components;                  

                  for(var m=0;m<g_address.length;m++) {                            

                            for(var n=0;n<g_address[m].types.length;n++) {                                      

                                      if (g_address[m].types[n] == "country") {

                                                gr.country = g_address[m].short_name;

                                      }

                            }

                  }

        } else {

                  gr.lat_long_error = "true";

                  mess += "Lat/long not found. Problem with returned JSON. ";

        }

        var sys_id = gr.insert();

        if(sys_id != null) {

                  users.location=sys_id;

                  users.update();

                  mess += "Added location to user record.";

                  gs.log(mess);

                  return true;

        } else {

                  gs.log(mess + "Could not add location to user record!");

                  return false;

        }                            

}