Account expiry dates

[andrewnorris]

Hello

We currently import our users from active directory to SNC via the LDAP plugin. I want to bring over the account expiry dates too but as this is stored in Integer8 format it will need some transform scripting to get it to show a human-readable date format.

I'm afraid I have no idea how to convert Integer* to the current date format. Can anyone offer help or guidance?

Thanks
Andy

[bteacher]

I modified John's code to be used in a standalone onBefore Transform Script. Note that as a prerequisite you'll have to add an Account Expires column to the sys_users table.

In addition to converting the AD accountExpires Integer8 value to SN DateTime, I also added a couple of other features:
1. Ignore AD users who are set to Never Expires.
2. Clear the SN Account Expires value if the AD account is reset to Never Expires.

// Updates the ServiceNow Account Expires field with the accountExpires value from AD
// Active Directory stores the accountExpires date as an Integer8 which must be converted into DateTime for SN

var n = source.u_accountExpires;
var s = n.toString();

// Only convert AD accountExpires values that begin with 1 (ex. 0 represents never expires)
if (s.charAt(0) == 1) {
   var msDate = new Packages.org.apache.poi.hpsf.Util.filetimeToDate(n);
   var gDate = new Packages.com.glide.glideobject.GlideDateTime();
   gDate.setValue(msDate);
   if (target.u_account_expires != gDate) {
  target.u_account_expires = gDate;
   }
}
// Clear the SN Account Expires date if the accountExpires date is no longer set in AD
else if (!target.u_account_expires.nil()) {
   target.u_account_expires = '';
}

Best,
Brett

[andrewnorris]

Thanks John and Brett.

Brett, just to clarify I'm reading this a if I need to take the following actions:
1. Create a new date/time field on the sys_user table named 'Account Expires (u_account_expires) that maps to u_accountexpires from the import.
2. Add the onBefore transform script you've provided (thanks again) to the 'LDAP User Import' table transform map.
3. Import all records and apply the transform then I should see the fields start to populate.

I've done this but I'm not seeing any results yet.

Best regards,
Andrew

[bteacher]

Andrew,

Sorry if I wasn't clear about the implementation. Here are the steps that I performed to get it working in my environment:

1. Add an Account Expires (u_account_expires) column to the sys_user table as data type glide_date_time (Max length 40).

2. Go to the LDAP Server screen and add accountExpires to the Attributes section. This will allow the AD field to pass through.

3. Add the onBefore Transform Script from my previous post. It will take care of the transform, so don't add a Field Map for accountExpires.

4. Run the LDAP load and transform it.

[andrewnorris]

Thanks Brett,

1. Yes, I have a u_account_expires column in the sys_user table with a data type of glide_date)time and a max lenght of 40.

2. I didn't have any attributes set so it was pulling all across anyway but I have now set the attributes to just pull across the specific fields I need, including accountExpires. I can see these fields fitlered when I browse the LDAP connection via SNC.

3. The onBefore Transform script is active on the LDAP User Import table transform map and I have removed the Field Map for accountExpires from the the same.

4. I run the load and transform and can see a number of recoirds in the user table receving updates but nothing comes through to the 'u_account_expires' field.

If I look at the import set I can see some of the updated records have accountExpires populated but these aren't making it through to the the user records.

Best regards,
Andrew

[bteacher]

Andrew,

Sorry, I didn't copy over the code correctly the first time. I cleaned it up a bit, added some logging, and re-tested. Try this:

1. Add an Account Expires (u_account_expires) column to the sys_user table as data type glide_date_time (Max length 40).

2. Go to the LDAP Server screen and add accountExpires to the Attributes section. This will allow the AD field to pass through.

3. Add the onBefore Transform Script using the code below. It will take care of the transform, so don't add a Field Map for accountExpires.

4. Run the LDAP load and transform it.

// Updates the SN Account Expires field with the accountExpires value from AD
// Active Directory stores the accountExpires date as an Integer8 which must be converted into DateTime for SN

var n = source.u_accountexpires;
var s = n.toString();

// Only convert AD accountExpires values that begin with 1 (ex. 0 represents never expires)
if (s.charAt(0) == 1) {
   var msDate = new Packages.org.apache.poi.hpsf.Util.filetimeToDate(n);
   var gDate = new Packages.com.glide.glideobject.GlideDateTime();
   gDate.setValue(msDate);
   target.u_account_expires = gDate;
   gs.log('Setting the Account Expires date for ' + target.name + ' to ' + gDate);
}
// Clear the SN Account Expires date if the accountExpires date is no longer set in AD
else if (!target.u_account_expires.nil()) {
   target.u_account_expires = '';
   gs.log('Updating the SN Account Expires date for ' + target.name + ' to Never');
}