Import user photo from LDAP into S-N (reloaded)

conmic · ‎07-15-2013

Dear Community,
my company is now changing to Service-Now and I'm currently working on adapting it to our needs. I worked the entire day long on the import of the LDAP user photo into S-N and I really got to thank john.roberts who first published in 2009 the base-script for what I need, see this topic: Importing user picture from LDAP into S-N.

Unfortunately his script is out-of-date as calling packages directly is now prohibited with the Calgary release and also his script is not able to update existing photos in S-N. So I accepted the challenge and modified his script.
It's already working and I want to share it with you in a minute, but maybe you guys can help me on a few questions as I'm new to S-N and JavaScript (previously I was more working on MS.NET).

Let's start withe the requirements:

1. Only import JPEG pictures into your LDAP, else you need to modify the script (to be honest I don't know if LDAP supports other formats)

2. Make sure to add the value "thumbnailphoto" to the system property "glide.ldap.binary_attributes". This system property should already exist on the sys_properties.list, otherwise add it manually by following the instructions that can be found on the S-N wiki.

Note: john.roberts script was aiming for the "jpegphoto", you can also use such attribute but you need to change the previous mentioned system property and also the following script accordingly (simply replace each thumbnailphoto with jpegphoto).

3. Make sure to set the system property "com.glide.loader.verify_target_field_size" to true. By default it's false. Otherwise follow the instructions given on john.roberts script.

Now to the Script:

Go to your "LDAP user import" transform map and add a new onAfter transform script.

Sorry, I'm horrible in giving proper comments on scripts. Please refer to john.roberts base-script or simply ask me if you have a question.



//add user image from ldap thumbnailphoto attribute and keep records in SN up-to-date

//**first check and get the existing photo of the SN-record
//this is vital if we want to update the photo when it has been changed in LDAP
//however the more users there are and the bigger the stored pictures are, it might slowdown the import process
//It can be simplified but then SN-pictures will not be removed automatically when the picture has been removed in LDAP
var existingPhoto = new GlideRecord('sys_attachment');
existingPhoto.addQuery('table_name','ZZ_YYsys_user');
existingPhoto.addQuery('table_sys_id',target.sys_id);
existingPhoto.addQuery('file_name','photo');
existingPhoto.query();

//**check if there is a picture on LDAP
if (source.u_thumbnailphoto != '') {
    //**if there is no picture for the record in SN
    if (!existingPhoto.next()) {
        //**launch the function to attach the picture
        attachPhoto();
    }
    //**if there is a picture for the record in SN
    else {
        var sysEncodedAttachment = new GlideSysAttachment();
        var binData =  sysEncodedAttachment.getBytes(existingPhoto);
        var EncodedBytes = GlideStringUtil.base64Encode(binData);
        //**verify if the current existing SN-picture for the record does not match the current LDAP picture
        //if it does not match, delete the current SN-picture and launch the funtion to attach the new picture
        if (EncodedBytes != source.u_thumbnailphoto) {
            existingPhoto.deleteRecord();
            attachPhoto();
        }
    }
}
//**if there is no picture on LDAP
else {
    //**check if there is one on the SN-record and delete it
    if (existingPhoto.next()) {
        existingPhoto.deleteRecord();
    }
}

//function to attach a new photo from the LDAP to the SN-record
function attachPhoto(){
    var sysDecodedAttachment = new GlideSysAttachment();
    var DecodedBytes = GlideStringUtil.base64DecodeAsBytes(source.u_thumbnailphoto);
    var attID = sysDecodedAttachment.write(target, 'photo', 'image/jpeg', DecodedBytes);
    var newAttachment = new GlideRecord("sys_attachment");
    newAttachment.addQuery("sys_id", attID);
    newAttachment.query();
    if (newAttachment.next()) {
        newAttachment.table_name = "ZZ_YYsys_user";
        newAttachment.table_sys_id = target.sys_id;
        newAttachment.content_type = 'image/jpeg';
        newAttachment.update();
    }
}

So... as you can see in order to be able to delete the photo on S-N if it has been removed in LDAP, I need to load the entire S-N image-data for each user-record there is. Meaning the more users you have and the bigger the stored pictures are, the longer it will take to verify and import. If you guys have an idea how to optimise this, I'd be grateful! Otherwise we can simplify the script, but then the photo on S-N will not be removed if it has been removed in LDAP, however it will still be able to update changes.

As john.roberts also suggested, you can afterwards test the script only on a specific user by adding to the LDAP filter (sAMAccountName=DesiredUsername*)

P.S.: Unfortunately S-N has not yet the possibility to directly import the LDAP photo, this is the only possibility we have

erikbos · ‎01-12-2015

I was thinking a bit more about this: could this be mid server related? Is everyone doing LDAP directly from the SN instance via VPN, or as in my case via the midserver ?

I would guess that all of the glide.* properties are settings of the Glide application running in the JVM in SN's data centre. Do the glide properties for LDAP make it to the midserver?

Just wondering as there is a difference between direct LDAP from the SN instance vs LDAP importing via the midserver: for the second case the System LDAP settings do not apply(!)

erikbos · ‎01-13-2015

Did a little bit more investigation in the meantime:

- Ran tcpdump on the midserver to capture the traffic between mid server and LDAP server. I can see a JPEG/JFIF header in the response from the LDAP server, so looks like the mugshot is in there.

- I had a look at the ECC queue for the communication between the SN instance and the mid server, as I figured the LDAP reply should be in there. I found the message that has the result set of the LDAP query via the mid server for my test user. It contains:

<results probe_time="4337">


<result>


<row id="0">


....


<thumbnailPhoto type="String">[B@6c1f6177</thumbnailPhoto>


....

Looks like the mid server is putting the object id in as value.. As test I added the LDAP attributes objectSid and objectGUID to the LDAP attribute whitelist for user imports. This to try and see if these attributes do end up as base64'd in the ECC queue for the test user. After a retry the ECC queue message now contains:

<objectGUID type="String">DHcWKCij <contents removed> ==</objectGUID>


<objectSid type="String">AQUAAAAAAAUVAA <contents removed> ==</objectSid>

So those definitely got base64 encoded.

It looks like the midserver is not aware of the special treatment for my mugshot LDAP attribute. I will follow up with SN support.

(It might confirm my hunch that glide.ldap.binary_attributes applies to the SN instance only.)

For the people in this thread that are able to import mugshots successfully: do you access LDAP via a midserver or via a VPN from the SeN data centre?

conmic · ‎01-13-2015

Sorry folks, haven't checked the community for a long time.

Erik, you're onto something! In our environment we have a Lan2Lan connection to the SN Datacenters, so we're not using a MID Server and I cannot do any tests on my side on this topic... BUT nevertheless I think I know the solution for you guys. Please try the updated Code below. Make sure to uncomment line 16.

If you're able to get it to work with this, I'll also update the original script.

//add user image from ldap thumbnailphoto attribute and keep records in SN up-to-date




//**first check and get the existing photo of the current SN-record


//this is vital if we want to update the photo when it has been changed in LDAP


//however this might slowdown the synchronization the more users there are.


//It can be simplified but then SN-pictures will then not be removed automatically when the picture has been removed in LDAP


var existingPhoto = new GlideRecord('sys_attachment');


existingPhoto.addQuery('table_name','ZZ_YYsys_user');


existingPhoto.addQuery('table_sys_id',target.sys_id);


existingPhoto.addQuery('file_name','photo');


existingPhoto.query();




var sourcePhoto = source.u_thumbnailphoto;




//MID SERVER WORKAROUND: uncomment the line below if you're using a MID Server


//sourcePhoto = GlideStringUtil.base64Encode(sourcePhoto);




//**check if there is a picture on LDAP


if (source.u_thumbnailphoto != '') {


       //**if there is no picture for the record in SN


       if (!existingPhoto.next()) {


               //**launch the function to attach the picture


               attachPhoto();


       }


       //**if there is a picture for the record in SN


       else {


               var sysEncodedAttachment = new GlideSysAttachment();


               var binData =   sysEncodedAttachment.getBytes(existingPhoto);


               var EncodedBytes = GlideStringUtil.base64Encode(binData);


               //**verify if the current existing SN-picture for the record does not match the current LDAP picture


               //if it does not match, delte the current SN-picture and launch the funtion to attach the new picture


               if (EncodedBytes != sourcePhoto) {


                       existingPhoto.deleteRecord();


                       attachPhoto();


               }


       }


}


//**if there is no picture on LDAP


else {


       //**check if there is one on the SN-record and delete it


       if (existingPhoto.next()) {


               existingPhoto.deleteRecord();


       }


}




//this is the function to attach a new photo from the LDAP to the SN-record


function attachPhoto(){


       var sysDecodedAttachment = new GlideSysAttachment();


       var DecodedBytes = GlideStringUtil.base64DecodeAsBytes(sourcePhoto);


       var attID = sysDecodedAttachment.write(target, 'photo', 'image/jpeg', DecodedBytes);


       var newAttachment = new GlideRecord("sys_attachment");


       newAttachment.addQuery("sys_id", attID);


       newAttachment.query();


       if (newAttachment.next()) {


               newAttachment.table_name = "ZZ_YYsys_user";


               newAttachment.table_sys_id = target.sys_id;


               newAttachment.content_type = 'image/jpeg';


               newAttachment.update();


       }


}

erikbos · ‎01-13-2015

Hi Michel,

I have tried such a change yesterday; it confirms that the contents of the field as received from the mid server does make it all the way to the user as content in an attachment. (see my remark earlier about removing GlideStringUtil.base64DecodeAsBytes().)

But it is not the solution to this problem: the objectid we see is only valid in the context of the JVM the mid server application runs in. It is the JVM's pointer to the corresponding bytes in memory for that object. Like a sys_id in SN's database: only valid in local context.

So when data leaves the JVM of the mid server the object needs to be serialized before it can be sent over the network or stored on disk for future reading. I assume base64 encoding is done to make it safe to stick it as payload in XML.

I have opened a case with SN to investigate why the mid server does not do the right encoding for the thumbnail attribute. Without actual (picture) content in the import table there is not much to work with in the SN instance.

erikbos · ‎01-19-2015

I opened a case with ServiceNow support. They confirmed they are able to reproduce the problem.. Stay tuned 😉