Modifying an existing Transform Map

[jesusemelendezm]

Hi everyone,

I need to modify a transform map used for LDAP user import. I need to add "user account expiry date" from active directory into the user form of ServiceNow. I have done the following:

1) create a new field "u_expirydate" of date type and added to the user form.

2) from Table Transform Map I created a new field map and select   the source field "u_accountexpires" to "u_expirydate"

3) run the scheduled ldap import.

I can't get this field "Expiry date" updated? is there anything else that I need to do additionally?

Thanks,

Jesus E.

[chadlockwood · Accepted solution]

Go into System LDAP > LDAP Servers > <your ldap server>

Click Browse under Related Links

Enter the distinguishedName of a user that has an expiration date and click Search

See if the accountexpires field appears

What I think you will find is this:

While you see a date/time in ADUC, this field is actually a 64-bit number representing the number of "ticks" since 12:00 AM January 1, 1601. If the accountexpires field is being pulled, it will likely not allow you to shoehorn it into the date field you created on your import table. You will have to change that data type and handle the conversion to a date in your transform map. There is some information about this here:

https://community.servicenow.com/message/652592#652592

[jesusemelendezm]

Thanks Chad

I have applied the following

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.

Question: when it says transform it? Is it only run the LDAP schedule load? will it take care of transform it? or I have do it again.

Regards

[jesusemelendezm]

Do I have to go an create again another import set with the existing tables?

[chadlockwood]

No, I don't think so. If you added the field to the import table and it transforms correctly then the scheduled job should take care of it going forward. I recommend some spot checking on your ServiceNow user records against AD.

[jesusemelendezm]

I run the schedule load and I went to couple users and hit the refresh from ldap button. But It's still not displaying any value.

I got this from the log

com.glide.db.impex.transformer.TransformCoercionException: Unable to format 9223372036854775807 using format string yyyy-MM-dd hh:mm:ss for field u_expiry_date

  at com.glide.db.impex.transformer.DateCoercion.convertDateTimeValue(DateCoercion.java:54)

  at com.glide.db.impex.transformer.DateCoercion.coerceValue(DateCoercion.java:23)

  at com.glide.db.impex.transformer.ATransformCoercion.coerce(ATransformCoercion.java:45)

  at com.glide.db.impex.transformer.TransformerField.coerceValue(TransformerField.java:108)

  at com.glide.db.impex.transformer.TransformerField.setValue(TransformerField.java:103)

  at com.glide.db.impex.transformer.TransformerField.transformField(TransformerField.java:77)

  at com.glide.db.impex.transformer.TransformRow.transformCurrent(TransformRow.java:80)

  at com.glide.db.impex.transformer.TransformRow.transform(TransformRow.java:53)

  at com.glide.db.impex.transformer.Transformer.transformBatch(Transformer.java:140)

  at com.glide.db.impex.transformer.Transformer.transform(Transformer.java:76)

  at com.glide.system_import_set.ImportSetTransformerImpl.transformEach(ImportSetTransformerImpl.java:148)

  at com.glide.system_import_set.ImportSetTransformerImpl.transformAllMaps(ImportSetTransformerImpl.java:73)

  at com.glide.system_import_set.ImportSetTransformer.transformAllMaps(ImportSetTransformer.java:59)

  at com.glide.system_import_set.ImportSetTransformer.transformAllMaps(ImportSetTransformer.java:46)

  at com.snc.automation.ScheduledImportSetJob.runImport(ScheduledImportSetJob.java:55)

  at com.snc.automation.ScheduledImportJob.execute(ScheduledImportJob.java:44)

  at com.glide.schedule.JobExecutor.execute(JobExecutor.java:72)

  at com.glide.schedule.GlideScheduleWorker.executeJob(GlideScheduleWorker.java:159)

  at com.glide.schedule.GlideScheduleWorker.process(GlideScheduleWorker.java:119)

  at com.glide.schedule.GlideScheduleWorker.run(GlideScheduleWorker.java:54)

Caused by: java.text.ParseException: Unparseable date: "9223372036854775807"

  at java.text.DateFormat.parse(DateFormat.java:337)

  at com.glide.db.impex.transformer.DateCoercion.convertDateTimeValue(DateCoercion.java:48)

  ... 19 more