accountExpires attribute import to User Table

[Cory Hitchings]

Hi all, 

I've been trying to import the accountExpires attribute from AD into ServiceNow sys_user table and having some issues with the date format. 

I have a new field on the sys_user form called u_end_date in a Date/Time variable with a limit of 100.  

Our AD date format appears like this:  

(Month/Day/Year) (Hour:Minutes:Seconds) (AM/PM) (Timezone)

Looking at this, it leads me to believe the format should be: 

mm/dd/yyyy hh:mm:ss a z

However; when I run the LDAP User import, it errors on each individual with an accountExpires date with this message: Unable to format undefined using format string MM/dd/yyyy hh:mm:ss a z for field u_end_date 

Here is my script for accountExpires Field Map: 

answer = (function transformEntry(source) {
	// This function converts the UTC date/time to local date/time
function getLocalTimeZoneDateTime(dateTimeUTC) {
  var gdt = new GlideDateTime(dateTimeUTC);
  return gdt.getDisplayValueInternal();
}

var dtUtil = new DateTimeUtils();

if (source.u_accountexpires === undefined) {
      // set a blank value if the source is 'undefined'
      target.setValue(u_end_date,'');
} else if (source.u_accountexpires != 0) {
      // convert the date from int8 format to GlideDateTime in UTC
      var expiresUTC = dtUtil.int8ToGlideDateTime(source.u_accountexpires);
      // convert the GlideDateTime in UTC to Local TimeZone
      var expiresLocal = getLocalTimeZoneDateTime(expiresUTC.toString());
      //log.info("ExpiresLocal: " + expiresLocal);
      // Set the value to the Local Time Zone value
      answer = expiresLocal;
} else {
      // set a blank value if the source is anything else.
      target.setValue("u_end_date",'');
}

// 	return ""; // return the value to be put into the target field

})(source);

I've tried the following but out success with any of these variations:

MM/dd/yyyy hh:mm:ss
yyyy/dd/MM hh:mm:ss

MM/dd/yyyy hh:mm:ss a
yyyy/dd/MM hh:mm:ss a

MM/dd/yyyy hh:mm:ss z
yyyy/dd/MM hh:mm:ss z

MM/dd/yyyy hh:mm:ss a z
yyyy/dd/MM hh:mm:ss a z

Any ideas what date format this should be configured as?  Thanks so much in advance! 

[Mike Patel · Accepted solution]

I use below to onBefore transform script to import that from AD. (Make sure to remove field mapping)

// Updates the SN Account Expires field   
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 dtUtil = new DateTimeUtils();
	var gDate = dtUtil.int8ToGlideDateTime(n);
	gDate.addDaysLocalTime(-1);
	target.u_account_expires = gDate;  
}else if(!target.u_account_expires.nil()) {  
	target.u_account_expires = '';  
}  

[Cory Hitchings]

Thanks for the quick response Mike.  I removed the map, and added the onBefore script.  There are no errors but the data is still not populating.  This is my code tailored to the u_end_date field on the sys_user table: 

Use the Tab key to enter a tab character in your script.For tab actions, use Esc to tab forward and Shift + Esc to tab back.
1
// Updates the SN Account Expires field   
2
var n = source.u_accountexpires;  
3
var s = n.toString();  
4
​
5
// Only convert AD accountExpires values that begin with 1 (ex. 0 represents never expires)  
6
if (s.charAt(0) == 1) {  
7
    var dtUtil = new DateTimeUtils();
8
    var gDate = dtUtil.int8ToGlideDateTime(n);
9
    gDate.addDaysLocalTime(-1);
10
    target.u_end_date = gDate;  
11
}else if(!target.u_end_date.nil()) {  
12
    target.u_end_date = '';  
13
}  

[Mike Patel]

Few things to check. 

Make sure the end date is coming in from AD to your source table.

Make sure field length of expire date is at least 100 on source table.

Make sure u_end_date on sys_user table is Date/Time format.

Once to have done that then make sure to run job to import new records.

Share screenshot of import data and transform map. 

[Dan R]

Hi Mike,

What is the purpose of line 9 where you subtract 1 day from the time?