If needing to purge the Profile table and re-import from Active Directory, what are the considerations?

Version 1

    Details

    If needing to purge the Profile table and re-import from Active Directory, what are the considerations?


     


    Resolution

    If purging the Profile and doing a re-import, there are a few major things to keep in mind when possibly altering the Profile.CustID field.

    1.  Call Ticket relationship.  All Call tickets are linked to a Profile record by the CustID field.  The CustID field exists on Profile, Subset and CallLog tables.  All three of these need to match in order to have a proper relationship from the Call Ticket to the Profile.  Therefore if you change the Profile.CustID to a new value, you should also change the Subset.CustID and CallLog.CustID to that same new value.  Call Tickets where the CustID does not match a Profile.CustID are called Orphaned Records.  
     There are two ways to reclaim Orphaned Records.  You can do it though SQL using an UPDATE statement on both Subset and CallLog or you can do it through HEAT Administrator module from Database>> Global Replace.  Be sure that you have a good backup of the DB no matter which route you take, because neither of these options offer a rollback.
    Here is a query you can run to FIND all Call Tickets that are no longer linked to a Profile record.

    --Find orphaned Subset Records--
    SELECT CallID, CustID
    FROM Subset
    WHERE CustID NOT IN (SELECT CustID FROM Profile)

    --Find orphaned CallLog Records--
    SELECT CallID, CustID
    FROM CallLog
    WHERE CustID NOT IN (SELECT CustID FROM Profile)

    2.  The second thing to consider is also driven by matching the CustID field from two different records.  That is the Configurations or Config table contents.  If you have Configuration items associated to a Profile, they are linked by the CustID field.  So again, if you change the Profile.CustID, in order to retain the Config records you will need to make the same alteration of the Config.CustID.

    --Find orphaned Config Records--
    SELECT CustID, ConfigType
    FROM Config
    WHERE CustID NOT IN (SELECT CustID FROM Profile)

    3.  Matching the CustID to AD objects.  Keep in mind that the CustID field is a HEAT System field.  Its size is 50 characters and that size cannot be altered.  So when deciding what attribute to match in AD, make sure you are not going to choose something that has the possibility of being more than 50 characters long.  This is why the majority of people do not necessarily match CustID to the Email AD attribute as it has the most possibility of entries being 51+ characters long.