Compare data between import set table and target table

[Navyashree]

Hi All,

 I'm using a post execute script in Scheduled Data import, to delete the records in the target table. The DL's are synced from AD to ServiceNow via LDAP sync everyday. As part of delta sync I've to delete the DL's in the ServiceNow custom table if any DL's are deleted in AD. 

  To achieve this, I was querying the import set table with importsetrun number to get the all DL's samaccount names of current/recent load and push to an array. And then, query the target table where the samaccount name not in the defined array. Since the table has around 90K + records, it is taking lot of time to compare and delete the records. Can anyone suggest if there is any other way to compare the data and delete.

Regards,
Navyashree B R

[Bhuvan]

@Navyashree 

Deleting records directly in target table is never a good option and you can follow what ServiceNow does for User & Groups sync from Azure AD to ServiceNow sys_user table as part of SSO integration. When a new user is created in Azure AD, during next sync [out of box every 20 mins] user is created in ServiceNow. When a existing user is deleted in Azure AD, it is soft deleted and active is set to false in ServiceNow.

You can do similar logic, wherein if DLs are deleted in AD, set active to false in custom table when you cannot find the matching record in last update. You can use 'updated' field to check as part of onAfter transform event script to soft delete DLs in custom table.

If you would like to delete records, configure a weekly scheduled job during weekends where soft-deleted records can be cleaned up.

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Bhuvan]

@Navyashree 

Did you get a chance to review this ?

If my response helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Ankur Bawiskar]

@Navyashree 

I still believe it's not a good use-case to delete target record

what's your actual business requirement?

If my response helped please mark it correct and close the thread so that it benefits future readers.